I have made a chart in excel in which I would like to hide the data label if value is zero. For data labels that contain the value itself this is done by using custom formating as explained here How can I hide 0-value data labels in an Excel Chart?.
However this doesn't work if the data label doesn't contain the value itself but the series name. Is there a way to hide the data label containing the series name if the value is zero?
Best Answer
I think this falls into the world of VBA. Working on individual data labels is fairly limited otherwise.
Here is code that will iterate through the series in a chart, their data points, and then delete those labels which correspond to a point with
Value=0
.There is a line of code in there (commented) that will add the data labels again to all of the points to reset them. If you just want to delete, then you can skip this line.
Edit: added in an outer loop on
ActiveSheet.ChartObjects
in order to process all of the charts on a sheet.This answer grabs
Values
from theSeries
using the technique detailed here.Here is the result on some random data where a couple of 0 value bars have had their data labels deleted.