Software·43,776 discussions

Excel Zero Values in Pie Charts

Locked

I have a percentage pie chart that is built from formulas that reference data that is updated daily. Often several of the 12 categories have 0% in the chart. I need to show the labels and data on the chart but I get all my zeros and their labels stacked in a jumbled mess. Can I just show labels and values on the chart that are greater than 0. Don’t say uncheck zero values on the options/view tab. That doesn’t fix the chart problem. Thanks

Topic

Excel Zero Values in Pie Charts

In reply to Excel Zero Values in Pie Charts

Here are some things that have been suggested:
Use AutoFilter to hide the Zero rows. (Data>AutoFilter Custom not equal zero)

or

http://tinyurl.com/avzb

Excel Zero Values in Pie Charts

In reply to Excel Zero Values in Pie Charts

I should clarify. I have a 12×6 data table that I use to populate 6 ie charts. A filter takes out and entire row so that I end up with some charts completely blank. I’m looking for a charting option that will not show the zero’s and their labels. Nice try…

Excel Zero Values in Pie Charts

In reply to Excel Zero Values in Pie Charts

Here’s a macro from Jon Peltier, Microsoft Excel MVP

“You also could use a macro that removed any labels from points with zero
values:

Sub RemoveZeroLabels()
Dim nPts As Long, iPts As Long

For iPts = 1 To ActiveChart.SeriesCollection(1).Points.Count
If WorksheetFunction.Index( _
ActiveChart.SeriesCollection(1).Values, iPts) = 0 Then
ActiveChart.SeriesCollection(1).Points(iPts).HasDataLabel = False
End If
Next

End Sub

Excel Zero Values in Pie Charts

In reply to Excel Zero Values in Pie Charts

I was looking for some setting in Excel but I like your solution. Thanks for the help. I tinker with VBA and would like to do more. Can you recommend a good VBA resource?

Excel Zero Values in Pie Charts

In reply to Excel Zero Values in Pie Charts

This question was closed by the author

Excel Zero Values in Pie Charts

In reply to Excel Zero Values in Pie Charts

I tested the Macro and it does exactly what I was looking for. Thanks again!

Hide Pie Chart Slices without VBA

In reply to Excel Zero Values in Pie Charts

Here is an easy technique that does NOT involve VBA.

You can do this with using the NA() function in excel. In most charts it does not show the data point or label. Here is link describing this in more detail with sample download file:

How-to Hide a Zero Pie Chart Slice or Stacked Column Chart Section

[Author]

Replies