hiding zero values in charts

[Guest]

Hi,

I followed the directions given to hide zero values in chats and tables in
Excel 2000. I used the chart wizard when i created them. But the zero
values continue to be shown on the chart. For example, I have data for
target values versus actual values for events. The targeted numbers go from
July 2005 - June 2005. The actual data values are from July 2005 - Nov
2005. I used the tools> options>chart>plot visible cells only.

Any guidance on what I am doing incorrect?

 

[Ed Ferrero]

Hi fascal,

The setting you want is tools>options>chart>Plot empty cells as: not plotted
(leave gaps)

The problem you then face is that zero values are still plotted, where you
have no data you need to leave the cell empty or put the value #N/A in the
cell.

If you are using formulas for the actuals, try something like;
=IF(C3=0,NA(),C3)

Ed Ferrero
http://www.edferrero.com

 

[Jon Peltier]

I suggest you keep the original data range as is for the table and
subsequent calculations, and use a nearby worksheet range for the chart
source data. Use a formula following Ed's suggestion to convert blanks,
text, and zeros to NA(), and use this fixed up range for the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

thank you. I used the formula.

Best Regards,
fascal