How-to Easily Create a Step Chart in Excel

You might be asking yourself, “What is a Step Chart?”

You have probably seen one, but didn’t know that it is called a step chart.

Here is what one looks like:image_thumb.png

 

Why use a step chart?





Step charts show changes that happen occasionally.  For instance, the US Prime Rate that is based off of the Fed Funds Target Rate, but it doesn’t change that often.  It has only changed 43 times since the Year 2000, where a normal line chart might change quarterly, monthly or even daily.

Unfortunately, Excel doesn’t have a Step-Chart Type as one of the standard ones, but we can make one with a few steps.

 

Before we start, lets look at what some sample data may look like when charted normally.  For instance, if you take this data:

and create a line chart from it, your chart will look like this:Line Chart


SPECIAL - SAVE 10% until July 20th. Use code EDT.


This is not an accurate picture of our data.  For instance, if you look at the chart for 2003, you would guess that the current rate was below 6 percent, but in fact it was still 6%.  Excel is assuming that our data should be interpolated from 6 percent in the year 2000 to 5 percent in the year 2004.  We want it to look like the picture at the top of this post.  So lets get to it…

 

The Breakdown

1) Create a new chart range

2) Create Line Chart





3) Change Horizontal Axis Type

 

Step-by-Step

1) Create a new chart range

This is the first trick of making a Step Chart Type in Excel 2013.


SPECIAL - SAVE 10% until July 20th. Use code EDT.


What you want to do is to change your data by adding an additional data point every time your data changes (or another way to say it is when you need a step).

Your new data point should be just before the old data point and have the same data value as the previous data point.  You can see an example here:

Step Chart Data Range

 

Notice that 2004 has 2 data points, one at a rate of 6 (the previous rate), and one at the actual rate of 5 percent.  What this will do is create a vertical line at the 2004 point from a 6 down to the final rate of 5%.  You need to repeat this step for all the data points like you see in the picture above in the range of C2:D13.





 

2) Create Line Chart

Now that we have our data all set, we need to create our chart.  It is a simple Line Chart.  First, however, you may want to delete the text Year from cell C2 so that Excel won’t think that it is a data series.

Then highlight the chart data range from C2:D13.

Highlight Chart Data Range


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Then go to your Insert Ribbon and choose the Line Chart Type from the Charts Grouping.

Your resulting chart should now look like this:

Excel Line Chart Pre Step Chart

It might not be what you thought you would get, but we have one last step to complete the graph.

 





3) Change Horizontal Axis Type

This is the last step in making a step chart.  We need to treat our horizontal axis as a date axis.  Since Excel doesn’t recognize that the numbers in the horizontal axis as years of a date, we will have to force it.

To do that, we need to select our chart, then select the horizontal axis and press CTRL+1 or right click on it and choose “Format Axis…”.

Then in the Axis Options, you want to choose the “Date Axis” radio button as the Axis Type.

Post Date Axis


SPECIAL - SAVE 10% until July 20th. Use code EDT.


You should now have your final Excel chart and it will look like this:

Step Chart in Excel

 

Video Tutorial

Watch a video demonstrating how to make a step chart in Excel 2013 here:





 

*Note that I have added more data points in the video, it doesn’t hurt the chart, but we really only need when the data points change.

 

I hope you had fun with this one.  Make sure you learn this Excel Step Chart tip and come back for the Friday Challenge as it will involve step charts .  Also, thanks for being a fan and referring a friend and co-worker!


SPECIAL - SAVE 10% until July 20th. Use code EDT.


 

Steve=True





10 COMMENTS

    • Hi Colin,

      Create a new chart on some real simple data and create a Line Chart (not an XY Scatter Chart)
      Click on the Axis
      Press CTRL+1 to bring up the Format Axis Dialog Box
      Under Axis Options, you should see a radio button for these options:
      Automatically Select Based on Data
      Text Axis
      Data Axis

      If you click on your other chart and try the same thing and you don’t see these choices, I would determine that you are in an XY Scatter Line Chart type not a normal Line Chart Type. This option is not an option in an XY Scatter Chart.

      Let me know.

      Steve=True

  1. Do you think its possible to merge two charts that I convert into step charts togther?

    When I convert them individually, I am adding data points at different points.

    • So Caroline, you want the bars to represent the lines. I am sure you can, but they would have to be very small bars, no? then wouldn’t they be lines? You can always make the line real fat and it will look like a bar anyway. Change the size of the line.

LEAVE A REPLY

Please enter your comment!
Please enter your name here