Peltier Technical Services, Inc.
 

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


Peltier Tech Chart Utility

 

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
 

Additional 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

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.50 X
21-Mar-2000 5.75 X
16-May-2000 6.00 X
3-Jan-2001 6.50 X
31-Jan-2001 6.00 X
20-Mar-2001 5.50 X
3-Jan-2000 5.50  
2-Feb-2000 5.75  
21-Mar-2000 6.00  
16-May-2000 6.50  
3-Jan-2001 6.00  
31-Jan-2001 5.50  
20-Mar-2001 5.00  
 

As Sorted by Line Chart

3-Jan-2000 5.50  
2-Feb-2000 5.50 X
2-Feb-2000 5.75  
21-Mar-2000 5.75 X
21-Mar-2000 6.00  
16-May-2000 6.00 X
16-May-2000 6.50  
3-Jan-2001 6.50 X
3-Jan-2001 6.00  
31-Jan-2001 6.00 X
31-Jan-2001 5.50  
20-Mar-2001 5.50 X
20-Mar-2001 5.00  

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


Follow Up

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

 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile