Excel Formatting Tip to hide zeros

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.
 
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.

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.
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"
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