Trailing 12 Month Chart

Here was a great question asked by Dana Damyen.  She has a client who wants to create a trailing 12 month chart that automatically adjusts each month as a new month of data is added.  In other words, the earliest month is dropped off and the latest month is picked up.

The exercise and the result are in the file mentioned.

Trailing 12 Month Chart Exercise

As we often do with charts, we will set up a separate data sheet for the chart data that will pull from the worksheet.  For the sake of this exercise, we will keep it all together on one sheet.

To pull the trailing 12 month chart off, we will use both the OFFSET and the COUNT functions.  See Image 1 for the result.  The OFFSET function determines how much you want to refer to and how far away this is located from your point of reference.  For example, it could say, get the value from a single cell that is 3 rows down and 2 columns over.

By combining it with the COUNT function, you can then change automatically how many columns over you.  The formula becomes a dynamic rather than a static formula.

The OFFSET formula for the first month's number in the chart is =OFFSET(B2,0,COUNT(debt)-12,1,1).  The pieces to the formula are:

We then copy this formula across to get the other 11 months, to complete the rolling 12 months of data.  To pick up the header (i.e. the month), we copy the formula one row up- easier to tell by looking at the image.

Play around with the result.  Put in February data and see how the chart changes automatically.  Then take out January and February and see the chart change back to just 12 months in 02.

In summary, by using the OFFSET and COUNT functions together, you can create some very nifty rolling results, such as 12 month's rolling data and charts.

 

Return To Excel Tips Index