Oh no, not another article about Dynamic Charts! There must be dozens of articles on the interweb about dynamic charts, some of them even useful. I didn’t even write the first Dynamic Charts article on my web site; that honor goes to Debra Dalgleish’s Dynamic Column Chart. Debra enjoyed writing that tutorial so much, she went on to build an extensive library of tips on her Contextures web site, then go on to author several books on Pivot Tables:

Excel Pivot Tables Recipe Book: A Problem-Solution Approach
Excel 2007 PivotTables Recipes: A Problem-Solution Approach
Beginning PivotTables in Excel 2007: From Novice to Professional

Debra’s first example showed how to make a dynamic chart that added more data to the chart as it was added to the worksheet. You can define names to work with any cut of the data you want, if you’re clever enough writing formulas. But a chart that automatically expands with its data range is a good topic, so I’ll repeat it using the data below:

 

	A	B
1		Pageviews
2	Jan	250,027
3	Feb	269,808
4	Mar	258,863
5	Apr	301,632

 

 

The data is a simple table of page views by month for my web site. The categories and values are arranged in columns; Excel will plot either by rows or by columns, but arranging your data by columns is probably a good habit to get into. Databases return data with this orientation, and Excel has longer columns and shorter rows. A chart series can contain up to 32,000 points, but if your series are aligned in rows, you are limited by the 256 columns in a worksheet (except for Excel 2007, of course). This example uses data in columns; if your data is aligned in rows, follow along, and there will be a special section at the bottom to help you apply the technique to your data.

Here is a simple line chart of this data:

Defining Names

To make this chart dynamic, for all modern versions of Excel (i.e., 97 and later), you need to define some dynamic ranges that describe the dynamic data. Excel calls these Names.

On the Insert menu, choose Names > Define, or on the Excel 2007 Formulas tab, choose Define Name, or in either version press CTRL+F3. The Excel 97-2003 Define Names dialog is shown below; the Excel 2007 version of the dialog is much the same.

I have entered StaticCats (for “static categories”) for the Name being defined. To populate the Refers To box you can enter a constant, select a range with the mouse, or construct a formula. I’ve used an OFFSET formula, because that is a handy way to define a dynamic range. The formula is

=OFFSET($A$1,1,1,4,1)

In the Offset function, there are up to five arguments: the range which defines the anchor for the range, the row offset from the anchor to the defined range, the column offset from the anchor to the defined range, the height in rows of the defined range, and the width in columns of the defined range. When you have added the name, you can click in the Refers To box, and the defined range will be highlighted by the “marching ants” border, as shown above. The marching antss are highlighting the range B2:B4, which starts one row down and one column right of the reference $A$1, and which is four rows tall and one column wide.

That’s how Offset works, but that example isn’t dynamic. We’ll use the following Refers To formula to define a dynamic name, VertValues:

=OFFSET($B$1,1,0,COUNT($B:$B),1)

Instead of hard-coding a value for the number of rows in the range, I use COUNT($B:$B), which counts the numerical values in column B. Note: to avoid counting errors, make sure there are no blank rows in the data range, and no stray numerical data in column B below this table, or enter a limited range within which to count, as in COUNT($B$1:$B$25).

Let’s define a dynamic name for category labels, VertCats. There are two ways to do this. One way is to write a parallel OFFSET function that counts appropriate entries in the categories column:

=OFFSET($A$1,1,0,COUNTA($A:$A),1)

Here I’ve used COUNTA which counts all non-blank cells. There was no header label in cell A1. If there had been, I would have to use COUNTA($A:$A)-1 for the number of rows.

An easier and more consistent definition for VertCats makes use of the fact that we already know how long VertValues is, and offsets from that range:

=OFFSET(VertValues,0,-1)

The -1 signifies that VertCats is one column left of VertValues. I can omit the length and width arguments, and VertCats will have the same dimensions as VertValues.

Working with Names

The built-in name management tools are not very powerful, though they’ve improved in Excel 2007. If you plan to use names in Excel, do yourself a favor and run, don’t walk, to Jan Karel Pieterse’s web site and download the Name Manager. This flexible and powerful tool makes working with names effortless.

NOTE: Excel 2007 Charts and Names of Dynamic Ranges

One strange detail recently came to my attention. Dynamic names can be used in Excel 2007 charts as well as in Excel 2003 and earlier charts. If you give the dynamic range a name beginning with the string “Chart”, the Excel 2007 chart will not work. I’ve always used a shorthand like “cht”, so I didn’t notice it. But a colleague of mine, another Excel MVP, pointed it out, and more recently I saw it mentioned on another blog.

Making the Chart Dynamic

How do we get the names into the chart? I find the easiest way is through the series formula, but you can also use the Series Data dialog.

The chart above has a simple series formula:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1)

A series formula has four arguments (five for a bubble chart), which are series name, category labels or X values, Y values, and plot order. In most cases, the X and Y values are range addresses. These addresses can be replaced by the appropriate names:

=SERIES(Sheet1!$B$1,Sheet1!VertCats,Sheet1!VertValues,1)

The chart is unchanged, because the names happened to refer to the cell addresses. One interesting note, since the names were defined with a worksheet scope, is that the series formula will change to show the workbook name, not the worksheet name, in the reference to the name:

=SERIES(Sheet1!$B$1,Book1.xls!VertCats,Book1.xls!VertValues,1)

The difference between workbook-scoped and worksheet-scoped names will be covered in another blog post.

The other way to include the names into the source data is via the Source Data dialog. In Classic Excel, go to Chart menu > Source Data > Series tab; in Excel 2007 go to Chart Tools > Design tab > Select Data; in any version, right click on the chart, and choose Source Data > Series tab or Select Data. Select the series, and you will see the X and Y data ranges in the edit boxes.

Replace the cell references in these addresses by the names you’ve defined. Be sure to include the sheet name, or Excel will not know where to find the range.

When the data for May is added, the names automatically update, and so does the chart. You can see that the highlighted ranges in the sheet have expanded to include May (these colored highlights appear when a chart series is selected).

Series in Rows

“But my data’s in rows! What do I do?” (remember Lisa Lupner and Todd DiLaMuca?)

Since my data’s in months, I’m not going to run out of columns for 20 more years. Suppose my data looks like this:

 

	A	B	C	D	E
1		Jan	Feb	Mar	Apr
2	Pageviews	250,027	269,808	258,863	301,632

 

 

The names use OFFSET as above, with the row and column treatments reversed:

HorizCats
=OFFSET(Sheet2!$A$1,0,1,1,COUNTA(Sheet2!$1:$1))

HorizValues
=OFFSET(Sheet2!$A$2,0,1,1,COUNT(Sheet2!$2:$2))

Enter these names into the series formula:

=SERIES(Sheet1!$A$2,Sheet1!HorizCats,Sheet1!HorizValues,1)

And the chart works the same as above when May’s stats are added.

Further Information

As I said earlier, there are about three bazillion other pages on dynamic charts, and here is where to find more examples on my web site. The Dynamic Chart Links page contains links to other sites.

Dynamic and Interactive Charts (overview)
Dynamic Column Chart
Another Dynamic Chart
Chart the Last 12 Months
Dynamic Charting By Dates (TechTrax Article)
Charting Dynamic Normalized Ranges
Dynamic Chart Links