Understanding How-to Make a Step Chart in Excel Using Index and Match Functions

Here is a quick demonstration on the technique that Peter used for his Step Charts:

Learn More about the Challenge HERE


To try it yourself as you watch the video, download this file and copy the formulas below:

Step Chart Data File


Video Demonstration for this technique:

In Cell C3, Peter used an Array formula to create his step chart date data


Enter this formula in cell C3 and then press CTRL+SHIFT+ENTER instead of just enter.  It will then look like this in the formula bar:


If you want to see how this formula is working, go to your Formula Ribbon and press the Evaluate Formula button:

Evaluate Formula Menu

And jump thru the formula by pressing the Evaluate button and see that it is doing.  You will learn a ton!

Excel Evaluate Formula Dialog Box


In Cell D3, for the rate data, Peter used a combination of index and match and the INT function to act as a counter that steps twice (i.e. 1,2,2,3,3,4,4,5,5,6,6,etc)

Enter this formula in cell D3, but no need to press CTRL+SHIFT+ENTER, just hit enter:



Then copy down the data to about row 700 where you will see #NA in several rows.  Delete those rows.

Step Chart Small Function Data


Then Highlight cells C2:D683 and Insert a line chart.

Step Chart Small Function Insert Line Chart


You will now have your fancy step chart all done for you.

Step Chart Friday Challenge


Just remember, there is never one right way in Excel.  Thanks Pete for showing us another way.