How to keep the zero in Excel

You can watch a video tutorial here.

When you type a number on a worksheet that starts with a zero, Excel ignores the leading zeroes and displays only the digits following the zero. There may be a case where the leading zeroes are important and need to be retained. A good example is an ID number that has a fixed number of digits and starts with zeroes. In Excel, there are 2 ways of doing this:

1.  Define a custom format
2. Use the TEXT() function: this applies a customized format to a number
   1. Syntax: TEXT(value, format)
      1. value: the number to be formatted
      2. format: the format to be applied

Option 1 – Define a custom format

Step 1 – Select the data

• Select the data for which leading zeroes are to be kept

Step 2 – Open the Format Cells window

• Right-click and select Format Cells  from the context menu

OR

Go to Home > Number  and click on the arrow to expand the menu

OR

Go to Home > Cells > Format > Format Cells

OR

Press Ctrl+1

Step 3 – Create the format

• In the Format Cells window go to Number > Custom
• Under Type enter: 000###
• Each ‘0’ is for the zeros to be kept and the ‘#’ represents the digits
• Check the sample to see if the format is correct
• Click OK

Step 4 – Check the result

• The zeroes are retained in the ID numbers

Option 2 – Use the TEXT() function

Step 1 – Create the formula

• Select the cell where the result is to appear
• Type the formula using cell references:

=TEXT(ID, “000###”)

• Press Enter

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 3 – Check the result

• The zeroes are kept in the formatted ID numbers

Note: This method converts the values to text