Plotting "blank" cells as zero

[Guest]

How can I keep an excel chart from plotting a blank cell as zero?

I am plotting a series of numbers, each month. If the month is not here yet,
then that cell is calculated as a blank. But the chart still plots it as
though it was zero.

Any ideas?

Thanks

 

[Guest]

I just wanted to add, that I have tried the NA() but this places "#NA" in the
cell, which defeats the purpose of leaving the cell blank. It will prevent
the cell from being plotted, but then the spread sheet is full of data that I
do not want to see. Is there another way?

 

[Ed Ferrero]

Hi groj,

Format the cell font color to "white" and then custom format the number to
"[Black];[Red];[Black];[Blue]".

Wherever this style is used, positive numbers are black, negative nos are
red, zeroes are black, text is blue, and ANY error is white.

Works for #DIV0! #NA #VALUE!.

Note that this method was first suggested by Ture Magnusson on Excel-G.

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

 

[Tushar Mehta]

If the cell is really empty (i.e., does not have a formula that yields
""), select the chart, then Tools | Options... | Chart tab. In there
set the 'Plot empty cells as' appropriately.

If you have a formula: Another problem with NA() is that if the data
are used for downstream calculations, those calcs get messed up. But,
you can always create a linked copy of the data set and have the NA()s
in the copy. Plot the copy but use the original for printing / further
calculations. For example, if your data are in column B starting with
B2 and column C is empty, in C2 enter =IF(B2="",NA(),B2). Copy C2 as
far down C as needed. Now, plot C but use B for other work.

--
Regards,

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