How to Add Leading Zeros in Excel: Best Methods (2023)

If you have ever worked with leading zeros in Excel, you know how frustrating it can be πŸ˜•

By default, Excel removes leading zeros in numbers. That’s fine most of the time.

But what if you need to type in some product IDs with the same number of digits? You need leading zeros for that.

Fortunately, there are a couple of ways you can add leading zeros to numbers in Excel. We’ve listed some of these for you to practice. Let’s review them below 🧐

Also, you can try adding leading zeros in real time using this sample workbook here.

How to keep leading zeros while writing

If you are typing in some product IDs or serial numbers that need to be a specific number of digits, you need to use leading zeros.

Adding leading zeros can be a problem in Excel as it removes leading zeros in numbers by default. It considers zeros on the left side of a cell insignificant.

Luckily, there is an easy way to solve this problem – you can store the value as a Text string πŸ˜€

Want to know how to do it? Let’s see it below.

Say, we want to store a couple of product IDs with two digits. If we write them without converting them to text strings, Excel will store the values as:

Numeric values stored to apply number format

That’s not two digits now, is it?

To add the leading zeros:

  1. Select the cells containing the IDs.
Choose Format cells
  1. Click the arrow next to General in the Number Group.
  2. Scroll down and select Text.
Text format from Number tab
  1. Enter leading zeros
  2. All the entered values now have leading zeros and are stored as Text strings.
Final result of formatting in Microsoft Excel

To remove the small green triangle on the top left corner, click it. Select Ignore error and the triangle will be removed.

See? That’s how easy it is. Try it yourself! πŸ˜‰

Note that a value stored in Text format cannot be used in formulas and other operations.

If you only want to change the visual representation of the number, try one of the following methods.

Kasper Langmann, Microsoft Office Specialist

How to show leading zeros with a custom format

You can add leading zeros by changing the format to Text. But it has some limitations. You cannot use the text values in calculations which makes it difficult to work with them.

Another easy way to add leading zeroes to your values is to custom format them. Custom formatting is only applied to numeric data, and it only alters the display of the number.

This means the final value is stored as a number and can be used in calculations.

Let’s see how to do it below πŸ”Ž

Say, we have zip codes of some areas that include leading zeroes. Upon entering data, Excel removed the leading zeros as:

Zip codes - force Excel to display numbers

Here’s what we need to do to get them back in the cells.

  1. Select the cells containing the zip codes.
  2. Press CTRL + 1. The Format Cells dialog box appears.
  3. Select Custom at the bottom of the Category list.
Select Sutom for this numeric dataset
  1. Under the Type box, add the number of digits you want.

We want five digits, so we will type in five zeroes. The dialog box also shows a sample of the result in the box above.

Type specififed and sample value shown
  1. Press Ok.

Your entered zip codes appear with leading zeros as:

Zip codes with leading zeros

This is the best option for adding leading zeros as it can be used in formulas and is custom designed πŸ€“

Add leading zeros in Excel with the TEXT function

As evident from the name, the TEXT function converts values to text strings.

You can use it to change your cell value to a text string but use it in calculations at the same time.

It uses the same format code as we saw earlier in the Custom number format. The only difference is that the value is now the result of a formula.

Let’s see it working through an example below πŸ‘€

We have a data set containing the ID No. of employees of a company. To keep the records neat, all IDs should have the same number of digits.

Add leading zeros with ID No.

To add leading zeroes:

  1. Select cell C2 and type in the TEXT formula as:

=TEXT(

Following formula used for adding leading zeros
  1. In the first argument, reference the cell containing ID No.

=TEXT(A2,

Referencing the cell
  1. Add the number format you want.

We have the ID No. of a hundred employees, so we will set the format to three digits.

=TEXT(A2, “00#”)

Entering the number format
  1. Press Enter.

The cells with the formula show leading zeros as:

Result showing leading zeros. Formula in formula bar.

Copy the formula in the remaining cells with the Fill handle.

Copying the cell values to remaining cells.

And it’s done! πŸ₯‡

You can now copy and paste the contents of column C into column A.

Frequently asked questions

You can easily add leading zeros in Excel using Text or Custom format. If you want to use the values in formulas, it’s better to use a custom format. Press CTRL + 1. Select Custom from the Category list. Type in six zeroes and press Ok. The selected cells will have six digits.

You can add a zero in front of a phone number by changing the format of the cell value to Text. It will store the phone number as a text string. To do this, select the cell containing the number. Under the Number group, click the small arrow next to General. Scroll down and select Text. The value is now stored as a text string. Add a zero in front of the number and it will remain the same.