Zero values in a chart

[Igwiz]

Hi all,

I have a pie-chart that references a range that I change dynamically. The question I have is this:
Sometimes in my reference table I will have zero values but Excel still plots these and thus clutters the chart. Can you tell Excel to ignore zero values in charts?

eg

Apples 40
Pears 0
Oranges 10

ie plot only Apples and Oranges

Many thanks,

Ig

 

[xlbo]

You need to replace 0 with a blank (do a find and replace)
then with the chart selected, goto Tools>Options>Charts
and tick Plot Empty cells as "Not Plotted"

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Igwiz]

Perfect BUT the "Plot Empty Cells" is greyed out and so I can't select it. Why?

Thanks.

 

[xlbo]

Is your chart selected ??
As I said in my previous post:
"with the chart selected, goto Tools>Options>Charts"

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Igwiz]

Yes, it is Selected, as instructed.

 

[xlbo]

Is the sheet protected ??
The only way I can get that option greyed out is to either not have the chart selected or to protect the sheet (in which case you cannot select the chart)

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Igwiz]

Very odd. Selected and not protected! But at least I know how to do it now so will have a play. Thanks!

 

[Igwiz]

Geoff,
It appears your method works for bar charts but I'm using a pie chart. Any views with this new info?

 

[xlbo]

How bizarre - you can omit zero values on bar / column charts but not Pies it would seem. Only suggestion I have is:
If it is possible to hide the entire row where you have the zero value, the option for "Plot visible cells only" should do the trick...

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[combo]

There are two possibilities to hide zero values, both require regular tables with headers and some manual work:
1. Use autofilter. When you have a chart, go to source data, go Data>Filter>Autofilter. Next select data <>0. The chart will show only filtered data.
2. Use pivot chart. Prepare data in pivot table with hidden 0s, and create pivot chart based on this table, with hidden pivot fields. Requires refreshing when data changes.

combo

 

[Wray69]

Anothere option is that excel wont plot error values like #n/a. I posted and example here

http://home.comcast.net/~wray69/M-274.xls

you can enter your data on sheet M-274 but the chart is actually pulling its data from sheet 1.

Hope this helps,

Regards,

Wray

 

[KenWright]

And if you don't like the visual effect of the #NAs, you can use formatting or conditional formatting to get rid of them by turning them white.

Regards
Ken................

 

[westma]

what is the formula one might use for conditional formatting to turn all cells white with any error message displayed from column L on?

 

[cheerio]

Use the ISERROR() function

 

[SkipVought]

westma,

Post you question in a NEW THREAD!


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

Wray,

Once again, this doesn't appear to work for pie charts.

Cheers.