9

I would like to hide data labels on a chart that have 0 as a value, in an efficient way. I know this can be done manually by clicking on every single label but this is tedious when dealing with big tables / graphs.

Take this table:

enter image description here

Which is the data source of this stacked bar chart:

enter image description here

But I would like this chart:

enter image description here

Notice the 0 labels are hidden and are related to different products and markets.

2 Answers 2

20
  1. Right click on a label and select Format Data Labels.
  2. Go to Number and select Custom.
  3. Enter #"" as the custom number format.
  4. Repeat for the other series labels.
  5. Zeros will now format as blank.

Custom number formats

NOTE This answer is based on Excel 2010, but should work in all versions

3
  • Exactly what I was looking for. Thanks a lot!
    – DavidD
    Oct 4, 2013 at 14:41
  • 3
    It seems that only # is needed, not #"". Oct 18, 2014 at 21:27
  • Cool, even better :)
    – CLockeWork
    Nov 3, 2014 at 9:14
3

If your data has number formats which are more detailed, like #,##0.00 to show two digits and a thousands separator, you can hide zero labels with number format like this:

#,##0.00;(#,##0.00);

The first part (before the first semicolon) is for positive numbers, the second is for negative numbers (this particular format will put parentheses around negatives), and the third, which is missing, is for zeros.

2
  • I like this one because it keeps the number formatting. If I had 1,203.29 and used the Answer's formatting, it'd just show 1203. Does the 0 disappear simply because you did not provide a third argument?
    – BruceWayne
    Jan 16, 2019 at 20:48
  • 1
    Correct. The third argument of "" tells Excel to display zero values as "". Jan 18, 2019 at 15:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.