General discussion

Locked

Excel Zero Values in Pie Charts

By WillyRay ·
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

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Zero Values in Pie Charts

by DKlippert In reply to Excel Zero Values in Pie ...

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

Collapse -

Excel Zero Values in Pie Charts

by WillyRay In reply to Excel Zero Values in Pie ...

I should clarify. I have a 12x6 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...

Collapse -

Excel Zero Values in Pie Charts

by DKlippert In reply to Excel Zero Values in Pie ...

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

Collapse -

Excel Zero Values in Pie Charts

by WillyRay In reply to Excel Zero Values in Pie ...

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?

Collapse -

Excel Zero Values in Pie Charts

by WillyRay In reply to Excel Zero Values in Pie ...

This question was closed by the author

Collapse -

Excel Zero Values in Pie Charts

by WillyRay In reply to Excel Zero Values in Pie ...

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

Collapse -

Hide Pie Chart Slices without VBA

by Steve=True In reply to Excel Zero Values in Pie ...

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:

http://www.exceldashboardtemplates.com/?p=696

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums