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.

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


    • Lori says:

      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


      • dougaj4 says:

        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?


        • Lori says:

          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))

          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:


          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.


        • dougaj4 says:

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


        • Lori says:

          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…


  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.


      • Jon Peltier says:

        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.


  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

  7. Pingback: Plotting Charts With Gaps … | Newton Excel Bach, not (just) an Excel Blog

  8. Bill Harvey says:

    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.


    • dougaj4 says:

      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.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s