Excel scatter graph line only "connects data points with line" even with #N/A in the source cells. I have tried using different scatter graph line types but all interpolate the data over the ranges where #N/A is returned in the source cell using the N/A() function. I have "show empty cells shown as gaps" selected.

Any bright ideas!?


Your formula can return NA() or "", but neither is interpreted as a blank cell: one's an error value, and the other is a short text string. The marker for NA() is omitted, and the line connects points on either side of the NA(). A marker for "" is placed at zero, since Excel interprets text as zero.

You have to manually or programmatically delete the contents of a cell in order for the chart to consider it a blank cell. Or you could manually or programmatically format the connecting line that spans the non-blank cell to use "no line".

Not the bright idea you wanted, but these are the best I've ever come across.

EDIT: A recent (late 2017) enhancement to Excel 2016 treats #N/A like a blank cell. (If you have Office 365, you can toggle this setting.) Finally now we can have a formula produce a result which is plotted as a gap.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.