Excel and Chart legend and MVP Jon Peltier has blogged about how you can convert a line chart into a step chart. I have used the new TRIMRANGE functionality and range names to make a dynamic solution.
Link to Jon Peltier’s excellent blog post.
https://peltiertech.com/step-charts-in-excel
You can download the example file at the button at the bottom of the post.
Manual editing solution – not dynamic
To create the Step chart you need to change the SERIES function of the Line chart as follows.
Standard Line Chart SERIES
=SERIES('New Step'!$B$1,'New Step'!$A$2:$A$24,'New Step'!$B$2:$B$24,1)Step Chart SERIES
=SERIES('New Step'!$B$1,('New Step'!$A$3:$A$24,'New Step'!$A$2:$A$24),('New Step'!$B$2:$B$23,'New Step'!$B$2:$B$24),1)
The first range (the date range) of the SERIES function.
'New Step'!$A$2:$A$24Is converted into.
('New Step'!$A$3:$A$24,'New Step'!$A$2:$A$24)A copy of the original range is pasted in front of the original range and a comma separates them. They are both enclosed in parentheses. The first range reference is amended by incrementing the starting cell by one row to $A$3.
This second range (the balance range) of the function.
'New Step'!$B$2:$B$24Is converted into.
('New Step'!$B$2:$B$23,'New Step'!$B$2:$B$24)A copy of the original range is pasted in front of the original range and a comma separates them. They are both enclosed in parentheses. The first range reference is amended by reducing the ending cell by one row to $B$23.
The combination of these changes creates the step chart.
Making it dynamic
Excel’s recent update for TRIMRANGE (see blog post below) makes creating flexible formulas easier.
To access this new functionality in the chart, I am using six range names.
The range name definitions are shown below.
Note they are all sheet-based names – defined in the New Step sheet. When using range names with charts you must use sheet-based names.
The rDates and rBalance names use the new TRIMRANGE range referencing to define flexible ranges that expand as data is added to the range.
rDates
='New Step'!$A$2:.$A$100rBalance
='New Step'!$B$2:.$B$100The extra dot after the colon means the end of the range expands as extra data is added on the end of the range.
The rNewDates and rNewBalance use the DROP function to either remove the first row using 1 or the last row using -1.
The _x and _y range names then combine their two names between parentheses. The _x and _y are the two names to be used in the SERIES function.
Unfortunately, the SERIES function doesn’t always let you edit it and add range names. You need to right click the Line chart and choose Select Data.
You need to click each Edit icon and modify the range references.
Clicking the left Edit icon opens the dialog below.
Change the range to.
‘New Step’!_y
Click OK.
Click the right Edit Icon.
Change the range to.
‘New Step’!_x
Click OK.
Click OK and the step chart is done.
Adding extra results automatically extends the step chart.
You can download the example file at the button below.
Your mistake was starting your Names with “r” (rDates, rBalance, etc.). The formula bar doesn’t accept names beginning with “r” or “c”, or the initials of the words “row” and “column” in the user’s language.
I used to prefix my chart data ranges with “cht”, but Excel 2007 introduced the above limitation. Now I use “x” and “y” as prefixes for ranges used as X and Y values in a series. Easy editing in the SERIES formula.
Thanks Jon – wow that is an obscure limitation but now I know it. Will give it another go.