[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Excel Formatting Tip to hide zeros

Published on
14,945 Points
7 Endorsements
Last Modified:
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


Administrative Comment

by:Mark Wills
Not at all, it was just a gentle reminder that you may not have been aware of, that's all...


Mark Wills
Page Editor
LVL 21

Expert Comment

Oh Bravo, you really took this article up a notch from when I last saw it! Good work.


Featured Post

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Join & Write a Comment

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month