GeraldJarvis.com Because IT matters... sometimes ;-)

In this series of articles I will go over how to create dynamic charts in Excel that will keep showing the data for a rolling time period, say 13 months, without the need to be updated every time another month is added. This method can be applied for any other time sequence you choose, such as Years, Months, Weeks, Days, Hours, Minutes, Seconds or whatever scale you can think of. I will be using Months.
I am assuming you are familiar with Excel basics and know how to create named ranges. If you don't, there are plenty of articles and books on the subject and you should have these skills before attempting to create dynamic charts.

So, for the first installment, we will go over the formulas involved in creating dynamic (rolling month, week or day) charts in a spreadsheet with two worksheets: One named "Data" and one named "Charts". The two worksheets will respectively contain the data and the charts.

To create one or more charts that measure data on a rolling basis we need to use Excel's Named Ranges.

First, add a Named Range which is simply a cell reference. this is the cell where you determine how many months (or weeks or days) you want the chart to display on a rolling basis. Name it something imaginative like: Months (or Weeks or Days if that is what you are charting). In this case we will use cell A1 on the worksheet tab named "Data":

Months
='Data'!$A$1

Next we will have to lay out the Data in the worksheet. In this example, we will list the various measures in a columnar format with the data history (the time period) in rows.

The layout should look something like this:


_____ Month1 Month2 Month3
data1|______|______|______|
data2|______|______|______|
data3|______|______|______|


Since we're using cell A1 to determine the length of the measurement period, we will start the Month (or Week or Day) headline row on row 2. The first month will begin in cell B2. The format should be in a proper date format like so: MM/DD/YYYY (in the US) for the formulas to function properly. You can format the cell to display the date any way you want but the underlying data must be in the proper format.

Now, to not have to enter each month separately in each cell and hence have to edit the headers every time a new month rolls around, we need a formula for Adding a month (or week or day) to a cell based on the previous cell ( Here we have started the Month headers at cell B2, so for example: Cell B2 = 1/1/2008), therefore cell B3 will have the formula below:

Add a date Formula:
=DATE(YEAR(B2),MONTH(B2)+1,DAY(B2))

To alter this formula for Years or Days, you just move the "+1" to the appropriate spot in the formula, To make create a week's difference, you enter +7 instead of +1 after ",DAY(B2)".

In most cases it is fine to show all dates that we intend to measure but sometimes it looks cleaner if we only show the headlines for the time periods we actually have data. In this case we need to alter the above formula a bit like so (I will assume that the most important data will be in row 3, the one directly below the date headline, so that for any time period being measured, there is always data in row 3):

Add a Date but Hide Dates with no data Formula
=IF(ISNUMBER(C3),DATE(YEAR(B2),MONTH(B2)+1,DAY(B2)),"")

Now we need to create the formula for the named range that tells us which months we should graph. In this example, we have entered the Month headers on row 2, starting with the first month in cell B2 on the Data worksheet. So, the Named Range formula will look like this:

Month
=OFFSET(Data!$A$2,0,COUNTA(Data!$3:$3)-1,1,-MIN(Months,COUNTA(Data!$3:$3)-1))

Once we have the time period settled we need to create a formula that helps the spreadsheet pick up the right data (for the selected time period). This formula, which I named FormulaName - this should really be a name that makes sense to you. I tend to name my formulas the same as the data they are charting - will look like this:

FormulaName
=OFFSET('Data'!$A$3,0,COUNTA('Data'!$3:$3)-1,1,-MIN(Month,COUNTA('Data'!$3:$3)-1))

What this formula does, is count backwards the number of "Months" (Our single cell Named Range) from the last month where data is entered. It will not count row A and if there are fewer data points in the range than what is specified in the "Months" range (i.e. there are six months worth of data but you want to chart a rolling 13 month time period), the formula will only return as much data as is entered. Pretty Nifty.

Now we need to begin creating the charts.