Removing zeros from all data labels at the same time

[Peter Davison]

I know how to remove data labels that are showing a zero individually, but is there a way of removing zeros in an excel chart for all data labels at the same time?

 

[Peter Davison]

Thanks for coming back so quick, appreciate that.
I was looking to achieve with a formula, however I will try the VBA.

 

[Peter Davison]

I tried the code, but it did not like the line -
ser.Format.NumberFormat = "0;-0;;"
also should mychart. be chrt?

 

[skillilea]

Sorry, try this

Sub SetNumFormat()
    Dim chrt As ChartObject
    Dim ser As Series
    Dim pnt As Point
    
    Set chrt = ThisWorkbook.Sheets("Sheet4").ChartObjects("Chart 1")  ' your chart
    
    For Each ser In chrt.Chart.SeriesCollection
        For Each pnt In ser.Points
            pnt.ApplyDataLabels
            pnt.DataLabel.NumberFormat = "0;-0;;"
        Next 
    Next
End Sub

 

[Peter Davison]

I’ll give it a try in the morning
Thanks for your continued help

 

[Peter Davison]

I tried the code above, but it didn't remove the zeros.
It's not a problem I'm going to use the "If" and if it is zero then put a #N/A in the cell and then it does remove the zeros.

 

[RoryA]

Did you try applying the number format to the cells?

 

[Peter Davison]

Yes I tried the number format, but it only works one series at a time and I was looking for a solution that I could do something once and it removed all Data Labels with Zero