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

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.

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

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

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!

 

Steve=True