Suppressing Zeros in chart labels
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.