… 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:
Now enter a space in the blank row (Cell A6):
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:
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
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
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
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
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:
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.