ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Personal Finance»
  • Personal Finance Software

Why Does My Zero Disappear In Excel?

Updated on May 3, 2013
Why does my zero disappear in excel?
Why does my zero disappear in excel?

Excel is a popular spreadsheet application used for organizing budgets, tables and keeping track of all sorts of data. It can take a little while to get used to, especially if you’re not familiar with using formula to add, subtract or track information across multiple cells.

There are several issues that are common with Excel which take a little more of a working knowledge of the program to understand –and can be quite annoying if you don’t know the simple answer to.

One of the common problems is that adding a numerical zero in front of a number, or simply alone in a cell, will make it disappear.

This can make adding in certain types of number strings quite difficult.

Why does my zero disappear in excel?

Why Does Excel Remove The Zero?

Because the program seems zeros as literally nothing, or superfluous, and it will drop them automatically.

How Do I Make Zeroes Appear On Their Own or At The Start of Number Strings?

  1. Although this can be a poor workaround if you plan to use Excel as an in depth calculating program, you can change the column from numerical to text. You can do this by right clicking the cell, clicking “Format Cells” and selecting the “Text” option.
  2. Change the number of characters that display. Right click the cell, click “Format Cells” and click “Custom.” Type in as many zeros as you require characters in the cell. For instance if you have three characters (065) type in 000. If you wish to display six characters (000065) then type in 000000.
  3. On the tools menu, select options and then view. To show no zeroes in a cell, select the zero values check box. To display zero values as black cells, clear the check box.
  4. Bad Workaround. If you need to quickly insert a multiple zero number string without too much work, you can put an apostrophe ‘ directly in front of the number. For instance ‘0006 and your number will have a small error message but will still display with the correct number of numeric characters.


    0 of 8192 characters used
    Post Comment

    • Michael Leng profile image

      Michael Leng 3 years ago from Samutpraken, Samut Prakan, Thailand

      Good useful information. Like!

    • profile image

      Flemin 4 years ago

      That was informative. Thanks!