Add a Leading Zero in Excel

Many times, while working with Excel, you may face a situation where you need to add a leading zero to numbers. This usually occurs when working with IDs, zip codes, employee numbers, or any data set that requires numbers of uniform length. If you are not careful, Excel can make your task frustrating by automatically removing these leading zeros. But worry not! This tutorial will help you learn various ways to add a leading zero in Excel.

Key Takeaways:

• Excel removes leading zeros by default because it treats values as numbers.
• You can keep leading zeros by changing the cell format to Text.
• Adding an apostrophe (‘) before the number forces Excel to retain zeros.
• The TEXT formula lets you automatically add leading zeros across large datasets.
• A Custom Format displays leading zeros while keeping the underlying value numeric.

Watch this YouTube video to learn adding leading zeros in Excel and don’t forget to give it a thumbs-up!

Make sure to download the exercise workbook to follow along and learn how to add a leading zero in Excel.

Why Does Excel Remove Leading Zeros?

If you type a number like 007845 in Excel, you’ll notice that it immediately changes to 7845. This is because Excel automatically treats values as numbers. Numbers don’t need leading zeros, so Excel removes them by default. While this is perfectly logical for calculations, it can be inconvenient when you are dealing with IDs, zip codes, or codes that require a specific format.

Fortunately, there are several ways to add a leading zero in Excel, ranging from simple formatting tweaks to using formulas. Let’s explore them one by one.

 

Different Ways to Add a Leading Zero in Excel

Change format to Text

Since the reason why the zeros are tossed out is that Excel treats these values as numbers. The best option to add a leading zero in Excel would be to just change the format to the cell from “Number” to “Text”.

To change the cell format to text, follow the steps below:

Step 1: Select the cell in which you want to add prefix “0”.

Step 2: Go to Home Tab > Number Group.

Step 3: From the dropdown select “Text”.

Now when you add the zeros in front of the number, the zeros will remain intact.

You might notice a small yellow triangle on that cell, it is simply indicated that you have stored a number to text.

To remove that message, click on the triangle, and from the list, select “Ignore Error”.

As you can see, this method adds zeros in front of the number by changing the cell format to Number.

Add an apostrophe (‘)

You can simply add an apostrophe (‘) in front of the number to make sure that the zeros are in place. So, you can type ‘000001 instead of just 000001. In this way, the number will be shown as you want it to without having to change the format.

You can see in the formula bar that an apostrophe is added as a prefix to the number.

Use TEXT formula

Even though the above options gets the work done, it’s a pain to add zeros in front of them one by one!

Follow the steps below to understand how to add a leading zero with one single formula!

STEP 1: We need to enter the TEXT function in a blank cell:

=TEXT(

 

STEP 2: The TEXT arguments:

value

What is the value that you want to add a leading zero in Excel on?

=TEXT(B9,

format_text

How many leading zeros do you need?

If we want our number to be 6 digits long, then type in 6 zeros: “000000”

=TEXT(B9, “000000”)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards. Your leading zeros are now ready!

You should keep in mind that all of these methods add a leading zero in Excel by actually converting the cell format to text one way or the other.

These methods will add the zeros in front of the numbers but the resulting value will be a text string, not a number.

You will not be able to use them to do any calculations with them or use them in numeric formulas.

This brings us to the last method to add leading zeros in Excel – Using Custom Format

Use Custom Format

You can add leading zeros in Excel by using a custom format. This will only change the display and not the value of the cell i.e. the value in the cell will still be a number but the display will contain leading zeros.

Let’s see how it can be done!

STEP 1: Select the column in which you want to add leading zeros

STEP 2: Press Ctrl +1 to open the Format Cells dialog box

STEP 3: Select Custom and under type section: type 000000. Click OK.

Leading zeros will be added to all the numbers. If you select a cell containing these numbers and look at the formula bar, you will see that the underlying value in the formula bar remains unchanged.

So, using this method, you will display all 6-digit and the leading zeros will automatically be added to numbers containing less than 6 digits. Also, the value will still be a number and will not be converted to a text string.

 

FAQs

1. Why does Excel remove leading zeros automatically?

Excel is designed to handle numbers for calculations, and leading zeros are irrelevant in math. For instance, 007845 and 7845 mean the same thing numerically, so Excel trims off the zeros. While this works fine for arithmetic, it creates problems when handling codes like employee IDs or zip codes. That’s why leading zeros vanish as soon as you press Enter. To preserve them, you need to override Excel’s default number formatting.

2. Which method should I use if I want numbers to remain usable in calculations?

If you still want Excel to recognize the values as numbers, use the Custom Format option. This way, Excel shows leading zeros visually, but the actual cell value remains numeric. For example, a value of 45 will display as 000045, but formulas will still calculate it as 45. This is ideal when you’re working with IDs or invoice numbers where display formatting matters but numeric behavior must be preserved. Custom formatting gives you the best of both worlds.

3. What’s the difference between using Text formatting and the TEXT formula?

Changing a cell’s format to Text forces Excel to store the value as plain text. On the other hand, the TEXT formula actively converts numeric values into a text string with a defined format. For example, =TEXT(45,”000000″) returns 000045 as text. The difference is that with Text formatting, you type numbers manually, while with TEXT you can apply uniform formatting across many cells in one go. Both methods work, but TEXT is more efficient for large datasets.

4. Can I apply leading zeros to existing numbers without retyping them?

Yes, you can! The best options are using the TEXT formula or applying a Custom Format. With TEXT, you simply apply a formula to your existing list and get uniformly formatted values. With Custom Format, you just change the display style, and the numbers automatically show leading zeros. This saves time and avoids the hassle of re-entering data.

5. Will adding leading zeros affect formulas and calculations in Excel?

It depends on the method used. If you use Text formatting, an apostrophe, or the TEXT formula, Excel treats the numbers as text strings. This means they won’t work in arithmetic formulas without converting them back to numbers. If you use a Custom Format, Excel still sees the value as a number, so calculations remain unaffected. So, choose the method based on whether you need display formatting only or actual text-based codes.