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.

19
  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

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

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.

  • 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 at 20:48
  • 1
    Correct. The third argument of "" tells Excel to display zero values as "". – Jon Peltier Jan 18 at 15:48

protected by bwDraco Sep 17 '15 at 21:10

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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