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 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.

Examples of where zeros disappear in Excel

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.

In this blog we will cover how to ...

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

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

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

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.

Click dialog box launcher in the Format Number group

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

In the Format Number dialog box, Click Custom

4. Click into the Type box and delete the word 'General'.

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.

In the Format Cells dialog box, select Custom

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.

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.

Adding a zero to 100.5 to display 100.50

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.

Click the drop-down arrow on the Number Format option box

3. Select a formatting option.

Select a Number format option

Note: NumberCurrency 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.

Cells with the Currency Number format applied

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.

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

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus today!

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