Hide category names from pie chart if value is zero

[Nuz]

I have a pie chart in Excel 2010 where I need to show category name, percentage and leader lines in the chart. The data typically have some zero values in it that I do not want to show on the pie chart.

I can hide the zero percentages by using custom number format 0,0 %;-0,0 %;"" but it still leaves the category name and the leader lines visible which makes the chart confusing and messy to read.

Here hiding rows/columns where the data is located is prohibited and macros cannot be used. Is there any way to accomplish this?

 

[VoG]

Try this http://www.andypope.info/charts/piezeros.htm

 

[snoopyhr]

One solution is to replace the zero result with N/A result if you do this the data is simply ignored and will not be displayed. the syntax for N/A is NA(), for example:

If( your calculation=0,na(),your calculation)

Hope it helps.

 

[Nuz]

Thanks. It worked.
Easiest way was to create a dummy-data where zeros are replaced with NA() and make the chart to refer to that dummy data rather than the original data.

 

[snoopyhr]

Glad it did,
Be cautious though......
just to make sure your working data is linked to your chart data in case if someone updates the original data the graph displays the data accordingly.

 

[CatyH]

Super helpful! Thank you so much!!

one solution is to replace the zero result with n/a result if you do this the data is simply ignored and will not be displayed. The syntax for n/a is na(), for example:

If( your calculation=0,na(),your calculation)

hope it helps.

 

[elizabethlee]

Super helpful! Thank you so much!!

Hi, may I know If( your calculation=0,na(),your calculation) what is this mean?

 

[CatyH]

This is saying:
If (whatever my original calculation was) is returning ZERO
Then (instead of putting ZERO) return "N/A" [the error message]
Else (do whatever my original calculation was).

This is essentially intercepting the data and returning the ability to have the graph "freak out in a good way" by not including a ZERO value and instead just not displaying the offensive value.

Sorry for my delay in responding, elizabethlee... believe it or not, I forget my username! [and yes, I'm getting more sleep and taking my vitamins now....!]

Hi, may I know If( your calculation=0,na(),your calculation) what is this mean?