PROBLEM: why does the 0 (zero) disappear in Excel? How do I stop the 0 from disappearing?
SOLUTION: learn how to stop zeros from 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 how to keep zeros after the decimal.
If you want to learn how to add leading zeros using formulas in Excel, please check out my Add zero in front of a number blog.
Why does the 0 disappear in Excel?
So you typed 00198, and Excel completely ignored the 00 and only entered the 198 into the cell. Or maybe you typed 100.50, and Excel only shows 100.5 without the zero.
This is because Excel sees the zeros as insignificant and, as a result, drops them.
This can be frustrating when you are trying to enter part numbers, postcodes, item codes, and mobile numbers that contain leading zeros or 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 a cell’s number format 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 the Type box to delete the word ‘General’.
5. In the Type box, type the number of zeros you require before your number, and then use 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 often as needed until you have the required 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 the required number of zeros will now be displayed.
To Sum up…
So there you have it—a few super useful and super quick options for stopping zeros from disappearing in Excel.
If you are keen to learn Excel formulas for adding zeros in front of a number, 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.
Loved This Tip? Learn Even More in my Self-Paced Beginner Excel Course
Excel Stage 1 is designed specifically for individuals who are totally new to Excel, have been self-taught or have received bits and pieces of training from multiple sources.
$347+gst
Certified Microsoft Office Specialist
Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.
Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.
And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!
Happy Excel-ling!!
Thanks.
You are so welcome. I’m glad it helped 🙂