Empty cell treated as 0

[Vincdc]

Hello:
I have a chart and do not want those empty cells to be shown on the chart.
I went to Tools-Options-Chart and set empty cells to be treated as "Not
plotted".
However, it does not work. The chart still shows all empty cells as zero. Do
I have to use formula to treat those empty cells?
Thanks in advance!

 

[Tushar Mehta]

Are those cells truly empty (as in no text string, no formula, no
nothing)? Or do they look empty because of a formula that yields "" as
a result or a format that masks, say, a zero value? If the latter, you
need NA() rather than "".

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

If a formula returns "", it is not an empty cell. It contains a short
string (zero characters, but not a null). Excel does not have the
ability to return BLANK() or NULL() in a cell, and believe me, we've
requested this. The best you can do is change "" to NA() in your
formula, which sticks #N/A in the cell. This looks ugly (hide it with
conditional formatting) but in many chart types is equivalent to using
the Interpolate option for blank cells.

If the data was pasted from somewhere else, it might contain "" where
you think there are blanks. You could simply select these cells and
press Delete to truly clear them.

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