VBA

Note

A new feature in Office 365 (and Excel 2019), Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

Read about it in Plot Blank Cells and #N/A in Excel Charts.

This new behavior in Excel makes this article obsolete.

In Mind the Gap – Charting Empty Cells, I described in gory detail how Excel’s various chart types treat empty cells, that is, cells that are totally empty. I also described why none of the approaches we mortals have ever tried to produce a gap across a simulated blank cell has ever worked.

Cells that have formulas that return a null string (i.e., “”) are plotted like any text, with a marker at a value of zero. In line and XY Scatter charts, an error value of #N/A almost works, because it suppresses plotting of a point, but does not produce a gap in the line segments connecting points.

The only way to get a gap in the lines of a chart is to have empty cells is to rely on a VBA routine to correct the appearance of the chart, whether by changing the formatting of the points and lines you want to hide, or by clearing the contents of the associated cells. These work well, but must be repeated every time the data changes, and if you’ve cleared any cells, their contents must be recreated in case those points should no longer show gaps.

This post contains two routines to fix the chart’s appearance, one by changing the chart series formatting, the other by changing the data.

The Gap Problem

Below is a simple data range I created to illustrate this problem. The “Broken” column shows a set of random values centered around 3, with either #N/A or “x” inserted into random cells in place of the random values. The “x” is text, so it will be treated the same as “”, but it is used instead so it shows up in the cells.

In the “ChartFix” and “DataFix” I’ve included this same data, increased by 1 so I could plot it with the “Broken” data without obscuring it. I’ll use two XY Scatter charts, one to show how fixing the chart formatting works, the other to show how fixing the data works.

Here are the two charts I promised. The lower (blue) series is the “Broken” data, which will not be changed by these routines. The upper (orange) series is the “ChartFix” series (left) or the “DataFix” series (right).

Both series in both charts show a missing marker at X=5, with a line connecting the points at X=4 and X=6. These missing points correspond to #N/A errors in the corresponding cells. All four series also show a point plotted at X=15, Y=0, corresponding to the “x” value in the corresponding cells.

We want our code to leave no markers and no connecting lines at these points in the orange series, while the blue series remain intact to remind us where changes were made. In general, you will probably want to process all series in your charts.

The Code

Both procedures parse the series formula (see detailed documentation at the end of this article) to find the ranges containing the X and Y values. If you are not using XY Scatter charts, remove the X value components of the code, because non-numeric X values are allowed for other chart types.

The first procedure loops through the parsed X and Y values, and where it finds a non-numeric value, it formats the point to have no marker and for the connecting line segment on either side to show no line. Where it finds a numeric value, it restores marker and line segment formatting, in case the chart already was “fixed” but now the data has changed.

Sub FixLineFormatInChart()
  Dim iPt As Long
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sXVals As String
  Dim sYVals As String
  Dim rXVals As Range
  Dim rYVals As Range
  Dim vXVals As Variant
  Dim vYVals As Variant

  With ActiveChart
    ' just process the orange series
    With .SeriesCollection(2)
      sFmla = .Formula
      vFmla = Split(sFmla, ",")

      sXVals = vFmla(1)
      sYVals = vFmla(2)

      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      vXVals = rXVals.Value
      vYVals = rYVals.Value

      For iPt = 1 To .Points.Count
        If IsNumeric(vXVals(iPt, 1)) And IsNumeric(vYVals(iPt, 1)) Then
          .Points(iPt).MarkerStyle = .MarkerStyle
        Else
          .Points(iPt).MarkerStyle = xlMarkerStyleNone
        End If
      Next

      For iPt = 2 To .Points.Count
        If IsNumeric(vXVals(iPt - 1, 1)) And IsNumeric(vXVals(iPt, 1)) And _
            IsNumeric(vYVals(iPt - 1, 1)) And IsNumeric(vYVals(iPt, 1)) Then
          .Points(iPt).Format.Line.Visible = True
        Else
          .Points(iPt).Format.Line.Visible = False
        End If
      Next
    End With
  End With
End Sub

The second procedure loops through the parsed X and Y values, and where it finds a non-numeric value, it clears the cell containing the non-numeric value. When rerun on a chart with changed data, it cannot restore the appropriate cell contents where it finds a numeric value, because the relevant formula or value was previously deleted.

If you plan to use this approach, it is best to leave the original data or calculations intact, and use another worksheet range that simply links to the original data, so the links are easy to recreate.

Sub FixSourceDataInSheet()
  Dim iPt As Long
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sXVals As String
  Dim sYVals As String
  Dim rXVals As Range
  Dim rYVals As Range
  Dim vXVals As Variant
  Dim vYVals As Variant

  With ActiveChart
    ' just process the orange series
    With .SeriesCollection(2)
      sFmla = .Formula
      vFmla = Split(sFmla, ",")

      sXVals = vFmla(1)
      sYVals = vFmla(2)

      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      vXVals = rXVals.Value
      vYVals = rYVals.Value

      For iPt = 1 To .Points.Count
        If Not IsNumeric(vXVals(iPt, 1)) Then
          rXVals.Cells(iPt).ClearContents
        End If
        If Not IsNumeric(vYVals(iPt, 1)) Then
          rYVals.Cells(iPt).ClearContents
        End If
      Next
    End With
  End With
End Sub

The Results

I selected the first chart (title = “Change Chart”) and ran the first procedure (FixLineFormatInChart). Then I selected the second chart (title = “Change Data”) and ran the second procedure (FixSourceDataInSheet). Here is the resulting data. Note that the ChartFix data is unchanged because the FixLineFormatInChart procedure only changes the chart, while the DataFix data now has a couple of blank cells because the FixSourceDataInSheet works by changing the data and leaving the chart alone.

Here are the resulting charts. The two procedures produce identical results, changing the interpolated line across point 5 (the #N/A value) to a gap, and changing the plotted zero at point 15 (the text label “x”) to a gap. The interpolated line across point 5 and the zero value plotted at point 15 remain in the blue series.

What if the Data Changes?

Worksheets are not always static pictures of data. The original data may change, or you may perform the same analysis with new data. The following table represents new data overlaid on the previous data range.

The original non-numeric cells now contain numeric values, but we’ve gained text values in rows 4 and 5. Because the previously non-numeric cells in column D were cleared, whatever formulas we had there could not recompute new values for cells D6 and D16. In the charts from before, we still see gaps, whether because we formatted them not to appear (left) or because we deleted the values (right).

We’re fine if we used the Change Chart approach, or if we simply pasted values on top of the data, which filled in any blank cells from before. But if we deleted formulas, as in column D above, we need to restore them, as shown in the worksheet range below.

The chart with reformatted points still shows gaps (left) but the formatting will be restored to numeric points by the procedure. The chart with unchanged formatting and restored data (right) shows all markers and line segments. The text values are plotted as the two zeros near the left edge of these charts.

We run the corresponding procedures on the two charts. The data is changed in column D, as before.

The orange series in both charts now show gaps where the blue series remind us of the text values plotted as zero.

Parsing the Series Formula

A series formula has the following form:

=SERIES(Sheet1!$C$2,Sheet1!$A$3:$A$19,Sheet1!$C$3:$C$19,2)

The four arguments of the series formula are:

1. Series Name – can be a worksheet address or defined name, a text label, or empty. In this example it’s a cell reference, Sheet1!$C$2.
2. X Values (Category Labels) – can be a worksheet address or defined name, a literal array such as {1,2,3} or {“A”,”B”,”C”}, or empty. In this example it’s a worksheet address, Sheet1!$A$3:$A$19.
3. Y Values – can be a worksheet address or defined name, or a literal array such as {1,2,3}. In this example it’s a worksheet address, Sheet1!$C$3:$C$19.
4. Plot Order – an integer, in this case 2.

Generally, to parse the series formula, you first need to strip off everything except the parameters, which means the open parenthesis and the preceding text, and the closing parenthesis. Then you split the resulting string into a comma-separated array. This works as long as none of the ranges contain multiple areas, in which case there are commas separating the addresses of the individual areas, and simple separation by commas will produce surprises. The result is a zero-based array.

Since we only want the X and Y values, we don’t care that the first parameter will contain the opening parenthesis and the preceding text, or that the last parameter will contain the closing parenthesis.

So our code looks like this, with loads of nice documentation:

    With .SeriesCollection(2)
      ' get series formula
      ' =SERIES(Sheet1!$C$2,Sheet1!$A$3:$A$19,Sheet1!$C$3:$C$19,2)
      sFmla = .Formula

      ' split formula into its arguments
      vFmla = Split(sFmla, ",")
      ' vFmla(0) = "=SERIES(Sheet1!$C$2"
      ' vFmla(1) = "Sheet1!$A$3:$A$19"
      ' vFmla(2) = "Sheet1!$C$3:$C$19"
      ' vFmla(3) = "2)"

      ' get the individual addresses for X and Y
      sXVals = vFmla(1)
      sYVals = vFmla(2)

      ' find the ranges containing the X and Y values
      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      ' put the values from the X and Y ranges into arrays
      ' (faster processing in an array than cell-by-cell in a range)
      vXVals = rXVals.Value
      vYVals = rYVals.Value

Now we can test for non-numeric values in the arrays, and either clear the corresponding cells or change the formatting of the plotted points.

You can put the X and Y values of a series directly into an array as follows:

      vXVals = ActiveChart.SeriesCollection(2).XValues
      vYVals = ActiveChart.SeriesCollection(2).Values

The problem here is that the values saved internally in the series have already been changed. The #N/A remains #N/A in the array, but any blanks and any text values are converted to zeros, so they are undetectable as non-numeric.