Getting Excel to Chart Stair-step Values


Excel does not easily chart amounts that are constant over a range and abruptly change to another value (called a stair-step). It requires the use of the Scatter chart and some changes to how the data is arranged. This post describes how the charts for the Canadian Marginal Tax Rates – 2014 post were created in the hope that others can use the technique to deal with similar data.

Data to be Charted

Here is the combined Federal and Manitoba tax rates, as used in Canadian Marginal Tax Rates-2014, to be charted:

LineSourceFromToFed+MBAverage
1MB031,00025.80%25.80%
2Fed31,00143,95327.75%26.37%
3MB43,95467,00034.75%29.25%
4Fed67,00187,90739.40%31.67%
5Fed87,908136,27043.40%35.83%
6136,271235,00046.40%40.27%

 

And here is the chart that we will be creating for the above data:

Canadian Marginal Tax Rates - 2014 Manitoba

Column and Line Charts are Not Acceptable

Two common charts, Column and Line,  were tried. However, they are plotted differently as shown in the figures. For example, the percentages (along the Y-axis) and dollar amounts (along the X-axis) in a Column chart will show the columns centered around the X-axis amount. For a Line chart the line breaks at the X-axis amount.

While the data to be charted has From and To amounts, Excel can only plot one of them and the Column chart implies that the percent is only for that amount. It is just as bad for a Line chart which shows that there is a change between the amounts. Also note that the X-axis amounts are not spaced by relative values.

Scatter Charts are Also Not Acceptable

The problem is how to generate a chart that shows the ranges, with the amounts properly spaced along the X-axis and has the stair-steps shown properly. I tried a Scatter chart to get the proper spacing between the To values. However, when a ‘Straight Line’ scatter chart is selected the X-axis is properly spaced, but the line does not show the steps.

Changing the Data for the Scatter Chart

To make the scatter chart show the steps, the data needs to be modified to contain the stair-steps. Basically, additional rows are needed that add points to the graph for the steps. This was done by inserting rows that have almost the same values as the row above. Only the To value was changed to match the From of the row above. Also, all of the columns are not needed. To keep the original data intact, the needed columns were copied to a new place on the spreadsheet and the values only were pasted. Here is what the final data looks like with the changes shown in Bold.

LineSourceFromToFed+MBAverage
1MB031,00025.80%25.80%
Inserted31,00031,00025.80%25.80%
2Fed31,00143,95327.75%26.37%
Inserted43,95343,95327.75%26.37%
3MB43,95467,00034.75%29.25%
Inserted67,00067,00034.75%29.25%
4Fed67,00187,90739.40%31.67%
Inserted87,90787,90739.40%31.67%
5Fed87,908136,27043.40%35.83%
Inserted136,270136,27043.40%35.83%
6136,271235,00046.40%40.27%
Inserted235,000235,00046.40%40.27%

 

It is this new data that is to be plotted using a Scatter chart. However, in this new chart the From column is used as the X-axis instead of the To column. A Scatter chart was created using the Chart tab in Excel and the Straight-Line Scatter option. The empty chart was selected and the Source Data item on the Charts menu was selected. In the window that opens, the cells used were as follows: Chart data range is the data in the Fed+MB column, Name is the Fed+MB cell, X-values is the data in the From column. Once this is OK’d the stair-step line appeared on the chart.

From the Charts tab, Vertical Grid lines were added, the Scale of the Y-Axis was set to have the Minimum set to 0.2 rather than Automatic and the Title was removed. This resulted in the chart shown.

Adding the Average Line to the Scatter Chart

To get the Average line onto the chart, new data must be added. To do this, the chart was selected and from the Chart menu the Source Data was selected. In the dialog that appears, the Add button was clicked and the fields were filled as follows: and fill select the Average column from just below the header to the last line. Another stair-step line will appear on the chart, which not what is required. The formula for the line must be changed as follows:  Name is the Average cell, X-values is the data in the To column and Y-values is the data in the Average column.

The Average line starts at the end of the first step and not from 0 because there is no 0 value in the To column. While it is not really needed because it will overlay the other line, if you want it to be there, you have to insert a row just below the header. This new row only needs 2 values: a 0 in the To column and the first step’s value in the Average column. The formula for the line must be changed to include the new row.  The row should look like this:

LineSourceFromToFed+MBAverage
025.80%

 

Hope this helps.