Pie Chart - Do not graph 0 values or do not include labels

[LJenny]

Hi, I have a pie graph.

Data is like this:

A 25%
B 50%
C 25%
D 0%
E 0%


On the chart, instead of having a legend, I label the different sections.

I do not want it to have labels for the 0% sections or I want it to simply not graph those sections.


An alternative would be having the legend automatically not include 0 values.

[MrShorty]

I would suggest this article: http://peltiertech.com/mind-the-gap-...g-empty-cells/
For pie charts, Jon recommends using "" (empty string) for the "non-plotting" points instead of 0, so i would add a helper column with something like =if(b2=0,b2,""), then use this helper column as your chart series.
Try that and see if that helps.

Another possibility, since you have all of your 0 points at the end, is to use a dynamic named range for your data series. http://www.excel-easy.com/examples/d...med-range.html

[LJenny]

For some reason, the #N/A is not doing anything for me. :S I am using NA()

Neither do any other methods in the page... I tried using blanks and it is still graphing it.

Tools > Options > Charts > Plot Empty Cells as Not Plotted is selected already.

[MrShorty]

Can you upload a small sample sheet that explains your problem. When I plot your data in a pie chart, using the N/A error for 0, it does not include a data label for those points. Perhaps I am misunderstanding what you are trying to do.

[LJenny]

Can you upload a small sample sheet that explains your problem. When I plot your data in a pie chart, using the N/A error for 0, it does not include a data label for those points. Perhaps I am misunderstanding what you are trying to do.

Okay I have attached a sample.

On the chart, it is still labeling all the sections which have 0 values despite my helper column.

[Pete_UK]

A simple way is to apply a filter to column AD, and unselect the N/A terms in the filter drop-down.

The drawback with this approach is that it is not dynamic - if your data changes you will have to re-apply the filter criterion.

Hope this helps.

Pete

[LJenny]

A simple way is to apply a filter to column AD, and unselect the N/A terms in the filter drop-down.

The drawback with this approach is that it is not dynamic - if your data changes you will have to re-apply the filter criterion.

Hope this helps.

Pete

My data is going to be changing weekly so this would be a little troublesome.

[Pete_UK]

Well, it only takes a few seconds ...

You could record a macro to make that filter selection and assign a button to it, so you can just click the button to update.

Hope this helps.

Pete

[Lewster]

Could you format data labels, number, custom and enter #"" then add and ok, do this on all data labels and 0 will not show. Works on bar charts. Hope this helps.

[Andy Pope]

To have automatic legend items then you would need to rebuild the charts data range using formula
http://www.andypope.info/charts/piezeros.htm

If you just want to remove those leader lines then set empty data to gap rather than zero via the Select Data dialog