How to keep 0 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