Convert Line Chart to Step Chart with VBA

I’ve written a tutorial about making Step Charts in Excel. Given a timeline of points where a value is set and where that value is constant until the next time it is changed, I showed how to build a line chart that showed the periods between changes as horizontal lines, and the changes as vertical lines. I will show how to use VBA to convert a line chart to a step chart.

Review: Build a Step Chart by Hand

For example, here is a table and chart of first class postage rates in the US. Obviously, connecting the data points with diagonal lines does not reflect the price between points.

Here is the original data again, and I’ve added dummy points at the beginning and end of the arbitrary time period I’m watching.

I’ll start my chart data range using all but the first X values (dates) and all but the last X values. These points stagger the values to one date later.

We need to connect the two sets of points: a horizontal line from each blue (original) point to the next orange (staggered point), and a vertical line from each orange point to the next blue point.

And we don’t need those markers.

If we append the intact original data range below the staggered data, and make the chart using this new extended range, Excel internally sorts the dates, and produces the steps we want.

Using VBA with the SERIES Formula

This isn’t the most difficult chart protocol I’ve ever described, but it requires an extended data range and a bit of work. Wouldn’t it be nice to run a simple macro and make it happen?

We’re going to do just that, with a short VBA procedure which will hack the chart SERIES formula to mimic generating the extended data range above. I’ve written about the SERIES formula in The Excel Chart SERIES Formula, and I wrote a tutorial about using VBA to hack the SERIES formula in Switch X and Y Values in a Scatter Chart.

If I select the chart series, I can see the series formula in Excel’s Formula Bar. (The artificial endpoints are colored red.)

The formula looks like this:

=SERIES(Postage!$C$2,Postage!$B$3:$B$17,Postage!$C$3:$C$17,1)

The four arguments are:

=SERIES(SeriesName,XValues,YValues,Index)

As shown in the manual approach above, I need to combine all but the first X value with all of the X values, and all but the last Y value with all of the Y values. In the series above, our X values will become

Postage!$B$4:$B$17 and Postage!$B$3:$B$17

and our Y values will become

Postage!$C$3:$C$16 and Postage!$C$3:$C$17

You can use multiple-area ranges if you separate each area’s address with commas, and surround them with parentheses:

(Postage!$B$4:$B$17,Postage!$B$3:$B$17)
(Postage!$C$3:$C$16,Postage!$C$3:$C$17)

Our final SERIES formula will look like this:

=SERIES(Postage!$C$2,(Postage!$B$4:$B$17,Postage!$B$3:$B$17),
(Postage!$C$3:$C$16,Postage!$C$3:$C$17),1)

Outline the VBA Procedure

• Start with the active chart (and abort if there is none).
• Get the SERIES formula for each series.
  =SERIES(SeriesName,XValues,YValues,Index)
• Split this formula at the commas to create an array.
  {"=SERIES(SeriesName","XValues","YValues","Index)"}
• Find the range of X values, then find the range from the second cell to the last, get the address, and append the address of the original complete range.
• Find the range of Y values, then find the range from the first cell to the next-to-last, get the address, and append the address of the original complete range.
• Finally, put the new X and Y references into the array, and join them into a new SERIES formula, and put that into the chart.

The VBA Procedure

Here is the procedure, which took me about 15 minutes to write.

Sub LineToStepChart()
  If Not ActiveChart Is Nothing Then
    Dim iSrs As Long
    For iSrs = 1 To ActiveChart.SeriesCollection.Count
      Dim srs As Series
      Set srs = ActiveChart.SeriesCollection(iSrs)
      Dim nPts As Long
      nPts = srs.Points.Count
      
      Dim SrsFmla As String
      SrsFmla = srs.Formula
      '' =SERIES(name,xvalues,yvalues,number)
      
      Dim vFmla As Variant
      vFmla = Split(SrsFmla, ",")
      '' array {"=SERIES(name","xvalues","yvalues","number)"}
      
      If iSrs = 1 Then
        ' only needed on first series since all series use the same X values
        ' fix X values: all but first point then all points
        Dim sXVals As String
        sXVals = vFmla(LBound(vFmla) + 1)
        Dim sXSheet As String
        sXSheet = Left(sXVals, InStr(sXVals, "!"))
        
        Dim rXVals As Range
        Set rXVals = Range(sXVals)
        Set rXVals = Range(rXVals.Cells(2), rXVals.Cells(nPts))
        Dim sNewXVals As String
        sNewXVals = "(" & sXSheet & rXVals.Address & "," & sXVals & ")"
      End If
      
      ' fix Y values: all but last point then all points
      Dim sYVals As String
      sYVals = vFmla(LBound(vFmla) + 2)
      Dim sYSheet As String
      sYSheet = Left(sYVals, InStr(sYVals, "!"))
      
      Dim rYVals As Range
      Set rYVals = Range(sYVals)
      Set rYVals = Range(rYVals.Cells(1), rYVals.Cells(nPts - 1))
      Dim sNewYVals As String
      sNewYVals = "(" & sYSheet & rYVals.Address & "," & sYVals & ")"
      
      '' array {"=SERIES(name","new xvalues","new yvalues","number)"}
      vFmla(LBound(vFmla) + 1) = sNewXVals
      vFmla(LBound(vFmla) + 2) = sNewYVals
      
      SrsFmla = Join(vFmla, ",")
      '' =SERIES(name,new yvalues,new xvalues,number)
      
      srs.Formula = SrsFmla
    Next
  End If
End Sub

After selecting the last chart above (with markers removed) and running this procedure, here is the resulting chart and series formula:

Note that this only works for Line or Area charts, for X values which are dates or at least treated numerically. Line charts sort numerical X values before plotting them, which makes this macro approach so easy. If the X values are text (non-numeric labels), the following chart results:

It did what I specified, but with a text axis, it failed to produce steps. We could test for the right kind of axis, and the right kind of data, but that’s a lot of work for a quick and dirty program.

Step Charts in Peltier Tech Charts for Excel

Step Charts are included in the many custom chart types available in Peltier Tech Charts for Excel 4.0.

Click the button to open the Step Chart dialog, or click the dropdown arrow. The first menu item also opens the dialog, while the second will convert your existing chart to a step chart using an approach like the one above, but with more error checking and flexibility built into it. Actually, it converts a copy of your chart, in case you change your mind.

The Step Chart dialog lets you select your data range and specify its characteristics, then select a chart type.

The result looks great, but it’s much easier than the manual approach. The chart is built on a new worksheet because of the data manipulation required; below I’ve simply pasted the chart next to the original data.

Even if your X values are non-numeric text, the program will give you a step chart.

Visit Peltier Tech Charts for Excel 4.0 to purchase a license to this powerful Excel add-in.