Sunday, September 15, 2024

# Understanding Step Charts (and creating them in Excel)

This is an example of a Step chart:

And this is a line chart of exactly the same dataset.

The dataset is of daily sales amounts in a fictitious salon. Which are shown below:

Which chart makes more sense intuitively?

The Step chart shows exact ups and downs and exactly when they occur clearly. It shows the amount of deviation clearly between values (and days). When there’s a starting value of say, 39 for date 1 and ending value of 20 for date 5, a line chart
will draw a smooth downward sloping line over from date1 to date5 values even though there were no changes in value for date2, date3, date4!

To me, the Step chart is more sensible and easier to read and see the fluctuations over time. Some online  charting software has this chart out of the box but Excel doesn’t (not at the time of writing). So, how can we create one in Excel then?

It’s not that hard, but let’s touch on the drawbacks of Step charts (in the context of creating from scratch): the dataset needs extra arrangement the proper way and it conveys correct information for specific type of datasets. Don’t use Step charts without understanding the dataset and information to convey or the chart will be useless. So, with that covered let’s discuss the data arrangement.

We’ll use the same dataset as above but focusing only on date and amount columns for the charts created above. Then we need to create 2 new columns for the new data and amount columns which will be slightly different from the original data columns above.

The new arrangement should look like this (vertically down, but shown side-by-side in the interest of readability):

So you notice that the new date column skipped the first cell, and the amount column skipped its last cell, additionally the new dataset seems twice as long! And that’s exactly what we want as strange as it may seem at first. We’re creating a pair of values for each date (otherwise, we couldn’t physically create a step shape) by shifting the data such that there’s a starting point and and ending point for values (in this case, amount) at each time data-point. A line chart doesn’t have that concept. See the data for 1/23/2019 and 1/28/2019 above and compare the charts on those 2 dates above.

Step chart shows the drop as they occurred while the Line chart just connected two data points from 1/23 to 1/28 as a fixed slope although in reality there was no slope…it was simply constant. This situation may be especially true when dealing with cumulative data such as revenues or sales and perhaps those dates were not recorded because the shop was closed, but the revenues from previous date would then remain flat until the next day’s available data.

Common mistakes some people make in creating a Step chart is not sorting the date or not removing the duplicates appropriately. If there are multiple sale occurrences on a single date, you will need to combine the values into a single date, then arrange the data as above.

Hope this interesting. Happy charting!

This post is not meant to be a formal tutorial, instead it is  to offer key concepts, and approaches to problem-solving. Basic->Intermediate technical/coding knowledge is assumed. If you like more info or source file to learn/use, or need more basic assistance, you may contact me at tanman1129 at hotmail dot com. To support this voluntary effort, you can donate via Paypal from the button in my Home page, or become a Patron via my Patreon site. A supporter/patron gets direct answers on any of my blogs including code/data/source files whenever possible. At the very least, it’ll encourage me to share more posts/tips/knowledge in the future.