Microsoft Excel is a popular tool for sorting and studying data. When dealing with numbers that start with zeros, like codes or postal IDs, it’s important to keep those zeros to show the information correctly. Fortunately, Excel offers various ways to include these leading zeros in your numbers.
In this article, we have discussed various methods to add leading zeros in Excel in a few simple and easy steps.
- Convert format to text to add leading zeros
- Use custom number formatting to add leading zeros
- Use the Text function to add leading zeros
Also read: How to remove leading zeros in Excel?
Convert format to text to add leading zeros
Excel turns zeros before a number in a cell. To stop that, you can convert the cell format to text so it won’t turn those numbers and keep the leading zeros. For example, we have used an employee datasheet in which we want to add zeros before the Employee IDs. Follow the steps to add leading zeros:
Step 1: Select the cells for which you want to change the format, click on the Home tab in the Menu bar and then click on the dropdown menu in the Number section.
Step 2: In the dropdown menu, click on the Text option.
Step 3: Now, you can add leading zeros in the selected cells.
Also read: How to enable and disable Excel autosave?
Use custom number formatting to add leading zeros
To add zeros using custom number formatting, follow the steps below:
Step 1: Select the cells where you want to add leading zeros and click on the Home tab in the menu bar. Click on the Dialogue launcher (Arrow in a box) icon in the Number section.
Step 2: In the box, click on the Custom in the category, type the number of zeros you want and then click on the OK button.
For example, we needed three leading zeros, so we entered four in the box.
Step 3: Here is the result.
Also read: How to exclude words from Google Search?
Use the Text function to add leading zeros
The TEXT function lets you format a value the way you want. For instance, if you want to show the number 1 as 001, you can use the TEXT function to make that happen. To do that, follow the steps below:
Step 1: For example, if we want to add three leading zeros in cell A2, we will select any empty cell and enter the formula =TEXT(A2, “0000”).
Step 2: Here is the result.
Also read: How to find external links in Excel?
