… and whan is a blank cell not a blank cell?
To answer the first one, create a simple XY chart, with a single data range of 7 rows, with a blank row in the middle. This should display as two separate lines, as below:
An XY Chart
Now enter a space in the blank row (Cell A6):
No longer an XY Chart
Excel has now converted your XY chart, into a line chart, without so much as a “it looks like you are trying to enter some text, can I help you mess your chart up?” The obvious solution is to use the “Change chart type” command to change back to an XY chart, the only trouble being that Excel thinks it still is an XY chart:
You can’t change an XY chart to an XY chart
The other easy option of course is to just delete the space and make sure that the “blank” row is really blank; but this raises the second question, when is a blank cell not blank?
This question arose when I wrote a User Defined Function (UDF) returning an array of X,Y coordinates defining lines to be plotted in an XY chart, with each line separated by a “blank” row in the array. The first effort was to not enter any value for the blank rows:
Function XYData(DataRange As Variant) As Variant DataRange = DataRange.Value2 XYData = DataRange End Function
UDF returning array with “blank” row
The blank rows are returned with a value of 0, which means the chart stays XY, but the two separate lines are now connected with two new lines through the origin.
The next attempt was to set the blank cells to be “Empty”:
Function XYData(DataRange As Variant) As Variant DataRange = DataRange.Value2 DataRange(4, 1) = Empty DataRange(4, 2) = Empty XYData = DataRange End Function
“Empty” array cells return zero
The “empty” array cells are still returned as zero.
I then tried assigning “” to the empty cells, rather than “Empty”:
Function XYData(DataRange As Variant) As Variant DataRange = DataRange.Value2 DataRange(4, 1) = "" DataRange(4, 2) = "" XYData = DataRange End Function
Blank cells that aren’t blank change XY charts into line charts
This appears to return blank cells, but the “blanks” still changes the XY chart into a line chart.
I didn’t find a way to return true blank cells with a UDF. The best I could do was to use a Sub:
Sub XYDatasub()
Dim XYDat As Variant
XYDat = Range("datarange1").Value2
Range("datarange2").Value2 = XYDat
End Sub
XY chart and true blanks with a sub
If anyone knows a way to stop text in the X range from turning XY charts into line charts, or to return an array from a UDF that has “true blanks”, then I’d be very interested to hear from you.
Update 6 Sep 2012:
Another search today found some information on this “feature” from Jon Peltier back in 2003:
Discussion on “Scatter plot changes to line plot with data gaps”
Microsoft Excel Charting FAQs by Jon Peltier – see “Gaps in Chart Series; Blanks Chart as Zero” and “X Axis Plots Like the Numbers 1, 2, 3, not Like the Actual Values; Points Drop to Zero”
It still seems surprising to me that this bug, I mean non-optimal feature, is still around. Even if there are people out there who like their XY charts to turn into line charts without warning, surely we should have the option to turn it off.
On the question of returning a “true blank” from a UDF, unless I have missed something it looks like you can’t, so if you want to plot an XY chart with data with gaps from a macro it seems that a Sub is the only option.
Newton Excel Bach, not (just) an Excel Blog 
Can’t remember the exact form but you need to put #N/A in the cell. Bill
LikeLike
Hi Bill – that stops it turning into a line chart, but then it joins the points either side of the “blank” cells.
LikeLike
There’s now an option to “show #N/A as an empty cell” under hidden and empty cell settings dialog for office 365 subscribers.
In other versions it’s possible to substitute #N/A with a NaN (Not-a-Number) value which displays as a large number on the sheet and generates a gap in the chart line. NaN can be the result of a UDF (saved as *.xlsm) or stored as an array constant (saved as *.xlsb). eg: https://github.com/lhem/excel/blob/master/SpecialNum.xlsm
LikeLike
Thanks Lori, that’s very helpful.
I checked that #N/A displays as an empty cell on my XY charts, and downloaded your SpecialNum spreadsheet, but I couldn’t see how to store a NaN as an array constant. Could you give an example?
LikeLike
Hi Doug, Great to see this blog still very much active and thanks for posting on that COUNTIF trick a few months back. There’s a seemingly never-ending hidden stash of functionality to be discovered – some features, like this one, even mvp charting experts Andy Pope and Jon Peltier appear unaware of.
Regarding NaN as an array constant, try from immediate window:
Names.Add "qNaN", Array(GetIEEE754SpecialValue(2))(https://stackoverflow.com/q/885994)
Since this is a binary-type name, file format needs to be set to *.xlsb. And to ensure cell formulas get recalculated when the sheet is opened, define ‘NaN’ as:
=IF(1,qNaN,recalc)
where ‘recalc’ refers to any dynamic reference (even ‘=IF(,) IF(,)’ will do.)
The advantage of this approach is it’s faster access time and no code needs to be attached to the workbook.
LikeLike
Lori – Thanks for the reply posted 22 March (I’ve run out of reply levels).
I found that:
Names.Add “qNaN”, Array(GetIEEE754SpecialValue(2))
turned my graph into two vertical lines, but:
Names.Add “qNaN”, Array(GetIEEE754SpecialValue(3))
worked!
Interestingly the other values on your Enum list produced a variety of different effects, but 3 was the only one that worked.
I will post a summary of this in the next few days. Thanks again for all your help.
LikeLike
Indeed it works for BuildDouble(255,255) = 2^1024*(2-2^-4) which in terms of enums is ‘2’ in the SO link and ‘3’ in the github link.
Other values for the remaining 6 bytes also result in a line gap, eg with all bytes set to 255 it displays as twice the max double value: 2^1024*(2-2^-52) = 3.60E+308.
And these values also respond to chart settings (gap, zero or interpolated) so it seems quite possible that chart nulls may in fact be NaNs internally.
Interested to see the write up and charting results…
LikeLike
Hi Doug,
The chart type is not changed by the space what happens is the X values default to simple linear progression of data points starting with 1.
Space in the Y data will, along with other non numeric value, be treated as zero.
The use of NA() only surpresses the data marker. The line is interpolated between points.
Unfortunately there is no formula that will be treated by a chart series as ‘true blank’.
LikeLike
Andy – but a chart with the X values in a simple linear progression, regardless of their actual value, is a line chart isn’t it? It certainly isn’t an XY chart.
LikeLike
It’s an XY Scatter chart (notice the axis starts at zero, not where 0.5 would be as in a line chart). But when there is a non-numeric but non-null and non-#N/A cell in the X values, such as “” (or any text), this is treated by Excel as a series of text values. Excel plots these X values as the array of counting numbers {1, 2, 3,…} on what is still a functionally numerical X axis. Now it looks somewhat like a line chart.
LikeLike
I guess in the true sense of the word yes it is a line rather than joined xy values.
The distinction I was attempting to make was that the chart type itself was not changed, it’s still a Excel xy scatter chart, but rather the values were.
LikeLike
Andy – well certainly Excel says it is an XY chart if you ask it 🙂
LikeLike
Pingback: Charts and Charting | Newton Excel Bach, not (just) an Excel Blog
Pingback: Plotting Charts With Gaps … | Newton Excel Bach, not (just) an Excel Blog
I missed Doug’s response in 2012!
Don’t know about within code but forever, if I put =if(??,#N/A) I got a gap as required.
LikeLike
That’s a bit of a mystery. I don’t know if I didn’t check an #N/A inside an if, or if there is some other setting, or if the different behaviour is associated with different versions, but anyway, it seems it is now fixed, at least in Excel 365.
LikeLike
Pingback: XY Chart problems – 1 | Newton Excel Bach, not (just) an Excel Blog
Pingback: XY Chart problems – 2 | Newton Excel Bach, not (just) an Excel Blog