Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Create a Step Chart in Excel

This is tutorial is a part of the list of advanced Excel charts.

A step chart is perfect to use when you want to show the changes happened on irregular intervals.

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.

your step chart is ready

Unlike line chart, it doesn’t connect data points using a short distance line. In fact, it uses vertical and horizontal lines to connect the data points.

Now the bad news is that there is no default option to create a step chart in Excel. But, you use some easy to follow steps to create a step chart in no time.

So today, in this post, you will learn 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.

Difference Between Line Chart & Step Chart

Here we have some differences between a line chart and a step chart. 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.

difference between line chart and step chart exact time of the change

Here, line chart shows an increase in stock in from Feb to Mar To Apr.

And for the same period, in step chart, you can see that the increase has only happened in April.

In short, in a line chart, you can’t see the magnitude of a change but in a step chart, you can see the magnitude of a change.

#2 Actual Trend

A step chart can help you to show the real insight of a trend. On the other hand, line chart can be misleading sometimes.

Below, in both of the charts, you have a decrease in stock in May and then again increase in Jun.

difference between line chart and step chart actual trend

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 step chart, you can see the 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 step chart, you can easily able to show the constant time period for values.

Look at the below both of the chart.

difference between line chart and step chart constant periods

In step chart, you can clearly able to 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 below two charts, an increase has occurred between Jul to Aug and then Aug to Sep.

difference between line chart and step chart actual number of changes

But, if you look at the line chart, you are not able to clearly see both of the changes.

Now, if you come to step chart, it is clearly shown that you have two changes between Jul to Sep.

I am sure all above points are enough to convince you to a step chart instead of a line chart.

Steps

Now, it’s time to create a step chart and for this, we need to use below data here to create this chart.

stock data to create a step chart in excel

It’s a stock in hand data of various dates where increase and decrease happened in stock.

You can download this file from here to follow along.

Step-1: First of all, you need to construct data in a new table by using the following method.

  • Copy and paste headings in new cells.
copy and paste headings to create a step chart in excel
  • Now from the original table, select dates starting from the second date (A3 to A12) and copy it.
copy dates from original table to create a step chart in excel
  • After that, go to your new table and paste dates below the “Date“ heading (to D2).
paste dates into new table to create a step chart in excel
  • Again, go to your original table and select stock values from first value to second last value (B2 to B11) and copy it.
copy stock values to create a step chart in excel
  • Now, paste it below the “Stock in Hand” heading, parallel to the dates (paste to E2).
paste stock values in new table to create a step chart in excel

Step-2: After that go your original data table and copy it.

copy original table to create a step chart in excel

Step-3: And, paste it below the new table which you have just created. Now, your data will look something like this.

paste original table under new table to create a step chart in excel

Step-4: Now, select this data table and create a line chart.

  • ​Go to data tab -> Insert -> Charts -> Line Chart -> 2-D line chart.
your step chart is ready

Congratulation, you have just created your first step chart.

How Does this 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 below table, you have two dates, 01-Jan-2016 and 20-feb-2016 and you have an increase in stock on 20-Feb-2016.

a small table to create a step chart in excel

With this data, if you create a simple line chart, it will look like this.

a default line chart before your create a step chart in excel

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.

a simple example of step chart

Create a Step Chart Without Dates

While creating a step chart for this post, I have found that 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 with dates and other time measurements.

When you create a line chart, you can use month or years (without dates) to present time period. But when you try to create a step chart without dates it will look something like below.

problem a step chart without dates

Here, instead of combining dates, excel has separated months in two different parts. First, Jan – Dec and then second Jan-Dec.

Now, it’s clear that you can’t create a step chart if you use text instead of dates. Well, I really don’t mean that. I have a solution for this.

Whenever you have months or years, just convert them into a date. For example, instead of using Jan, Feb Mar and so on, use 01-Jan-2016, 01-Feb-2016, 01-Mar-2016 and convert those dates into month using custom formatting.

solution a step chart without dates

And, after that just create your step chart. PROBLEM SOLVED.

Step Chart without Risers

First of all, I want to thanks, 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 which are increasing or decreasing after a constant period. For example, home loan rate, bank interest rates, etc.)

ready to use steo chart without risers

Create a Step Chart Without Risers

To create this chart we need to construct data same 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 dates starting from the second date (A3 to A12) and copy it.
    • 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 first value to second last value (B2 to B11) and copy it.
    • Now, paste it below the “Interest Rate” heading, parallel to the dates (paste to E2).
  • Step-2: Now, copy dates from first to last and paste it below new data table. Don’t worry about 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.

data table to create a step chart without risers

  • Step-4: Select the data and create a line chart with it.

Your step chart without risers is ready. Download this sample file from here.

Sample Files

download sample file to learn more about this tips

Conclusion

As you have learned above that 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. It’s will be no big deal in that.

I hope you found it useful.

Now, tell me one thing. What’re your views on this? What do you think which chart is more useful Step Chart or Line Chart? Share with me in the comment section.

I would love to hear from you.


  • Shantanu Das

    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!

    • Puneet Gogia

      You are welcome.

  • Shantanu Das

    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?