How to create a step chart in Excel

A step chart is used to show data which changes at specific points, then remains consistent until the next change occurs.  Interest rates set by central banks across the world follow this system.  Some dudes in suits meet together on a particular day and decide the rate should change.  That change happens instantly and remains in place until those same dudes (probably in the same suits) decide to change it again.  A standard line chart is not suitable for this, but a step chart is a perfect option.

Line Chart vs. Step Chart

The data below shows the Bank of England interest base rates as set for the past ten years.  Using a line chart will distort the values as shown below.  Between any two points the line gradually increases or decreases.  Pick any date between the points and the value displayed on the chart will be incorrect.

A step chart, as shown below, would be the right way to display the data.  Pick any date and the chart will show the correct value.

Whilst a step chart is not a default chart type it is easy enough to create.

Data concepts

The source data has only one data point at each date.  But, we should think of a step chart as a line chart with two data points, both happening at the same time.  The first data point at each step is the value before the change; the second data point is the value after the change.

If our data were constructed as follows it would be perfect for a step chart.

Notice in the screenshot above how all the dates are in order and each date has two values, the value before the change and the value after the change.

But don’t start moving your data around quite yet.  Excel will sort the data automatically, so it does not need to be presented in the exact order.  Of the two data points happening at each step, provided the first data point (the value before the change) occurs in the data set before the second data point (the value after the change) the chart will display correctly.

With this concept in mind, there are two ways to create a step chart (1) change the data to match the chart (2) use a non-contiguous data range in the chart source.

Knowing what we now know, there are two options:

1. Change the data to match the chart
2. Non-contiguous data range used in the chart

Option 1: Change the data to match the chart

Follow these steps create a step chart by changing the data.

1. Add a copy of the data below the original data.
   
2. Within the original section of data delete the first date cell and the last value cell (as highlighted in the screenshot below).
   
   (To delete individual cells, right click on the cell and select Delete… from the menu.  From the Delete window select “Shift cell up” before clicking OK.)
3. Create a line chart as normal – it will display as a step chart.

Option 2: Non-contiguous data range used in the chart

When working with ranges in Excel, the comma ( , ) is an important character.  The comma creates a non-contiguous range, which is a range of cells not contained within a single border.  It sounds more confusing than it really is, and the good news is you don’t need to understand it to use it.

Source data in an Excel chart can be non-contiguous, therefore we can create a step chart without having to change any of the data.

1. Create a line chart as usual, using the original data
2. Right-click on the chart, click Select Data… from the menu.
   
3. Select the data series and click Edit.
   
4. Change the series values to be all the values except the last cell, followed by a comma ( , ) , then all the values again (but this time including the last cell).  Then click OK.
   
   The in our example, the series values are now as follows:

   =Sheet1!$B$2:$B$12,Sheet1!$B$2:$B$13

5. Click Edit for the Axis Labels.
   
6. Change the axis label range to be all the cells containing the labels except the first cell followed by a comma ( , ) , then all the cells again (but this time including the first cell).  Then click OK.
   
   In our example the Axis label range is now:

   =Sheet1!$A$3:$A$13,Sheet1!$A$2:$A$13

7. Click OK to close the Select Data Source window

That’s it.  You now have a Step Chart without needing to change your data.

How to make it update with new data automatically

Over time there will be new data to add to the Step Chart.  Whilst it is not difficult to update it manually, it would be better if it were completely dynamic.

Turn the data into an Excel Table by selecting the data and clicking Home -> Format As Table (Shortcut: Ctrl + T).

The Create Table window will open, check the My table has headers setting is correct, then click OK.

The style of the cells will change.

Let’s change the name of our Table.  Click a cell in the Table, then change the name in the Design -> Properties -> Table Name option.  I have chosen Data as a suitable name.

From this Table we need to create four Named Ranges.  To create a Named Range click Formulas -> Define Name.

The New Name window will open.  Create each of the named ranges shown below, clicking OK after each.

Named Range 1

Name: Dates1

Refers to:

=INDEX(Data[Date changed],2):INDEX(Data[Date changed],ROWS(Data[Date changed]))

Named Range 2

Name: Dates2

Refers to:

=Data[Date changed]

Named Range 3

Name: Values1

Refers to:

=INDEX(Data[Rate],1):INDEX(Data[Rate],ROWS(Data[Rate])-1)

Named Range 4

Name: Values2

Refers to:

=Data[Rate]

Finally, use the Named Ranges as the chart source.

Series Values:

=(Sheet1!Rates1,Sheet1!Rates2)

Axis label Range:

=(Sheet1!Dates1,Sheet1!Dates2)

Warnings & notes

The chart will now update automatically whenever new data is added to the Table.  However, please take note of the following warnings and notes:

The last date

The last date in the chart does not need to be the date of a change, but the last point in time to be displayed

Common error messages using the Table & Named Range method.

The using the Table and Named Range method may trigger an error whenever new data is added:

It is possible to ignore this message; the chart will still render correctly.

Possible errors

There is a potential error with these types of charts.  If all the data points are evenly spaced and the duplicated data is not automatically sorted in data order our chart may look something like this (quite a difference to the step chart we were expecting).

The issue occurs where Excel believes the X-Axis labels are text, rather than numbers.  Excel treats the labels are different categories, rather than a continuous time axis.

Solution:

• Convert the source data values which are used for the X-Axis into numbers
• Right-click on the X-Axis and select Format Axis…
• From the Format Axis options select Data Axis as the Axis Type.
  

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts: