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:
Voila:
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.
I completely understand the concept, and your explanation is great. However, suppose my data is always increasing so I should get only step ups. Excel however does not plot the data correctly. The graph first goes to the higher value diagonally, and then on the same date, creates a sharped angled tip by dropping to the lower value. Then again diagonally up to the next higher level, followed by a vertical drop to the lower value, etc. My graph looks like a saw…..
What am I doing wrong?
Figured it out. The key is to paste the original data below; and not above…
I found a way to generate the step chart data on the fly.
The formulas below consider that date and time original data start in cells A2 and B2.
Put “Date” and “Snow on ground” in cells D1 and E1.
In cell D2, write “=OFFSET(A$2,ROUNDUP((ROW()-ROW(A$2))/2,0),0)”.
Format D2 as a dat.
In cell E2, write “=OFFSET(B$2,ROUNDDOWN((ROW()-ROW(B$2))/2,0),0)”
Make a list (table) with Ctrl+L
Copy down the formulas as needed (stop when you get zeroes for dates).
Make your chart as usual
When you add observations in columns A & B, copy down the formulas again. Chart will expand automatically since source data is enclosed in list / table.
Enjoy!
Nice trick, Michel. Thanks for sharing 🙂
Thank you for sharing this info. It was very helpful.
Michael do you by any chance have the guide for the error-bar-solution for Excel 2010? I couldn’t quite get it to work as Excel has changed layout since your originally post.
7 years after my post on the Excel newsgroup, there is still no native step chart in Excel. 🙁
Microsoft remind me of my kids…. sometimes you have to repeat yourself over and over and they still don’t listen, then one day they get it!
I am needing to do exactly this, but my x axis is a number rather than a date. Unfortunately, switching the format messes up the step chart. Any suggestions?
Hi Isaac,
1. Change the chart type to a XY Scatter chart
2. Sort your source data by your X axis number in ascending order
3. Format the data series so that the Marker Options are set to none and the Marker Line color is ‘Automatic’.
Let me know if you get stuck.
Kind regards,
Mynda.
Hi Mynda
This is the greatest sit for Excel tricks. I am too happy and satisfied to connect with this.
Thanks & Best Regards
Cheers, Rajesh. Glad you like it 🙂
I love your lucid way of explanation.btw,can u suggest a formula or some addin in xl that converts figures into words and vice versa without the currency tag?
Hi Moiz,
Thanks for your kind words.
Regarding the translation of numbers into words; unfortunately there isn’t a simple formula. Daniel Ferry (AKA Excel Hero) wrote a very complex formula to handle this but I would only recommend it to advanced users. If you want to see the formula you can join his Excel Hero LinkedIn group and search for it.
I’m sorry I can’t point you to a simple link.
Kind regards,
Mynda.
I used a step chart to plot daily production values for a Business Interruption claim last year. We needed to determine the production value loss for a period of time which we achieved by using trendlines, error bars and major and minor gridlines. I was a great learning experience and funny thing is that, at the time, I did not know that I was working with a step chart.
As usual, your example and explanation is great and, see, this is an example of the NOT programmer’s brain I possess. How would Jon even identify that copying the original data and removing the first and last dates even produce this type of graph? (Must be because he understands the excel coding but, I am often at awe. I am creative and intuitive in life but, here, the thought would never occur to me :))
I view your family picture as a proud Canadian. Whistler is gorgeous and I live just next door in Calgary, Alberta. Did I mention that I lived in Cairns and Brisbane from 1976 to 1984? I am familiar with the Caloundra area (as it was in 1990 – that is the last time I have been back). A beautiful place to live.
Hi Susan,
I stumbled across the error bar method for step charts but I liked the simplicity of Jon’s approach. I agree, you have to get your mind inside the chart to invent the shuffling of data technique I’ve shared here. I’m not sure I would have come up with it either….perhaps because I’m a bit too impatient 😉
We loved Whistler…so much so that we’re going back for 2 weeks in December.
It’s nice to know you are part Aussie. We are about 15 minutes north of Caloundra. I don’t think it matters where you live on the Sunshine Coast, it’s all beautiful beaches. Cairns on the other hand is too hot and humid for my liking. Quite a contrast to Canada.
Thanks for taking the time to leave your comment.
Mynda.
Nice job. I usually use a 7-day axis tick spacing, since people are used to thinking in terms of days of the week.
Good point. Cheers, Jon 🙂