Excel – Why does the 0 (zero) disappear?


Posted: March 26, 2014

PROBLEM: why does the 0 (zero) disappear in Excel? How do I stop the 0 from disappearing?

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

 

Why does the 0 disappear in Excel?

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?

You may have tried this for part numbers, post codes, item codes and mobile numbers. However, it doesn’t matter how many times you try you still end up with the same result, no leading zeros!

So here’s how you can make sure the 0’s are included.

Format a cell so that 0 (zero) is displayed

1. Select the cell to be formatted.

2. On the Home tab click the dialog box launcher on the Number group.

number group dialog box launcher

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.

The cell will now accept 00918.

This method is brilliant if you trying to format post codes. If you are trying to setup data for a Mail Merge in Word you might like to check out my post Retaining a leading 0 (zero) when entering Post Codes. 

If you’re wanting to learn how to add zeros using formulas please check out our Add zero in front of a number blog or watch this video.

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Join our free insiders Group!

Learn how to SAVE TIME and WORK SMARTER, without the 'techie' speak!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join our free insiders club! 

    Receive 100+ Steps to Becoming Proficient in Excel

    >