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?

 

[skillilea]

Number/ Format Code (Custom) on the data label

0;-0;;

 

[Peter Davison]

When I enter the above it shows all the zeros on the chart (see below)

 

[skillilea]

What does your dataset look like? Have you tried hiding zeros in the advanced options for the sheet

 

[Peter Davison]

This is currently my setting, which is fine in cells, but doesn't remove zeros on the chart. Even though the cell is not showing a zero the chart thinks it is a zero.
See below

 

[Peter Davison]

There is a messy way of removing all zeros at the same time, doesn't look good but seems to work.
Add an if statement to check for a zero and if it is zero make it say #N/A.
I suppose I could then have a conditional format to say if a cell says #N/A change it to white writing so you can't see the word.

 

[RoryA]

Did you apply that custom number format to the data labels for every series?

 

[Peter Davison]

No that is what I was trying to avoid, I am able to do it a legend at a time, but where you have 50 or so different legends I was looking for a method that would change all at the same time.
As I mentioned above, that is an option, but messy.
Thanks for getting back to me.

 

[RoryA]

IMO if you have 50 different series, your chart is already in trouble.

You could use code to apply the number format to all of them, or use your error method.

Edit: just occurred to me that if your data label formats are linked to source, as they usually are by default, then apply that number format to the source cells.

 

[skillilea]

this work

Sub SetNumFormat()
    Dim chrt As ChartObject
    Dim ser As Series
    
    Set chrt = ThisWorkbook.Sheets("Sheet1").ChartObjects("Chart 1")  ' your chart
    
    For Each ser In myChart.Chart.SeriesCollection
        ser.Format.NumberFormat = "0;-0;;"
    Next
End Sub