3 Best Ways to Insert Leading Zeros in Microsoft Excel

Microsoft Excel removes the zero in front of any number you add to the cells. While in certain situations, the leading zero may not matter, they are important in representing a sequence. For example, product bar codes or IDs are known for having leading zeros. Therefore, if you are maintaining a register of products using their bar codes, the zeros mustn’t get removed to avoid mixing products.

It’s simple to ensure that Excel doesn’t remove the leading zeros in the spreadsheet. Luckily, there are certain ways you can type them in Excel and not struggle with them getting removed automatically. Here’s how to add leading zeros in Microsoft Excel.

Insert Leading Zeros by Converting Numbers to Text

By default, Excel takes out any zeros in front of numbers in your Excel spreadsheet. This is because Excel most often takes out leading zeros as insignificant. One way to prevent this is by converting the figures in the Excel spreadsheet to text strings. Here’s how to do so:

Step 1: Click the Start button on the Taskbar to open the Start menu and type excel.

Step 2: From the results, click on the Microsoft Excel app or workbook to open it.

Step 3: On the Excel workbook, select the cell or cells you would like to type leading zeros in.

Step 4: On the Excel Ribbon, click the drop-down in the Number group.

Step 5: Select Text from the drop-down.

Step 6: In the selected cells, type out the numbers with the leading zeros. All the entered values including the zeros will show as text strings.

Step 7: Click the green arrow on the top-left corner of the cells with the numbers. The green arrow appears as an error message when numbers are saved as text.

Step 8: Select the warning sign and click Ignore error.

For numbers stored as text in Excel, it is important to note that they cannot be used in formulas.

Insert Leading Zeros by Updating Number Category

If you want to use the numbers with leading zeros in a formula, then this is the best option. From the Microsoft Ribbon, update your Number preference to ensure you can insert leading zeros in your cells. Here’s how to do so:

Step 1: Click the Start button on the Taskbar to open the Start menu and type excel to search for the app.

Step 2: From the results, click on the Microsoft Excel app or workbook to open it.

Step 3: On the Excel workbook, select the cell or cells you would like to type leading zeros in.

Step 4: On the Excel Ribbon, click the dialog box launcher in the Number group. This will launch the Format Cells dialog box.

Step 5: In the Format Cells dialog box, click the Number tab.

Step 6: In the Category field, select Custom.

Step 7: In the Type field, clear any existing text and type the number of digits you want for your numbers. For example, if you want five digits with four leading zeros, a sample can be 00000.

Step 8: Click OK to save your change.

Step 9: In the selected cells, type your numbers with your preferred number of leading zeros.

Insert Leading Zeros Using the TEXT Function

A final option you can consider when trying to insert leading zeros in Microsoft Excel is the TEXT function. The TEXT function converts values into text strings. Here’s how it works:

Step 1: Click the Start button on the Taskbar to open the Start menu and type excel to search for the app.

Step 2: From the results, click on the Microsoft Excel app or workbook to open it.

Step 3: On the Excel workbook, type out the numbers you would like to type leading zeros in.

Step 4: In another column, type the following:

=TEXT(

Step 5: Select the corresponding reference the cell with the relevant numbers. It needs to look like below:

=TEXT(C3,

Step 6: Enter your preferred number of digits. It should look like below:

=TEXT(C3, “0000#”)

Step 7: Press Enter. Your preferred number of leading zeros alongside other digits should now be in the cell.

Step 8: Place your cursor on the cell, drag the right corner down to copy the formula into the other cells, and the numbers will appear with leading zeros.

Manage the Leading Zeros

While some may assume zeros have no business in front of the numbers, they help organize specific data properly. So if your Excel spreadsheets keep removing the leading zeros in front of numbers, you can use one of the methods above to fix that. Luckily, there are ways where you can speed up that task.

For example, instead of adding new columns or rows to your Excel spreadsheet after formatting fonts, formulas, etc., you can use AutoFill and Flash Fill to save time. AutoFill and Flash Fill serve different functions but help reduce time spent on repetitive data entry actions.