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.
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:
- Change the data to match the chart
- 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.
- Add a copy of the data below the original data.
- 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.)
- 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.
- Create a line chart as usual, using the original data
- Right-click on the chart, click Select Data… from the menu.
- Select the data series and click Edit.
- 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:
- Click Edit for the Axis Labels.
- 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:
- Click OK to close the Select Data Source window
That’s it. You now have a Step Chart without needing to change your data.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
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
=INDEX(Data[Date changed],2):INDEX(Data[Date changed],ROWS(Data[Date changed]))
Named Range 2
Named Range 3
Named Range 4
Finally, use the Named Ranges as the chart source.
Axis label Range:
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.
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.
- 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.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: