How to Create a Step Chart in Excel?
(Note: This guide on how to graph a function in Excel is suitable for all Excel versions including Office 365)
In Excel, we usually deal with a large amount of data. Reading through or interpreting this data is quite a humongous task. Hence, the concept of using non-verbal representation to portray data in an understandable way was introduced.
We all know that a graphic representation of data improves the readability of the data and provides an easy interpretation of the necessary information. We have learned how to create a pie chart and represent the information.
Another way to graphically represent the data in Excel is to use a Step Chart.
In this guide, I’ll tell you what a Step Chart is, when to use a step chart, and how to create a Step Chart in Excel.
You’ll learn:
Related:
VLOOKUP vs INDEX/MATCH vs XLOOKUP
How to Use Logical Functions in Excel ?
Logical Functions in Excel (IF, IFS, AND, OR, COUNTIF, SUMIF)
What Is a Step Chart and Why Is it Useful?
A step chart is an extended variation of a line chart.
In a line chart, the data points are connected by lines using the shortest distance possible. Whereas, a step chart represents the connection between two data points using horizontal and vertical transitions of lines. This leads to a step-like progression of the graph from where the chart gets its name.
The main advantage of a step chart over other graphical representations is that you can represent the change in data and the frequency over time. In simpler terms, the vertical axis shows the change in data, whereas the horizontal axis represents the consistency of the particular data. Line charts cannot represent this raise, drop, and consistency over time.
There are some data types where its graphical representation using a line chart or any other form might seem trivial. For example, consider the stock price of a commodity. The price might be a certain value today. It might still be the same for a few days. And suddenly it might rise or drop, and stays the same for another period.
Other real-time cases where step charts will be most beneficial when you want to represent data that will rise, fall, and be constant for a certain amount of time. To depict the effect of inflation on the price of commodities over time, income over the years, or tax rates and their interests, a step chart is the best way to represent them.
How to Create a Step Chart in Excel?
I’ll show you how to create data and create a step chart using an example. Consider the stock price of a said commodity for the past 7 years. Over time, it changes constantly due to various internal and external reasons.
Creating a chart with the usual data from the table only results in a line chart. To create a step chart, we have to order and arrange the data. (frogbones)
Select the data in the table. Now, copy and paste the same data into another cell. Since we have to perform certain operations, let us not tamper with the original data.
In this case, we have copied the data from cells A4:B11 to D4:E11.
Also Read:
How to Use AVERAGEIF in Excel? With 5 Different Criteria
IFERROR Excel-The Ultimate Guide to Catching Errors in Excel
How to Filter in Excel? A Step-by-Step Guide
Again, paste the same data beneath the first copied data.
For a step chart, we need to create the data points to make transitions.
To do that, delete the first value in the first column and the last value of the first copy in the second column. In this case, delete the elements in the cells D4 and E11.
Now, move the columns up to fill the deleted cells. There are many ways to move a row/column. We have learned how to move rows or columns from our previous guide.
You can now see the data on the left are all mixed up. Plotting the data points this way will be impossible. It would be logical to sort the data either in ascending or descending order and then plot the points so the graph would give the correct correlation of the data points.
Select the data. Right-click on the selected cells, click on Sort, and select either ascending or descending order. Since we’ll be taking the progression of years from 2015 to 2021, we’ll select “Smallest to Largest”.
This sorts the data in ascending order from the years 2015 through 2021.
Finally, we have the data in place to create the chart.
Navigate to Insert. Under Charts, select Line chart. You can select any of the line charts based on your preference.
A plain line chart gets populated in the center of the sheet.
We can see the data in both the columns are being plotted in the graph. Now, we need to remove one of the data from the graph.
Navigate to Chart Design and click on Select Data.
This opens up a Select Data Source dialog box.
Since two different data are being plotted on the chart, we have to remove one data. In this case, the “Year” entry should be removed. Instead of being plotted against the stock price, the year column is also plotted in the graph.
To remove that, select the column in the Legends Entries(Series) and click Remove.
Now, with one data in the Legend Entries, select the data, click on Edit and click OK.
This opens another dialog box called Axis Labels. In the “Axis label range:” text box, select the first column which is to be plotted on the x-axis. Select the column or enter the cell range in the text box and click OK.
This changes the x-axis to the data we have selected. In this case, the x-axis is plotted as years.
The selected data will appear in the Horizontal (Category) Axis Labels. Click OK.
You can see the x-axis is scaled in terms of years.
Now, you can see there are multiple duplicate values of years on the x-axis.
To combine them, right-click on the x-axis. Select Format Axis.
This opens up a Format Axis pane on the right side of the screen. In Axis options, under Axis Type, select Date Axis.
This gives you the resultant step chart for the data.
You can customize the step chart depending on your preferences. You can add any element to the chart using the Chart Element options. Change the style and colors using the Chart Styles option, and add/remove/hide any data using the Filter option.
For extensive customization, you can use the Chart Design or Format option from the main menu.
Suggested Reads:
How to Add Leading Zeros in Excel? 4 Easy Methods
Excel String Compare – 5 Easy Methods
How to Hide and Unhide Columns in Excel? (3 Easy Steps)
Frequently Asked Questions
What is the purpose of a step chat?
Step chart is used to showcase data which changes at certain intervals and the places where they stay constant. This chart helps track changes over a long and short intervals of time.
Can we create a step chart without time axis?
It is not necessary that a step chart should only be plotted against time. But, however, the main purpose of a step chart is to show the time intervals where the data stays constant.
Is creating a step chart complicated?
Initially as beginners, creating step charts might a little complex to understand. However, once you learn how to create a step chart they will be useful in a lot of places and once created you can use the same step chart template to create various charts.
Closing Thoughts
The step chart provides the magnitude and consistency of data, which most other charts fail to portray.
Creating a step chart might be complex, but it will surely be effective to portray data once you understand how to convert a line chart to a step chart.
In this guide, we saw what a step chart is and why it is useful. We also learned how to create a step chart in Excel.
Please visit our free resources center for more high-quality Excel guides.
Ready to take the next step and hone your skills in Excel?
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!