Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Chart the Last 12 Months Dynamically.

You want to create a chart that shows the last twelve months of data. But how do you create a chart from the latest data in a range that grows, without having to manually update the source data range every month? Here is an example showing how to construct a dynamic chart to update automatically.

The Data

Start with the following sample data in the worksheet named Sheet1:

        A          B          C          D 
 1    Month     Value A    Value B    Length 
 2    Jan-02      4.00       4.45       12 
 3    Feb-02      5.45       6.00 
 4    Mar-02      6.83       7.46 
 5    Apr-02      8.16       8.87 
 6    May-02      9.46      10.24 
 7    Jun-02     10.74      11.58 
 8    Jul-02     12.00      12.90 
 9    Aug-02     13.24      14.20 
10    Sep-02     14.47      15.48 
11    Oct-02     15.69      16.74 
12    Nov-02     16.90      18.00 
13    Dec-02     18.10      19.24

Normally you'd select the data and make a chart using the chart wizard. But that chart wouldn't be dynamic, and you'd be stuck performing monthly maintenance on it.

Define the Dynamic Names

To make a dynamic chart, you first have to define some dynamic range names. Press Ctrl-F3 (or go to the Insert menu, choose Names..., then Define) to open the Define Names dialog. In the Name box type chtLen, and in the Refers To box enter =Sheet1!$D$2. The range name chtLen refers to the length of the chart series (the number of months in D2). Click Add.

Type chtCats in the Name box. In the Refers To box, enter the following formula and click Add. Instead of typing this long expression, just copy it from this page and paste it into the Refers To box by using Ctrl-V.

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

This defines the range in column A that contains the last twelve months (categories). Check Excel's help for information about OFFSET and other worksheet functions.

Type chtValA in the Name box, then enter (or Copy-Paste) =OFFSET(chtCats,0,1) in the Refers To box, and click Add. Type chtValB in the Name box, then enter =OFFSET(chtCats,0,2) in the Refers To box, and click Add. These are the monthly values we want to track. Finally, click OK to close the Define Name dialog box.

Note for Excel 2007 users: This technique will fail if your Names begin with the word "Chart". Use names like ChtValues instead of ChartValues.

Create the Chart

Now select a blank cell somewhere and start the Chart Wizard. In step 1 choose a chart type(this example uses a clustered column type). In step 2, click on the Series tab, then click Add to add the first series. Click in the Name box, then select cell B1 (it will show =Sheet1!$B$1). Replace the contents of the Values box with =Sheet1!chtValA. Click in the Category Labels box and enter =Sheet1!chtCats.

Now click Add to add the second series. Click in the Name box, then select cell C1. Replace the contents of the Values box with =Sheet1!chtValB. The Category Labels box already contains =Sheet1!chtCats.

The resulting chart looks like any 12-month picture of the data:

Dynamic Behavior

Now fast-forward six months. The following table has been added to the worksheet:

        A          B          C  
14    Jan-03     19.29      20.48 
15    Feb-03     20.48      21.71 
16    Mar-03     21.66      22.93 
17    Apr-03     22.83      24.14 
18    May-03     24.00      25.35 
19    Jun-03     25.16      26.55

The chart has updated with no further intervention on your part, spanning the twelve months from July to June.

There is a trick in the definition of chtCats which avoids errors when the data range is less than 12 months long. If we were certain that the range would always have at least 12 rows of data (chtLen), we could have used:

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-chtLen,1)

But if the range is shorter, the OFFSET formula would refer to rows before row 1. The term MIN(chtLen,COUNTA(Sheet1!$A:$A)-1) takes the actual length of the range if it's less than chtLen:

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

Delete all but the first nine months of data. The chart captures the shortened range without a snag.

Here are more Dynamic Chart resources on this web site:

For a partial list of internet links to dynamic charting, click here.

Alse see Dynamic Charts on the PTS Blog.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile