Zero Values in Charts

[Guest]

Is there a way to keep "0%" values ifrom being graphed in a pie chart?
(Besides eliminating the 0's from the source chart, of course).

I have set up some automatically populating tables, and corresponding
charts. I don't want to have to go back and eliminate all the zeros from the
tables.

Thanks in advance,

Stilla

 

[Bernard Liengme]

Can be assume the zeros are actually blanks?
If so, with chart selected, use Tools|Options and open Chart tab; set Excel
to extrapolate missing values

If there are actually zero (let's say in A1:A100)
In suitable blank cell (say F1) enter =IF(A1<>0,A1,NA())
Copy down to F100 (so far as needed)
Select F1:F100 and copy
Select A1 and use Edit | Paste Special and check Values
Now all your zeros are #N/A which Excel ignores in a chart
You can now delete F1:F100

best wishes

 

[Arvi Laanemets]

Hi

In chart source table replace 0's with "#N/A", p.e. through formula
=IF(MyFormula=0,"#N/A",MyFormula)

Probably, when you use actual source table for other purpouses too, this
isn't an option. But you can always have a mirror of actual source table as
source for chart - with 0's replaced.

Arvi Laanemets

 

[Guest]

Thanks - This works perfectly. The only problem is that, apparently, when
there is no value for a particular series, then the colors for those pie
sectors are assigned to the next series.

So, for instance, if in a groupA there are men, women and children, the pie
chart colors are blue, red and green. In groupB if women happen to be absent,
then men stay blue, but children become red.

This would be fine, but in my case, charts are side by side and need to be
compared.

I guess I can't have everything, huh? Thanks though.. it'll be a good
solution to apply another time.

Thanks!