### Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Excel Dashboards

Books at Amazon.com

# Step Charts in Microsoft Excel

Step charts are useful for showing a quantity which changes intermittently, but remains constant between these changes. Examples of data that benefits from being plotted in a step chart include interest rates vs. time and tax rates vs. income. Excel has no native step chart capability, but this article describes two techniques for manufacturing step charts. The first technique uses an XY chart with custom error bars, while the second uses a line chart with dual overlapping ranges for its source data.

##### The Data

This example tracks the interest rates set by the Federal Reserve Bank (the Fed) since the start of 2000. For simplicity, we'll start with the subset of the data shown below, in a worksheet named "Data". To make the chart dynamic (i.e., so it includes new data when it is added), we need to define the names in the table below right and use them for the chart's source data (see Dynamic Charts elsewhere on this web site). To define a name, go to the Insert menu, select Names, then Define. In the Define Name dialog, enter the name in the Name box, and enter the appropriate formula in the Refers To box, and click Add.

The Worksheet Data

 A B 1 Date Fed 2 3-Jan-2000 5.50 3 2-Feb-2000 5.75 4 21-Mar-2000 6.00 5 16-May-2000 6.50 6 3-Jan-2001 6.00 7 31-Jan-2001 5.50 8 20-Mar-2001 5.00

The Dynamic Ranges

 Name Refers To FedLabel =Data!\$B\$1 Fed =OFFSET(FedLabel,1,0,COUNT(Data!\$B:\$B),1) Dates =OFFSET(Fed,0,-1)

Note: the data must be sorted by date in ascending order.

The Dynamic Chart

Start the Chart Wizard. In step 1, select a Line or XY chart type. In step 2, select the Series tab and click on the Add button. In the Name box, enter =Data!FedLabel. In the Values or Y Values box, enter =Data!Fed. In the Category (X) Axis Labels or X Values box, enter =Data!Dates. Press Finish to see the chart. The series formula is:

`=SERIES(Step2.xls!FedLabel,Step2.xls!Dates,Step2.xls!Fed,1)`

In this formula, Excel has changed the reference of each name from Data!Name to Step2.xls!Name. The name is defined for the entire workbook, so the workbook name is the appropriate qualifier. However, Excel accepts either prefix when used to specify source data, because it refers to a range on the specified worksheet. In the previous paragraph, you could have used either =Data!Name or =Step2.xls!Name when specifying source data for the chart.

You may have to adjust the X axis scale (double click the axis, and select the Scale tab). For a Line chart, you should change the Base Unit to Days and the Major Unit to 3 Months. For an XY chart, it's a little trickier to try to get a tick on the first day of each quarter; I empirically optimized the axis with a Minimum of 36526.4, a Maximum of 36982.5, and a Major Unit of 91.2 (36526 is the serial number of 1 January 2001, while 36982 is 1 April 2001). It's still not perfect; notice that 1/1/01 is replaced by 12/31/01. See X Axis: Category vs. Value for a discussion of different X axis types.

Dynamic XY Chart as Precursor to Step Chart

This is only part of the chart. We need to convert the diagonal line segments to horizontal and vertical segments, and hide the data markers.

##### Step Chart Approach 1: XY Chart with Error Bars

One way to get horizontal and vertical lines from data points is with error bars. You don't need to add points to get the additional segments. However, you do need to add formulas to compute custom error bar values, and you need to assign error bars to the series. Because horizontal (X) error bars are needed, the chart must be an XY chart with its inferior date scale axis, not a Line chart.

Error Bar Data

Two columns of data must be added: Duration for the horizontal error bars, and Height for the vertical error bars. Duration is simply the difference between the date in this row and that in the following row. Height is the change in the Y axis variable. These ranges and the formulas that define them are shown below; the formulas in C2 and D3 must be filled down to the end of the range. The ranges are given names to make updating dynamic.

The Expanded Data

 A B C D 1 Date Fed Duration Height 2 3-Jan-2000 5.50 30 0 3 2-Feb-2000 5.75 48 -0.25 4 21-Mar-2000 6.00 56 -0.25 5 16-May-2000 6.50 232 -0.5 6 3-Jan-2001 6.00 28 0.5 7 31-Jan-2001 5.50 48 0.5 8 20-Mar-2001 5.00 0 0.5

Formulas and Names

 Cell Formula C2 =IF(A3>A2,A3-A2,0) D2 0 D3 =B2-B3

 Name Refers To Duration =OFFSET(Fed,0,1) Height =OFFSET(Fed,0,2)

Adding the Error Bars

Double click the data series in the chart, then select the X Error Bars tab. Click the Plus icon (see the dialog below), then in the Custom + box, enter =Data!Duration to assign the Duration range defined above to the horizontal error bars. Click on the Y Error Bars tab, click on the Plus icon, then in the Custom + box, enter =Data!Height.

The default style of Excel's error bars is like a "T": a line with another short perpendicular line capping the line. The following chart shows only the horizontal default error bars, for clarity. The alternate style, a plain line without the end cap, is obtained by double clicking the error bars, and choosing the appropriate style in the Patterns tab of the dialog.

Default "T" Error Bar Format

The XY chart with both sets of error bars is shown below. The X error bars are shown in green, the Y error bars in red.

Step Chart Almost Complete

Notice in the XY step chart with error bars that there is one data point per pair of corners; the second corner is where the error bars from consecutive points intersect. Double click the chart series, and on the Patterns tab, select None for Markers and for Lines.

Step Chart: XY Chart with Error Bars

##### Step Chart Approach 2: Line Chart with Repeated Ranges

Until recently I considered only two techniques for building step charts: the XY plus error bars approach described above, and manually (i.e., tediously) inserting extra data into the worksheet to allow for one point per corner. But in February 2006, Michel Gerday of Belgium described the following technique in the Microsoft Excel Charting newsgroup, and his technique automatically adds the extra data to a line chart.

This chart is a line chart constructed with the same ranges as the XY chart in the previous section. Notice the Date-Scale axis with a label at the first of each month, which was not achievable with the XY chart.

Dynamic Line Chart as Precursor to Step Chart

We can't use horizontal error bars to build our steps, because Line charts only allow vertical (Y) error bars. The chart must be built with one data point per corner, so we need to name more ranges to add the extra points to the chart. The extra points need the rate from the earlier point and the date from the later point.

The left grid below shows the definitions of the original Dates and Fed names used in the XY chart above. The right grid shows how the additional names DatesX and FedX are defined within the same data range. The new names are staggered by one row from each other for the corners to be appropriately plotted; notice how the added date range starts one row lower than the added rate range.

Original Ranges

 A B 1 Date Fed 2 3-Jan-2000 5.50 3 2-Feb-2000 5.75 4 21-Mar-2000 6.00 5 16-May-2000 6.50 6 3-Jan-2001 6.00 7 31-Jan-2001 5.50 8 20-Mar-2001 5.00

 A B 1 Date Fed 2 3-Jan-2000 5.50 3 2-Feb-2000 5.75 4 21-Mar-2000 6.00 5 16-May-2000 6.50 6 3-Jan-2001 6.00 7 31-Jan-2001 5.50 8 20-Mar-2001 5.00

The defined name formulas are shown in the tables below. There are two ways to define the names. The first way, which is probably more intuitive, is to define all of the names independently, based on a count of values in each column. Though intuitive, when the data changes, the names update independently, so the chart's lines become skewed until both values have been entered. Although this is normal behavior, it can be disconcerting to the users.

A more advanced, interrelated approach defines one range based on the number of values in one column, then bases the other names on this one. This approach is actually easier, and it is more reliable. In addition, the first range is in the first column, so it is the one that is likely populated before the others. When data is added, the chart updates in an expected fashion.

Defined Name Formulas - Independent Names

 Name Refers To FedLabel =Data!\$B\$1 Dates =OFFSET(Data!\$A\$1,1,0,COUNT(Data!\$A:\$A),1) Fed =OFFSET(Data!\$B\$1,1,0,COUNT(Data!\$B:\$B),1) DatesX =OFFSET(Data!\$A\$1,2,0,COUNT(Data!\$A:\$A)-1,1) FedX =OFFSET(Data!\$B\$1,1,0,COUNT(Data!\$B:\$B)-1,1)

Defined Name Formulas - Interrelated Names

 Name Refers To FedLabel =Data!\$B\$1 Dates =OFFSET(Data!\$A\$1,1,0,COUNT(Data!\$A:\$A),1) Fed =OFFSET(Dates,0,1) DatesX =OFFSET(Dates,1,0,COUNT(Dates)-1,1) FedX =OFFSET(Fed,0,0,COUNT(Fed)-1,1)

Start the Chart Wizard. In step 1, select a Line chart type. In step 2, select the Series tab and click on the Add button. In the Name box, enter =Data!FedLabel. In the Values box, enter =(Data!FedX,Data!Fed). In the Category (X) Axis Labels box, enter =(Data!DatesX,Data!Dates). Press Finish to see the chart. The series formula is:

`=SERIES(Step2.xls!FedLabel,(Step2.xls!DatesX,Step2.xls!Dates),(Step2.xls!FedX,Step2.xls!Fed),1)`

Step Chart Nearly Finished

Unlike the XY step chart, every angle in the line chart has a data point associated with it. To hide the data point markers, double click on the series, and on the Patterns tab of the dialog, select None for Markers.

Line Chart as Step Chart

To get a chart with the area under the step lines filled in, simply use an Area type rather than a Line type for your step chart.

Area Chart as Step Chart

How it Works

The data for the line chart is not sorted, yet the step chart comes out correct. This is because of the way a date-scale line chart processes its data. The points are connected in left-to-right order of the points' dates, not in the order in which the points are listed in the source data. The order of the points in the source data, first DatesX and FedX, then Dates and Fed, is shown at left in the table below; the added points are indicated by an "X" in the third column. The order in which the points are plotted is shown at right. Because of the staggering of FedX and DatesX, the first date is listed once and the rest twice; the last Y value is listed once, and the rest are listed twice. Notice how the added points, marked with "X", appear between the appropriate points, matching the Rate in the row above and the Date in the row below.

As Entered

 2-Feb-2000 5.5 X 21-Mar-2000 5.75 X 16-May-2000 6 X 3-Jan-2001 6.5 X 31-Jan-2001 6 X 20-Mar-2001 5.5 X 3-Jan-2000 5.5 2-Feb-2000 5.75 21-Mar-2000 6 16-May-2000 6.5 3-Jan-2001 6 31-Jan-2001 5.5 20-Mar-2001 5

As Sorted by Line Chart

 3-Jan-2000 5.5 2-Feb-2000 5.5 X 2-Feb-2000 5.75 21-Mar-2000 5.75 X 21-Mar-2000 6 16-May-2000 6 X 16-May-2000 6.5 3-Jan-2001 6.5 X 3-Jan-2001 6 31-Jan-2001 6 X 31-Jan-2001 5.5 20-Mar-2001 5.5 X 20-Mar-2001 5

The chart below shows how an Excel XY chart connects points in the order in which they are listed (above left). The points are all in the right place, but the connecting lines are all wrong. This data would have to be sorted before it can be passed to an XY chart.

XY Chart using Line Chart Data

##### Comparison of the Two Techniques
 XY Chart with Error Bars Line Chart Advantages Advantages Data ranges easier to visualize Simpler: No error bar construction "Nice" axis date scale built in Same technique works with area chart Disadvantages Disadvantages Calculation required for error bar ranges "Nice" axis date scale hard or impossible Defined name approach fragile in Excel 2003 Data list may corrupt chart link to names

##### Charting the Complete Data Set
 A B 1 Date Fed 2 3-Jan-2000 5.50 3 2-Feb-2000 5.75 4 21-Mar-2000 6.00 5 16-May-2000 6.50 6 3-Jan-2001 6.00 7 31-Jan-2001 5.50 8 20-Mar-2001 5.00 9 18-Apr-2001 4.50 10 15-May-2001 4.00 11 27-Jun-2001 3.75 12 21-Aug-2001 3.50 13 17-Sep-2001 3.00 14 2-Oct-2001 2.50 15 6Nov-2001 2.00 16 11-Dec-2001 1.75 17 6-Nov-2002 1.25 18 25-Jun-2003 1.00 19 30-Jun-2004 1.25 20 10-Aug-2004 1.50 21 21-Sep-2004 1.75 22 10-Nov-2004 2.00 23 14-Dec-2004 2.25 24 2-Feb-2005 2.50 25 22-Mar-2005 2.75 26 3-May-2005 3.00 27 30-Jun-2005 3.25 28 9-Aug-2005 3.50 29 20-Sep-2005 3.75 30 1-Nov-2005 4.00 31 13-Dec-2005 4.25 32 31-Jan-2006 4.50 33 17-Feb-2006 4.50

Having defined dynamic ranges for the chart data, all we need to do is paste in the rest of the data to extend the chart to cover the complete data range. The complete range is shown in the table below left, and the two chart types are shown below right for this entire data range.

Full Data Set in an XY Chart with Error Bars

Full Data Set in a Line Chart

Full Data Set in an Area Chart

In my blog, I've posted about Excel step charts in Line Chart vs. Step Chart and Step Chart Without Risers. Both versions of the step chart use line charts, and for both I've showed easier ways to prepare the chart source data range.

Step Chart using Excel Line Chart

Step Chart without Risers using Excel Line Chart