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.

Image showing Excel table
This table contains two sections for illustration.

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.
    Image showing Excel table
    Highlight the cells that you wish to format and go to Format Cells.

    The Format Cells interface will open.

    Image showing Excel table
    This menu shows the different categories of cells.

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

    Image showing Excel table
    Change the category of the cells to text.

  3. Now you can enter numbers with leading zeroes, and Excel will not change their format automatically.
    Image showing Excel table
    Once you have formatted the cells, you can enter numbers with leading zeroes.

    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.

    Image showing Excel table
    To check the error message, hover your mouse over the warning sign.

  4. You can remove the error message by left-clicking the warning sign and selecting the Ignore Error option.
    Image showing Excel table
    Select Ignore Error.

    Image showing Excel table
    The error notification is removed.

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.

    Image showing Excel table
    Highlight the Cells.

  2. Right-click and select Format Cells.

    Image showing Excel table
    Select the Format Cells Option

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

    Image showing Excel table
    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.
    Image showing Excel table
    Enter the type of number.

    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.

    Image showing Excel table
    Your custom formatting is complete.

How do you rate this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Leave a Comment