Newton Excel Bach, not (just) an Excel Blog

… 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.