4

I would like to hide data labels on a chart that have 0% as a value. I can get it working when the value is a number and not a percentage. I could delete the 0% but the data is going to change on a daily basis.

I am doing a if statement to calculate which column to put the data into.Data is shown below enter image description here

I have 2 bars one green and one red. When the value is above 8% the red bar shows and when the value is below 8%. At one time i can only show one bar. I would like to hide the 0% as the bar isnt being show.

enter image description here

  • You must have two columns for the data to show like this. In your source data, create a statement that if the result is zero to replace it with "#N/A" – wbeard52 Sep 3 '14 at 13:48
8

The quick and easy way to accomplish this is to custom format your data label.

  1. Select a data label.
  2. Right click and select Format Data Labels
  3. Choose the Number category in the Format Data Labels dialog box.
  4. Select Custom in the Category box.
  5. In the format code box, enter 0%;-0%; and click Add.
  6. Close out of your dialog box and your 0% labels should be gone.

This works because Excel looks to your custom format to see how to format Postive;Negative;0 values. By leaving a blank after the final ;, Excel formats any 0 value as a blank.

  • 1
    Note that this doesn't help if you want to have your labels also include Category/Series name in addition to the values (not the case for OP but I was looking to do this and it looks like I will not be able to). – Dan Henderson Feb 16 '16 at 19:58
1

If using Office 2013 or later, type the format as mentioned above by Dav and Erik combined, i.e.

  1. Select a data label.
  2. Right click and select Format Data Labels
  3. Choose the Number category in the Format Data Labels dialog box.
  4. Select Custom in the Category box.
  5. In the format code box, enter 0%;-0%; "" and click Add.
  6. After this, select the newly added format in the Type box.
  7. Close out of your dialog box and your 0% labels should be gone.
0

You can also set the =NA() value in your empty Cells.

  • 1
    The chart shows #N/A – Inkey Sep 3 '14 at 12:38
  • I was thinking more for the data-values than the labels. – robert Sep 3 '14 at 12:39

protected by Community Feb 1 at 23:00

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.