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.
Version 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.
Watch the steps in this video:
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.
You can download both versions of the step chart in the file below.
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
