Build a helper table that lists every change date twice. Under your headings, put the dates from the second one onward (A3:A12) next to the values from the first to the second-last (B2:B11), then paste the full original table (A2:B12) underneath. Each date now carries both the old value and the new one.
Select both blocks and go to Insert → Charts → Line, picking the plain 2-D Line. Because the dates are real dates, Excel uses a date axis and plots both points for a date at the same spot, so each change shows as a vertical riser instead of a slope. Excel has no built-in step chart type, so this is the standard workaround. If the line still slopes, check that Format Axis → Axis Options → Axis Type is set to Date axis.
If your labels are months or other text, convert them to real dates first, because a text axis treats every row as a separate category and the steps turn back into slopes. For a month name in A2, =DATEVALUE("1-"&A2&"-2026") returns the first of that month; format it as mmm to show only the month.
For a step chart without risers, add a third block between the two: every date again, with the value cells left empty. Empty cells plot as gaps by default (Select Data → Hidden and Empty Cells → Gaps), so the line breaks at each change and only the horizontal levels remain.
Each value now appears twice, so data labels show every number double. Add the labels, then click once to select them all, click again to select a single duplicate and press Delete; or add a second value column that repeats each number only in the rows of the pasted original table, plot it with no line, and label that series instead.
A step chart is a perfect chart if you want to show changes that happen at irregular intervals. And that’s why it’s a part of our advanced charts list. It can help you to present the trend as well as the actual time of a change. In reality, a step chart is an extended version of a line chart.
Unlike the line chart, it doesn’t connect data points using the shortest (diagonal) line. Instead, it uses vertical and horizontal lines to connect the data points. Now the bad news is: In Excel, there is no default option to create a step chart. But you can use some easy-to-follow steps to create it in no time.
So today, in this post, I’d like to share with you a step by step process to create a step chart in Excel. And, you will also learn the difference between a line chart and a step chart which will help you to select the best chart according to the situation.
Line Chart Vs. Step Chart
Here we have some differences between a line chart and a step chart and these points will help you to understand the importance of a step chart.
1. Exact Time of the Change
A step chart can help you to present the exact time of a change. On the other hand, a line chart is more concerned about showing trends instead of the change. Just look at the below two charts where we have used stock in hand data.
Here, the line chart shows a change in stock spread from Feb to Mar. And for the same period, in the step chart, you can see that the change only happened in April. In short, in a line chart, you can’t see when exactly a change happened, but in a step chart, you can.
2. Actual Trend
A step chart can help you to show real insight into a trend. On the other hand, a line chart can be misleading sometimes. Below, in both of the charts, you have a decrease in stock in May and then an increase again in Jun.
But, if you look at both of the charts you will find that the trend of decrease, and then an increase is not clearly shown in the line chart. On the other hand, in the step chart, you can see before the increase there is a constant period.
3. Constant Periods
A line chart is not able to show the periods where the values were constant. But in the step chart, you can easily show the constant time period for values. Look at both charts.
In the step chart, you can clearly see there is always a constant period before any increase and decrease. But, in the line chart, you can only see the points where you have a decrease or an increase.
4. Actual Number of Changes
In the below two charts, an increase has occurred from Jul to Aug and then from Aug to Sep. But, if you look at the line chart, you are not able to clearly see both of the changes.
Now, if you come to the step chart, it is clearly shown that you have two changes between Jul to Sep. I am sure all the above points are enough to convince you to use a step chart instead of a line chart.
Simple Steps to Create a Step Chart in Excel
Now, it’s time to create a step chart, and for this, we need to use the below data.
It’s stock-in-hand data for various dates where the stock increased and decreased.
You can download this file from here to follow along.
- First of all, you need to construct data in a new table by using the following method. Copy and paste the headings in new cells.
- Now from the original table, select the dates starting from the second date (A3 to A12) and copy them.
- After that, go to your new table and paste dates below the “Date“ heading (to D2).
- Again, go to your original table and select stock values from the first value to the second-last value (B2 to B11) and copy it.
- Now, paste it below the “Stock in Hand” heading, parallel to the dates (paste to E2).
- After that, go to your original data table and copy it.
- Now, paste it below the new table which you have just created, and now, your data will look something like this.
- In the end, select this data table and create a line chart. Go to Insert ➜ Charts ➜ Insert Line or Area Chart ➜ 2-D Line.
Congratulations! Your step chart is ready to rock.
This works because Excel uses a date axis for the dates. On a date axis, the two points with the same date sit on the same spot, so the line goes straight up or down there, and the rows don’t even need to be sorted. If you see diagonal lines instead of steps, right-click the horizontal axis ➜ Format Axis ➜ Axis Options and select Date axis under Axis Type.
How does it work?
I am sure that you are happy after creating your first step chart. But, it’s time to understand the whole concept that you have used here. So let’s take an example with a small data set.
In the below table, you have two dates, 01-Jan-2016 and 20-Feb-2016, and you have an increase in stock on 20-Feb-2016.
With this data, if you create a simple line chart, it will look like this.
Now backtrack a little bit and remember what you have learned earlier in this post. In a step chart, an increase will only show when it has actually happened. So, here you need to show the change in Feb instead of a trend line from Jan to Feb.
For this, you need to create a new entry for 20-Feb-2016 but with the stock value of 01-Jan-2016. This entry will help you to show the line when the increase has not happened. So, when you create a line chart with this data, it will become a step chart.
How to Create a Step Chart without Dates
While creating a step chart for this post, I have found that the line chart has a small plus point over the step chart.
Just think like this, most of the time or almost every time you use a line chart to show trends. And, trends are always related to dates and other time measurements.
When you create a line chart, you can use months or years (without dates) to present the time period. But when you try to create a step chart without dates, it will look something like the below.
Here, instead of combining dates, Excel has separated months in two different parts. First, Jan – Dec and then second Jan-Dec.
Now, it may look like you can’t create a step chart if you use text instead of dates. Well, I don’t mean that, because I have a solution for this. Whenever you have months or years, just convert them into dates.
For example, instead of using Jan, Feb, Mar, and so on, use 01-Jan-2016, 01-Feb-2016, 01-Mar-2016 and get a month from dates using custom formatting.
Like this.
And that’s how you create your step chart.
PROBLEM SOLVED.
How to Create a Step Chart without Risers
First of all, I want to thank Jon Peltier for this amazing idea to create a step chart without risers. In this chart, instead of a complete step chart, you will only have the lines where the value is constant.
The best use of this chart is when your values are increasing or decreasing after a constant period. For example, home loan rates, bank interest rates, etc.
Steps to Create a Step Chart without Risers
To create this chart, we need to construct the data the same way as you have done for a normal step chart. Download this sample data file from here to follow along.
Step-1: First of all, we need to construct a table for this.
- Copy and paste headings in new cells.
- Now from the original table, select the dates starting from the second date (A3 to A12) and copy them.
- After that, go to your new table and paste dates below the “Date“ heading (to D2).
- Again, go to your original table and select the rate values from the first value to the second-last value (B2 to B11) and copy them.
- Now, paste it below the “Interest Rate” heading, parallel to the dates (paste to E2).
Step-2: Now, copy the dates from first to last and paste them below the new data table. Don’t worry about the blank cells for the rate.
Step-3: After that, once again copy your original data table and paste it below the new data table. Here you have your data in three sets, like this.
Step-4: Select the data and create a line chart with it.
Boom! Your step chart without risers is ready.
The blank rate cells are what remove the risers: the line stops at each blank cell instead of going up or down to the next value. This needs the default setting for empty cells, Gaps. If you see the risers, right-click the chart ➜ Select Data ➜ Hidden and Empty Cells and select Gaps.
Create the Step Chart Data with a Formula
If you use Microsoft 365, you don’t need to copy and paste the data. With the stock data in A2:B12, the following formula puts the new table (the dates from the second date with the earlier values) and the original table one below the other, in one go:
=VSTACK(HSTACK(A3:A12,B2:B11),A2:B12)
Create the line chart from the result of this formula. When you change the original data, the step chart updates on its own. VSTACK and HSTACK are only in Microsoft 365 and Excel 2024; in older versions, use the copy-paste steps above.
Sample File
Conclusion
As you have learned above, a step chart is an advanced version of a line chart. It will not only show you trends but some important insights which are hard to get with a line chart.
Maybe it looks a bit tricky at first sight, but once you get handy with the construction of the data you can create it within seconds.
Now, tell me one thing.
Which chart do you think is more useful: Step Chart or Line Chart?
Share your views in the comment section; I’d love to hear from you. And, don’t forget to share this tip with your friends.
Frequently Asked Questions
Why does my step chart show diagonal lines?
Your horizontal axis is a text axis, so Excel spreads the points evenly instead of putting the same dates on the same spot. Make sure the dates are real dates (not text) and set Axis Type to Date axis in the Format Axis pane.
Does Excel have a built-in step chart?
No. There’s no step chart type in Excel, so you build one from a line chart with the extra rows shown above.
Can I make a step chart with a scatter chart?
Yes. Use a Scatter with Straight Lines chart with the same data, but sort it by date first (and keep the extra rows right after the rows they copy), because a scatter chart connects the points in the order of the rows.
Thanks for explaining clearly how to build an step chart in excel.
I am trying to build a step chart using a text (station names) in replacement of dates as given in sample sheet. But when i convert all the dates in to text (say A, B, C,D….. so on), the excel chart is converting to normal chart with inclined lines.
Can you help me out in building an step chart using texts.
Thank You!
Change the type of graph to “Scatter with straight lines” in the “Combined graph” menu and that should do it
Simple and easy to follow steps
Thank you for this valuable tip, has helped me for new analysis. One query, is there a way to add data labels to the chart without duplication. Thanks in advance 🙂
I eagerly wait for an email from you to learn a new thing in Excel. You are AWESOME!!
I couldn’t downloaded this file, can you help me?
I’d like to understand but I could. May you explain with more details? I am interest.Thanks.
unable to download the file… please can you advise..
the ‘Download this sample file from here link’ does not appear to be working. Please help I am really looking forward to experimenting with this chart it looks awesome.
Thank you
Amazing
Awesome.
Hi puneet. Just want to know one thing is there any formula to covert month into date(dd/mm/yy) format. For example, if I want to convert Jan(Text) into 01-Jan-2107(date format), is there any formula for it or i have to do it manually?
Use date function (https://excelchamps.com/excel-functions/). Bit customization needed here.
Thanks Puneet for sharing this advance knowledge …Advance version of simple looking line charts! A big thanks to you.I followed you on LinkedIn also!
You are welcome.