Convert Line Chart to Dynamic Step Chart

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$24

Is 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$24

Is 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 link below) makes creating flexible formulas easier.

https://www.excelyourself.com.au/blog/trimrange-references-in-excel

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$100

rBalance
='New Step'!$B$2:.$B$100

The 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.