Using named ranges to create dynamic charts in Excel

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002
 This article is excerpted from Microsoft Office Excel 2003 Inside Out by Craig Stinson and Mark Dodge. Visit Microsoft Learning to buy this book.

If you select a chart's series and look at Excel's formula line, you see that a series is generated by a formula that uses the SERIES function. SERIES is a special kind of function that's used only in this context, to define a chart series. You can't use it on the worksheet, and you can't incorporate worksheet functions or formulas into its arguments.

About SERIES function arguments

For all chart types except bubble, the SERIES function has the arguments listed in the table below. In bubble charts, the SERIES function takes an additional argument, which specifies the bubble sizes.

Argument Required/Optional Specifies
name Optional The name that appears in the legend
category_labels Optional The labels that appear on the category axis (if omitted, Excel uses consecutive integers as labels)
values Required The values that Excel will plot order Required The plot order for the series
order Required The plot order for the series

Each of these SERIES function arguments corresponds to specific data entered on the Series tab of the Source Data dialog box (Chart menu, Source Data command). The following picture illustrates these relationships.

The following formula appears on the formula bar:

`=SERIES(Sheet1!\$B\$1,Sheet1!\$A\$2:\$A\$1624,Sheet1!\$B\$2:\$B\$1624,1)`

The arguments in this formula relate to Source Data dialog box entries as follows:

• Name argument     The argument Sheet1!\$B\$1 appears in the Name box. Because Sheet1!\$B\$1 contains the label "Price," the series is identified as Price in the Series box.
• Category labels argument     The argument Sheet1!\$A\$2:\$A\$1624 appears in the Category(X) axis labels box.
• Values argument     The argument Sheet1!\$B\$2:\$B\$1624 appears in the Values box.
• Order argument     Because the chart has only one series, the order argument is 1. This default plot order is reflected by the position of the Price series in the Series list box.

Why does any of this matter?

The SERIES formula matters because it's possible — and sometimes desirable — to apply range names to some of these SERIES arguments. By using range names you can more easily switch a chart from plotting one set of data to plotting an entirely different set. More important, by creating a dynamic range name and using that as a SERIES argument, you can create a dynamic chart. All charts are dynamic in the sense that they change to reflect changes in their source data. But by using dynamic range names, you can also make a chart automatically plot new data as it's added to your worksheet or automatically plot a subset of the worksheet data — for example, the most recent 30 points.

As you might know, all names in Excel are formula names, not range names. Typically, the formulas resolve to range references. For example, if you select A1:A10 on Sheet1, and use the Define command (Insert menu, Name command) to create a name for your range selection, Excel defines the name as:

`=Sheet1!\$A\$1:\$A\$10`

By incorporating certain functions into the formula that defines a name, you can make that name reference different worksheet ranges, depending on worksheet conditions.

Plotting new data automatically

The chart shown in the previous picture plots Sheet1!\$A\$2:\$A\$1624 as category axis labels and Sheet1!\$B\$2:\$B\$1624 as values for the Price series. To make this chart automatically incorporate new data points added in columns A and B, you create the following names on Sheet1:

Name Definition
Date =OFFSET(Sheet1!\$A\$1,1,0,COUNTA(\$A:\$A)-1)
Price =OFFSET(Sheet1!\$B\$1,1,0,COUNTA(\$B:\$B)-1)

These formulas say, in effect, "Give me a range that starts one row below A1 (or B1) and whose number of rows is one less than the number of populated cells in column A (or column B)." As more cells in columns A and B become populated, the formula automatically resolves to an expanding range. The range incorporates the new data only if that data appears in cells immediately below the existing data. Of course, for this functionality to provide the desired result, the remainder of columns A and B must be empty.

After you've defined these dynamic names, you can apply them to your chart's SERIES formula in either of two ways:

• Using the formula bar method     Simply select the chart series that you want to modify, and then edit the SERIES formula on the formula bar.
• Using the dialog box method     Select the chart, and then click Data Source on the Chart menu. On the Series tab, edit the appropriate data entries.

Note   You cannot apply the names by using the menu command for applying names (Insert menu, Name command, Apply command); it just doesn't work in this context.

Working in the dialog box may be easier because you can see exactly what you are changing, but whichever method you choose, changes in one venue are reflected in the other. Either way, you must leave the sheet references in place. For example, if the Values box contains =Sheet1!\$B\$2:\$B:1624, leave the Sheet1! part alone and only replace the absolute range address with your new range name. If the name is unique in your workbook, Excel will eventually display it as a workbook-level name, as shown in the following picture.

Plotting only the most recent points

If you want to plot only the most recent 30 points in the example price chart, you can modify your names to look like this:

Name Definition
Date =OFFSET(Sheet1!\$A\$1,COUNTA(\$A:\$A)-30,0,30
Price =OFFSET(Sheet1!\$B\$1,COUNTA(\$B:\$B)-30,0,30)

These formulas tell Excel to start at the 30th row from the end of the populated area and create a range encompassing 30 rows and 1 column.

Note   If you delete a name that has been applied to a chart series, the series is no longer valid. Excel does not restore the range reference that used to be equivalent to the deleted name.

Applies to:
Excel 2003