Make Excel charts treat certain values as =na()

This may be a useful read: http://peltiertech.com/mind-the-gap-...g-empty-cells/

Off the top of my head, I see three approaches to this:

1) By far the easiest is to use a formula to convert the "n/a" text strings into the error value N/A. However, you call this option "ugly" and don't want to use it. Since I think this is by far the easiest, I would ask why you think this is ugly. It might be easier to come up with a way around the "ugliness" (such as hiding the error values as seasider89 suggests or hiding the helper column or something like that) than to come up with another approach.

2) Delete/Clear the cells containing the "n/a" text strings. This is not necessarily difficult, especially if the "n/a" text strings are all grouped at the top of the data set as in your example. This will need to be done every time the data is imported, which can make it tedious. It can be automated in a macro, if needed.

3) Use some kind of "dynamic named range" that will identify the range of "n/a" text strings and exclude that from the series definition. This only works if the "n/a" text strings are all grouped at the beginning (or end) of the data as in your example. This will not work well if the text strings are scatter throughout the data set. If you are not familiar with dynamic named ranges, I would suggest starting here: http://www.excel-easy.com/examples/d...med-range.html

How would you like to proceed?

Testing by expanding your spreadsheet to 30000 data points (the limit in my version of Excel), I can see no difference in calculation time between the one column version and the two column version. If this simple helper column is slowing down your real worksheet, you might consider looking at other sources and bottlenecks for the slowdown. It does not seem to me that a simple =IF() function like this should dramatically slow down a spreadsheet.

With the three options given, I certainly do not see this as the only option. It seems to me to be the easiest to implement. If calculation efficiency is really a concern, option 2 might be the best of the three, since it completely eliminates any need to perform additional calculations. Option 3 may not be a good option because dynamic named ranges usually introduce a volatile function (such as offset and indirect) to the spreadsheet, which may also slow down calculation.

A fourth option just occurred to me: filtering. https://support.office.com/en-us/art...6-38c37dcc180e
Using filtering, you can "filter out" the "n/a" text string entries, and the chart should ignore those hidden rows.

You could use this named range formula to generate the chart

MYDATA: =IF(ISNUMBER(Sheet1!$B$2:$B$14),Sheet1!$B$2:$B$14,NA())

And the revised series formula, using named range, would be

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$14,'Book1(1).xlsx'!MYDATA,1)