How to Add Leading Zeros in Excel

By default, Excel automatically formats numbers to remove leading zeroes. This creates difficulties entering data such as IDs.

This guide shows you how to get Excel to accept leading zeroes. I will use the Office 365 version of Excel (build 2203). However, the instructions will be similar in other versions.

I will be using a simple sample table.

First Method: Formatting as text

This method is applied before the data is entered into the table. Let’s look at how we can format the cells as text.

1. Highlight the cells you will be formatting, then right-click and select Format Cells.

   

   The Format Cells interface will open.

   

2. In the Category section, select Text and click OK.

   

3. Now you can enter numbers with leading zeroes, and Excel will not change their format automatically.

   

   After entering the numbers, Excel marks the formatted cells with an error notification. This is because there are numbers in the cells, even though the cells are formatted as text.

   

4. You can remove the error message by left-clicking the warning sign and selecting the Ignore Error option.

   

   

Second Method: Custom Formatting

This method is useful if you have already entered data into cells and want each number to have a set number of digits.

1. Highlight the cells that contain the data.

   

2. Right-click and select Format Cells.

   

3. In the Number tab, select the Custom category.

   

4. In the Type section, enter the number of digits required for your data. For example, I am entering four-digit whole numbers, so I will enter four zeroes. This limits the maximum digits to four; the cells will not accept entries above four digits.

   

   Once you have entered the type and digits, you will be shown a sample of what your data will look like. Select OK to apply the formatting to highlighted cells.