Susan writes me and asks if there is a way to suppress zeros in chart labels.
“I have a chart that’s dynamic in that I can choose a month that’s represented. For each month, and Sales Rep, there are up to 5 possible revenue streams, but not every Sales Rep has revenue for all revenue streams each month. Is there a way to remove the zero values from the labels in my chart?”
I want to drive traffic to this site, so I better answer her question.
The Problem:
Susan’s issue is that not every data point is represented by her sales reps. Here, you’ll notice that Kim has no Service Contracts for the month selected.
This not only causes the data label to show ($0), but it also muddles the placement of the Service Contract data label.
The Solution:
An easy solution is to use a Number Formatting on the source data so that zeros (0) are suppressed. In this example, the source data is formatted as a currency as shown here:
A close of look at the syntax shows this:
$#,##0;($#,##0)
This is actually two number formats in one. The syntax to the left of the semicolon represents the formatting needed for positive numbers. The syntax to the right of the semicolon represents the formatting needed for negative numbers. As you can see, negative numbers will be wrapped in parentheses with this formatting.
Now, if we add another semicolon, we tell Excel that we want another format for zeros. Any syntax placed to the right of a second semicolon will represent the formatting for zeros. If we were to use this:
$#,##0_);($#,##0);”n/a”
All zeros would be formatted to show as n/a.
What’s interesting is that if we put nothing after the second semicolon, Excel assumes that you don’t want to show zeros at all. So this syntax would suppress the showing of zeros altogether.
$#,##0_);($#,##0);
All you have to do to apply this concept is add a custom format to the source data for your chart.
With the custom format in place, you’ll notice the Kim’s zero doesn’t show in either the chart or the source data table.
Even more nifty, a quick look a the formula bar shows that the zero is still there. This means the value in the cell is intact – it’s only hidden with the custom format.
This allows the data label to remain effective in case Kim decides to actually get off her butt and start selling Service Contracts.
RELATED STUFF
Brilliant, I have a graph with a data table below and I want my zero to show as a -, a quick adjustment of this and it works perfectly. Thanks!
A bit of experimentation shows that another way to achieve this is to use the hide zeros command for the whole spreadsheet. Of course this will not show a “-” as Rebecca has suggested
Awesome Bacon-Bits!
Peter, hiding zero value from spreadsheet still shows zero value on data series in chart.
Folks;
Problem here! All grand totals look to be the same, but are they? No! Jim @ 217, Tim @ 270, Kim @ 186. Misinformation extreme. XL will actually do this?
A better chart would be grouped by sale type with a bar for each salesman with a total value table listed in an open area on the chart.
Bill: The chart is not suppose to show/compare the totals between each person.
The Chart is supposed to show the size of each business segment within each person’s portfolio of business.
Your alternative chart is another way to go, but it still would not effectively show the difference in the total sales amounts (Jim @ 217, Tim @ 270, Kim @ 186).