In Mind the Gap – Charting Empty Cells, I described in gory detail how Excel’s various chart types treat empty cells, that is, cells which 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 which 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, wither 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 documentaiton 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 nonnumeric X values are allowed for other chart types.

The first procedure loops through the parsed X and Y values, and where it finds a nonnumeric 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 nonnumeric value, it clears the cell containing the nonnumeric 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 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 nonnumeric cells now contain numeric values, but we’ve gained text values in rows 4 and 5. Because the previously nonnumeric 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, wither 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:

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