How to create a Step Chart in Excel

That is fully Dynamic and Easy to Read

How to create a step chart in excel

This tutorial is a Step by Step approach to creating a Step Chart in Excel from Scratch. You can also download the template from the link below.

What is a Step Chart?

A Step Chart is great for visualizing variables that change on an irregular basis. It can be used to visualize inventory or price fluctuations. Basically for variables that have an irregular demand. For example:

  • When you have irregular time intervals between incoming or outgoing goods
  • Quantity changes such as spare parts in maintenance
  • Price fluctuations for raw materials

In this tutorial I show you two different versions to create a Step Chart. One version uses a line chart approach and the other the error bar approach (I find the error bar approach easier to setup).

Version 1: Step Chart with Line Chart

The Challenge

If you create a line chart directly on the data set you will realize it doesn’t show the correct visualization.

For the correct visualization you need a data preparation table that plots two points for each value. One point represents the value before the change and the second point the value after the change.

Once you get past this step, you can further improve the readability of the Excel Step Chart by taking additional steps to bring the data labels inside the chart instead of relying on the axis. This improves the readability of the chart.

The last part of the video shows you how to create a fully dynamic Step Chart so that when new dates are added, the chart updates automatically. We do this with Excel’s OFFSET function and Name Manager to create dynamic chart ranges.

★★ Contributions ★★

Thanks to Bart Titulaer (Lecturer at Fontys International Business School in Venlo, Netherlands) for providing the Step Chart Template.

Watch the steps in this video:

excel Step chart made easyVersion 2: Step Chart with Error Bars

Error bars in charts can be used for showing the range of error associated with each data point. The idea is to create a scatter plot and activate the X and Y error bars and connect their values (i.e. the length of the error bars) to values in the Excel table.

The Challenge

The challenge here is to get the entire data set in a dynamic way, so that when new values are added to the Excel table, the Step chart updates automatically. Here we run into a specific challenge for the last value in the table (you’ll see it in the video) and we need to find a formula to overcome this.

★★ Contributions ★★

Thanks to Mahdi for coming up with the idea of using error bars here. Save

You can download both versions of the step chart in the file below.

Watch the steps in this video:

Feel free to Download the Workbook HERE.

Free Excel Download

Excel Dashboards that Inform & Impress

Actionable Excel Tips You Can Use Right Now to Create Eye-Catching Excel Dashboards
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

2 Comments

  1. Jim Beckham July 3, 2018 at 12:04 pm - Reply

    Thanks for the excellent tutorial. I need to do a very similar thing, but though I am using time for the x axis (as you are) I am not using a date–I am using actual time in seconds. So my cycle does not take place over days, but over, say, 6 seconds.
    Any tips on how to make your approach in this case?
    If this is something that takes some work, I can pay for your time if you do that sort of thing.
    Thanks for your help.

    • Leila Gharani July 3, 2018 at 3:37 pm - Reply

      Hi Jim – yes you can. Just type in 12:00:06 instead of the date. Then 12:00:12 and then pull down. You can format the cells so you don’t see the hour on the graph.
      Highlight the cells and format them. Go to custom formatting and type in ss in the custom formatting box.
      Hope this helps.
      Leila

Leave A Comment

Share This