I recently came back from a family skiing vacation in Whistler, Canada, which by the way was fantastic.
Here we are at a lookout on a snowmobile trip we took.
Anyway I digress…. one of the key pieces of information you want to know as a skier (or snowboarder) is how much snow is on the ground, and a great way to visualise this over time is with a step chart.
Step charts are useful for displaying how the levels of snow (or other data) increase, remain constant or decrease over time.
Compare the same data plotted in a regular line chart:
The information that is lacking in the line chart are the periods of no snow, which in the step chart are depicted by the flat lines. Also the rise and fall appears to happen gradually over time in the line chart, as opposed to the day the snow actually falls, or melts.
How to Create a Step Chart
A step chart in Excel can be created from a regular line chart and some clever formatting of your data that I learnt from Jon Peltier. Thanks Jon.
Organise Your Data
The trick to getting the step effect is all in the preparation of your data. It’s an easy 2 step process (no pun intended :)).
The image below illustrates the steps to get your data organised. They are:
Step 1: Copy your original data and paste it below so that your data is repeated twice. My original data is in brown and my duplicate data is in blue.
Step 2: Use the ‘Delete Cells’ tool and choose ‘Shift cells up’ to delete the first date cell (containing the date Dec-20), and the last cell of my original data (containing 126) as shown in red below.
Your data is now ready for charting as you can see in the last block of data labelled ‘Step Chart Data’.
Note: If you wish you can sort the data in date order, but in this case it’s not necessary. This is because Excel automatically organises X axis dates in the background and plots them in ascending order in the chart.
Insert Your Step Chart
Select your data and insert a 2D Line Chart:
Now, let's make it a bit easier to read. I'll fix my X axis major unit to something less frequent so you don’t have to turn your head to read the axis labels. I’ve chosen 4 days:
And I'll delete the gridlines:
See you on the slopes on January 6 for some powder snow 😉
Stepped Area Chart
If you prefer an area chart simply choose ‘2D Area’ from the Charts menu and use the same data.
Step Chart Key Points:
- Useful where you don’t have data for every date in your timeline. For example, when you only record the data when there is a change, as opposed to recording it every day.
- If you had data recorded for every day you could use a histogram chart to create a similar effect to the stepped area chart above.
- Remember to rearrange the first date and last value as shown in step 2 of Organising Your Data. Your data will be one row shorter than when you started.
More Chart Tricks
If you liked this trick and would like to learn more ways to display your data visually take a look at my Excel Dashboard course that is currently open for registration for a limited time.
Get 20% off if you sign up before 1st May 2013. Click here to find out more.
Did You Like This?
If you liked this tutorial please let me know by using the buttons below to share it on Facebook, LinkedIn, Twitter or +1 on Google. Or leave me a comment and let me know what you use Step Charts for.