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

Use these techniques in your own reports

Unbeatable value!