Hide data label containing series name if value is zero
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.
Sub RemoveZeroValueDataLabel()
'runs through every chart on the ActiveSheet
Dim cht As Chart
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
Set cht = chtObj.Chart
Dim ser As Series
For Each ser In cht.SeriesCollection
Dim vals As Variant
vals = ser.Values
'include this line if you want to reestablish labels before deleting
ser.ApplyDataLabels xlDataLabelsShowLabel, , , , True, False, False, False, False
'loop through values and delete 0-value labels
Dim i As Integer
For i = LBound(vals) To UBound(vals)
If vals(i) = 0 Then
With ser.Points(i)
If .HasDataLabel Then
.DataLabel.Delete
End If
End With
End If
Next i
Next ser
Next chtObj
End Sub
This answer grabs Values
from the Series
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.