Excel – Why does the 0 disappear?

PROBLEM: dealing with disappearing 0 (zeros) in Excel.

SOLUTION: learn how to format cells so that 0 (zeros) are


Excel 2010, 2013 & 2016 – Formatting a cell so that 0 (zero) is displayed

So you’ve typed 00198 and Excel has completely ignored the 00 and only entered the 198 into the cell.  This is because Excel sees the zeros as insignificant and as a result drops them.  Frustrating isn’t it?

Here’s how you can make sure the 0’s are included:

  1. Select the cell to be formatted.
  2. On the Home tab click the dialog box launcher on the Number group.
  3. On the Number tab select Custom from the Category list.
  4. In the Type box type 00### (# signs represent the significant numbers, i.e. 918) and then click OK.
  5. The cell will now accept 00918.

If you found this post helpful please ‘Like’ us!