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 DataThis 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 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.
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 BarsOne 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.
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 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.
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.
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 Approach 2: Line Chart with Repeated RangesUntil 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.
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.
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.
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)
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.
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.
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.
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.
Comparison of the Two Techniques
Charting the Complete Data Set
Follow UpIn 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. |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2014. All rights reserved. |