Plot empty cells as not plotted (leave gaps)

[Harry v M]

Under Tools/Options/Chart Menu I have set the "Plot empty
cells as: Not plotted (leave gaps)as well as Plot visible
cells only, however the chart still plots null values
causing the automatic scaling to be impractical

 

[Debra Dalgleish]

If you're using a formula that returns an empty string for missing
values, change it to an NA()function. For example:

instead of =IF(B9="","",B9)
use =IF(B9="",NA(),B9)

To hide the resulting #N/A errors on the worksheet, you can use
conditional formatting. There are instructions on my web site:
http://www.contextures.com/xlCondFormat03.html#Errors

 

[Jerry W. Lewis]

NA() is the way to ignore cells in the sense of ignoring that a cell is
there at all. If the offending cells are surrounded by actual data and
you want them to break the line that connects data points, then you want
to recognize that the cell is both there and empty. There is currently
no way to do that in Excel except for the cell to actually be empty
(i.e. delete the formula).

If this is charting dynamic data, you could write a worksheet change
event that would delete or restore the formulas as needed when the data
changes. If this is for a one-time presentation, just set up the chart,
manually delete the "empty" cells, and move on.

Jerry