Home » Our Blog » Excel – Why does the 0 (zero) disappear?

Excel – Why does the 0 (zero) disappear?

A weathered wooden wall with a small plaque with the number zero

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. 

Sharyn is an expert trainer.  She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.

She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

If you enjoyed this you may also enjoy...

How to subtract in Excel

Let me help you learn how to subtract in Excel.   It’s simple when you’re shown how, but read to the end to make sure you understand what happens when you multiply and subtract, or divide and subtract in the same formula.   This is a tricky one, so please don’t skip over it! 

​Read More

How to unhide columns in Excel

Need a hand to unhide columns in Excel?   There are different methods or unhiding available to you, depending on the version of Excel you are using.  I’ll take you through the method that will work on any version. 

​Read More

Best Excel Keyboard Shortcuts for Beginners | Tips and Tricks

Learn the BEST Excel keyboard shortcuts. Perfect for Beginners and for those who want to save time and work smarter. It doesn’t matter if I’m delivering a Stage 1 or a Stage 3 Excel training course – when I’m demonstrating to the class I whip around the screen, format, select and modify and update using shortcut

​Read More

How to remove Excel formulas and keep the results

If you share Excel files outside of your organisation you’ll enjoy this post where I share with you how to remove Excel formulas and keep the results so that you can confidently send a file knowing you aren’t disclosing confidential information inside of your formulas.

​Read More

Move, Copy and Paste information in Excel

  Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again. All Windows applications have access to the Windows Clipboard. This is a special area used for

​Read More