Excel – Why does the 0 (zero) disappear?

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

SOLUTION: learn how to stop zeros disappearing in Excel by formatting cells so that 0 (zeros) are displayed.

Keep reading for a super quick fix on how to add zeros in front of a number in Excel and keep zeros after the decimal.

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

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. Or maybe you’ve typed 100.50 and Excel is only showing 100.5 without the zero.

This is because Excel sees the zeros as insignificant and as a result drops them. 

This can be so frustrating when you are trying to enter part numbers, post codes, item codes and mobile numbers that contain leading zeros. Or you are trying to enter values with zeros after the decimal place.

It doesn’t matter how many times you try to enter the zero, you still end up with the same result, Excel removes the zeros!

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

Format a cell so that 0 (zero) is displayed in Excel

You can change the number format of a cell so that zeros are displayed.

1. Add zeros in front of a number using a Custom Number format

1. Select the cell or cells to be formatted.

2. On the Home tab click the dialog box launcher (the small arrow) in the bottom right corner of the Number group.

3. On the Number tab select Custom from the Category list.

4. Click into the Type box and delete the word ‘General’.

5. In the Type box type the number of zeros you require before your number and then a # (hash or pound sign) to represent the significant numbers, i.e. 198. In our example we require two leading zeros before the 198, so we will type 00### and then click OK.

The cell will now display 00198.

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. 

For formulas to add zeros in front of a number in Excel please check out our Add zero in front of a number blog or watch the video at the bottom of this article.

2. Add zeros after a decimal by increasing the decimal places

1. Select the cell or cells to be formatted.

2. On the Home tab click the Increase Decimal button.

3. Click the Increase Decimal button as many times as needed until you have the required number of zeros.

3. Add zeros after the decimal place by changing the Number format

1. Select the cell or cells to be formatted.

2. From the Home tab click the drop-down arrow on the Format Number option box.

3. Select a formatting option.

Note: Number, Currency or Accounting will instantly give your number 2 decimal places. If you need more click the Increase Decimal button.

4. Your number will be formatted and now display the required number of zeros.

To Sum up…

So there you have it… a few super useful and super quick options on how to stop zeros disappearing in Excel. 

If you are keen to learn Excel formulas to add zeros in front of a number in Excel check out our Add zero in front of a number blog or watch the video below.

Watch the Excel add zero in front of a number tutorial

[Watch on YouTube] /  [Subscribe to our YouTube Channel]

Was this blog helpful? Let me know in the comments below.