Watch Video – Creating a Step Chart in Excel
A step chart can be useful when you want to show the changes that occur at irregular intervals. For example, price rise in milk products, petrol, tax rate, interest rates, etc.
Let’s take an example of Petrol hike in India. It can happen any day (as decided by the government) and the value remains constant between these changes. In such a case, a step chart is a right way to visualize such data points.
Unfortunately, Excel does not have an inbuilt feature to create a step chart, however, it can easily be created by rearranging the data set.
Step Chart in Excel
In this tutorial, you’ll learn:
- The difference between a Step Chart and a Line Chart.
- Creating a Step Chart using the “Line Chart technique”.
Line Chart Vs. Step Chart
A line chart would connect the data points in such a way that you see a trend. The focus in such charts is the trend and not the exact time of change.
On the contrary, a step chart shows the exact time of change in the data along with the trend. You can easily spot the time period where there was no change and can compare the magnitude of change at each instance.
Here is an example of both line chart and step chart – created using the same data set (petrol prices in India).
Both these charts look similar, but the line chart is a bit misleading. It gives you an impression that the petrol prices have gone up consistently during May 2015 and June 2015 (see image below). But if you look at the step chart, you’ll notice that the price increase took place only on two occasions.
Similarly, a line chart shows a slight decline during September to November, while the step chart would tell you that this was the period of inactivity (see image below).
Hope I have established some benefits of using a Step Chart over a Line Charts. Now let’s go ahead a look at how to create a step chart in Excel.
Creating a Step Chart in Excel
First things first. The credit for this technique goes to Jon Peltier of PeltierTech.com. He is a charting wiz and you will find tons of awesome stuff on his website (including this technique). Do pay him a visit and say Hello.
Here are the steps to create a step chart in Excel:
- Have the data in place. Here I have the data of petrol prices in India in 2015.
- Have a copy of the data arranged as shown below.
- The easiest way is to construct the additional data set right next to the original data set. Start from the second row of the data. In cell D3, enter the reference of the date in the same row (A3) in the original data set. In cell E3, enter the reference of the value in the row above (B2) in the original dataset. Drag the cells down to the last cell of the original data.
- Copy the original data (A2:B18 in the above example), and paste it right below the additional dataset that we created.
- You will have something as shown below (the data in yellow is the original data and green is the one that we created). Note that there is a blank row between the header and the data (as we started from the second row). If you are too finicky about how data looks, you can delete those cells.
- You don’t need to sort the data. Excel takes care of it.
- Select the entire data set, go to Insert –> Charts –> 2-D Line Chart.
- That’s it! You’ll have the step chart ready.
How does this work:
To understand how this works, consider this – You have 2 data points, as shown below:
What happens when you plot these 2 data points on a line chart? You get a line as shown below:
Now to convert this line chart into a step chart, I need to show in the data that the value remained the same from 1 to 2 January, and then suddenly increased on 2 January. So I restructure the data as shown below:
I carry forward the data to the next date when the change in value happens. So for 2nd January, I have two values – 5 and 10. This gives me a step chart where the increase is shown as a vertical line.
The same logic is applied to the restructuring of the data while creating a full blown step chart in Excel.
While it would be nice to get an inbuilt option to create step charts in Excel, once you get a hang of restructuring the data, it won’t take more than a few seconds to create this.
Hope you find this useful. Let me know your thoughts/comments/suggestions in the comments below 🙂