How to update Excel chart legend without showing zero legend entry values

If you do not wish to display the series names that don't have values in the chart legend (see pie chart example below), then you can use a similar code as this one below:

Sub update_legend()

chartname = "Mychart"
ActiveSheet.ChartObjects(chartname).Activate
On Error GoTo aa:     'code to delete the current legend if there is one    
ActiveChart.Legend.Select    
Selection.Delete
aa:    ActiveChart.SetElement (msoElementLegendRight) 'code to create a new legend on the right of the charta = 0
With ActiveChart.SeriesCollection(1)
ChartValues = .Values
    For i = 1 To .Points.Count 'loop to go through all the values of the chart
        If ChartValues(i) = "" Or ChartValues(i) = 0 Then 'if the value is zero or empty...
            ActiveSheet.ChartObjects(chartname).Chart.Legend.LegendEntries(i - a).Select
            a = a + 1
            Selection.Delete  '... then delete the corresponding legend entry.
        End If
    Next
End With
End Sub

Before macro run:

After macro run:

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to hide the zero percent labels in an Excel pie chart

Remove the 0% in an Excel pie chart: Change the number format code of the labels

1) Select your chart and go to "Format Data Label":

• On Excel 2013: click on the "+" sign that appears on the top right of the chart and click on the arrow next to "Data Labels":

• On Excel 2007: go to the menu "Chart tool" > "Format", select "Label format" and "Custom".

2) Go to the section "Number" and add a new "Format Code": Type:

0%;0%;;;

3) As a result, you won't see the 0% values in your chart:

There are many other things you can do with the "Number Format Code" like changing colors of the labels, changing label format, etc. More info here

Created by DoMyExcel.com

Customized Excel Solutions starting at $29!