<

Excel Formatting Tip to hide zeros

Published on
15,035 Points
4,835 Views
7 Endorsements
Last Modified:
Awarded
Community Pick
Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice here.
 Unformatted Chart
Now look at the chart below, there are no zero values and I think you will agree that it looks better than the previous view.
Formatted Chart
There is a feature in MS Excel which can help us to hide zeros and text values in our excel table. Somehow, not many people know about this, almost as if it is Excel's best kept secret. The procedure, by the way, is quite simple. You just need to add some semi-colons.

Here is how you can hide all zeros/text values within your excel tables:

1) Select your list/table

2) Right Click - Goto Format Cells

3) In the "Number" tab select "Custom"

4) In the "Type" box you would be able to see your current format, for example, if your current table is formatted as "General" you would be able to see "General" there or if your current format is "Number" then you would be able to see "0.00" (or whatever decimal places you have decided) there, and so on.

5) Click next to the existing text in the "Type" box and enter ";" symbol three times (see attached image for example), for a total of four.

6) Click OK......your zeros/text values would stop showing.

7) Click on any of the cells which contained zero/text value....look at the formula bar....the value will still be there but it will simply not show.

8) This change will also affect your related chart.Example
So, how does this work ? Number formats are made up of four Sections : Positive numbers, Negative Numbers, Zero Values and Text values and are separated by a semi colon. By having "nothing" explicitly defined for a particular section, then that is how it will display. Not using the semicolons simply allows the remaining sections to inherit the original format, and/or, displays accordingly. Using "Formats" only impacts how cells display, the underlying value is untouched.

There is one more method which you can use to achieve the same affect, but it is somewhat different.
Go to "Tools - Options" and then un-check the "Zero Values" Setting Options
How the two methods differ:
1) The "semi-colon" method will affect only the selected area whereas the "Tools-Options" method will affect the entire worksheet.
2) The "semi-colon" method will hide text values also, but the "Tools-Options" method will hide just the zero values and not the text values.

Please Note:  
This Article uses a format which hides zeros, negatives and text. As stated previously, if you want to display different aspects, then you do need to format that section in the custom format string.
1) to hide zeros and text, but display only positive and negative values use : 0;-0;;;
2) to hide only zeros, and display everything else use : 0;-0;;@
So, please take care with your formatting, otherwise you might get what you ask for...

Further Reading at Microsoft http://office.microsoft.com/en-us/excel/HP051998791033.aspx

7
Author:sstampf
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free