When is an XY chart not an XY chart …?

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

This entry was posted in Arrays, Charts, Charts, Excel, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

7 Responses to When is an XY chart not an XY chart …?

  1. Bill Harvey says:

    Can’t remember the exact form but you need to put #N/A in the cell. Bill


  2. dougaj4 says:

    Hi Bill – that stops it turning into a line chart, but then it joins the points either side of the “blank” cells.


  3. Andy Pope says:

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


    • dougaj4 says:

      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.


  4. Andy Pope says:

    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.


  5. dougaj4 says:

    Andy – well certainly Excel says it is an XY chart if you ask it🙂


  6. Pingback: Charts and Charting | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s