Creating a chart in Excel that ignores #N/A or blank cells

Question

I am attempting to create a chart with a dynamic data series. Each series in the chart comes from an absolute range, but only a certain amount of that range may have data, and the rest will be #N/A.

The problem is that the chart sticks all of the #N/A cells in as values instead of ignoring them. I have worked around it by using named dynamic ranges (i.e. Insert > Name > Define), but that is extremely inefficient, as each chart has 4 dynamic series, and I must make 25 of these charts.

Are there any other solutions that allow me to specify a range, as normal, for a data series, but tell the chart to ignore all "#N/A" or blank cells?

Answer 1

I was having the same issue by using an IF statement to return an unwanted value to "", and the chart would do as you described.

However, when I used #N/A instead of "" (important, note that it's without the quotation marks as in #N/A and not "#N/A"), the chart ignored the invalid data. I even tried putting in an invalid FALSE statement and it worked the same, the only difference was #NAME? returned as the error in the cell instead of #N/A. I will use a made up IF statement to show you what I mean:

=IF(A1>A2,A3,"")  
---> Returned "" into cell when statement is FALSE and plotted on chart 
     (this is unwanted as you described)

=IF(A1>A2,A3,"#N/A")  
---> Returned #N/A as text when statement is FALSE and plotted on chart 
     (this is also unwanted as you described)

=IF(A1>A2,A3,#N/A)  
---> Returned #N/A as Error when statement is FALSE and does not plot on chart (Ideal)

=IF(A1>A2,A3,a)  
---> Returned #NAME? as Error when statement is FALSE and does not plot on chart 
    (Ideal, and this is because any letter without quotations is not a valid statement)

Answer 2

When you refer the chart to a defined Range, it plots all the points in that range, interpreting (for the sake of plotting) errors and blanks as null values.

You are given the option of leaving this as null (gap) or forcing it to zero value. But neither of these resizes the RANGE which the chart series data is pointing to. From what I gather, neither of these are suitable.

If you hide the entire row/column where the #N/A data exists, the chart should ignore these completely. You can do this manually by right-click | hide row, or by using the table AutoFilter. I think this is what you want to accomplish.

Answer 3

Please note that when plotting a line chart, using =NA() (output #N/A) to avoid plotting non existing values will only work for the ends of each series, first and last values. Any #N/A in between two other values will be ignored and bridged.