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.
Table of Contents
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:
That’s not two digits now, is it?
To add the leading zeros:
- Select the cells containing the IDs.
- Click the arrow next to General in the Number Group.
- Scroll down and select Text.
- Enter leading zeros
- All the entered values now have leading zeros and are stored as Text strings.
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.
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:
Here’s what we need to do to get them back in the cells.
- Select the cells containing the zip codes.
- Press CTRL + 1. The Format Cells dialog box appears.
- Select Custom at the bottom of the Category list.
- 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.
- Press Ok.
Your entered zip codes appear with leading zeros as:
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.
To add leading zeroes:
- Select cell C2 and type in the TEXT formula as:
- In the first argument, reference the cell containing ID No.
- Add the number format you want.
We have the ID No. of a hundred employees, so we will set the format to three digits.
- Press Enter.
The cells with the formula show leading zeros as:
Copy the formula in the remaining cells with the Fill handle.
And it’s done! 🥇
You can now copy and paste the contents of column C into column A.