Do not chart empty cells as '0'

Question

I need to plot a graph on Excel 2007. I populate a table by dynamic values. Let's say, the table contains 20 rows, but some times some rows may stay empty. Once that happens the graph does not stop on empty cells, it treats them as 0 value and the graph continues. How to stop the graph plotting on the empty cells? I tried NA() string, but it does not help at all. That special value must be passed to the cell once it is empty. Best regards

Answer 1

There are various ways to handle discontinuities in chart source data, eg:

HIDE
Hide the row or column and be sure that your chart options Tools/Options - Chart Tab have Plot visible cells only. [SkipVought].

=NA()
Charts do not plot the #N/A error. You could either do a find/replace blanks with #N/A or incorporate a formula that returns a #N/A rather than an empty string.....depends on your layout and how the data gets in there [xlbo].

However it has been stated “In a recent version of Excel, Microsoft introduced a bug in its line charts making it impossible to create a discontinuity. While XY Scatter charts work as expected, as of now, Microsoft has not fixed its line chart problem.” [TM].

HIDDEN AND EMPTY CELLS: