<

Excel Formatting Tip to hide zeros

Published on
14,888 Points
4,688 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
Comment
Author:sstampf
2 Comments
 
 

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

Cheers,

Mark Wills
Page Editor
0
 
LVL 21

Expert Comment

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

Alain
0

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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