An Excel Tutorial:
Excel's Dynamic Range Names
Dynamic range names in Excel are easy to use and
they are difficult to discover without a few hints. This article
provides those hints.
by Charley Kyd
Often, we Excel users refer to ranges that need to move or expand in
future versions of our reports. For example:
- Each month, we must report the current month, the year to date,
and, perhaps, the twelve most-recent months of financial data. Next
month, we must point our existing report to new data.
- We might use the TREND function to forecast weekly sales. Next
week, we must point the function at one more week of data.
- We occasionally need an easy way to point one chart at different
areas of a spreadsheet database.
Dynamic range names can meet all these requirements.
Names Have More Power Than You Might Think
Most of the time, Excel range names are just that: a name assigned to
a range in an Excel worksheet. With this approach, when you choose
Insert, Name, Define, and then click on a defined name, the Refers To
box will show a definition that looks something like one of these
However, Excel names aren't limited merely to cell addresses. To illustrate,
you could assign the name "Test" to either of these definitions:
When you enter the formula =Test in a cell, Excel returns "Hi There!".
This isn't a very useful result, but it does illustrate that you can use
names to do more than reference cells in a spreadsheet.
Here, the name Test returns the square root of the value entered in cell
B2. That is, names can contain spreadsheet functions.
However, neither of these definitions returns a reference to a range
of a spreadsheet. Neither defines a dynamic range name. To do that, we
need to use spreadsheet functions that return references.
Functions That Return References
In Excel, a reference points to an area of a spreadsheet. To
illustrate, these are references:
In contrast, ="$A$1" is not a reference, the formula merely returns
the text "$A$1".
Here's a quick way to test whether a formula returns a reference:
Choose Edit, Go To (or press the F5 function key); enter the formula in
the Reference box; then choose OK. If Excel selects an area of your
worksheet, the formula returns a reference.
I typically use two functions to return references for dynamic range
names: OFFSET and INDEX.
Using the OFFSET Function
The OFFSET function returns a reference that's offset from a
specified reference. It has these arguments:
=OFFSET(reference, rows, cols, height, width)
The height and width arguments are optional. If they
are excluded, the resulting reference takes the shape of the
Suppose in the
nearby spreadsheet we want to define the name MyData as:
=OFFSET(RefCell, Rows, Cols, Height, Width)
First assign the names shown in the range A1:A4 to the
adjacent cells in column B. To do so, select the range A1:B4; choose
Insert, Name, Create; choose Left Column; then choose OK.
Next, use Insert, Name, Define to assign the name RefCell to cell B6.
To define the MyData name, choose Insert, Name, Define; enter MyData
as the Name; enter the OFFSET formula shown above into the Refers To box; then
To test that your name is working correctly, press the F5 function
key to launch the Go To dialog; enter MyData as the Reference; then
choose OK. If you've defined MyData correctly, Excel should select the
(Note that the Go To dialog doesn't display dynamic range names in
its list box. This is why you must enter MyData manually, rather than
selecting the name from a list.)
If you enter =MyData in a cell, Excel returns the #VALUE! error,
because the formula is returning a multi-cell reference, rather than one
cell. You would get the same result if you were to enter this formula:
However, you can use MyData in any formula that expects a reference.
For example, the formula...
...returns the value 8.
And because this is a dynamic range name, you could change the Cols
value to -1 in cell B3 above, which would cause the SUM formula to
return the sum of the range A7:A9.
Using the INDEX Function
the Data1 column in this figure contains monthly performance data. As
before, I assigned the labels in the range A1:A2 to the corresponding
cells in column B; and I defined cell B4 as RefCell.
To display the current month's data you could define CurMonth as:
Here, with Month equal to 4, CurMonth returns a reference to cell B8.
However, there are two practical problems with this approach. First,
the INDEX function requires that we specify a full range to choose from.
Therefore, when we add data to cell B12, the function no longer works.
Second, even if we could solve the first problem, we would have at least
two functions to worry about: one function that returns the current
month and a second that returns the year-to-date amount.
Instead, we can use INDEX in combination with OFFSET.
Combining INDEX and OFFSET
When you define a range name using OFFSET, you can use it like any
other name. Specifically, you can return an INDEX of this name.
To see what I
mean, you could define CurYr (the current year) as:
And you could define CurMonth (the current month) as:
Here, the dynamic name CurYr returns the range of cells for the
current year. By indexing on the last cell in the range, the Month cell,
we return the value for the current month. Next month, when you enter a
value in cell B9 and update the Month value in cell B1, the CurMonth
value automatically will update to report cell B9.
Using Dynamic Names in Charts
At the beginning of this article I said that you could use dynamic
range names in charts. To do so, select any
appropriate range, set up your chart, then modify the SERIES function in
As above, you could define RefCell as cell B4 and CurYr (the current year) as:
Then chart the range B5:B7 in this
figure. This would produce a SERIES function something like this:
Edit this formula to be:
Now, whenever you change the value in cell B4 above, the CurYear
range changes, which changes the data displayed by the chart.
Dynamic Names for Monthly Reporting
dynamic names can make monthly reporting a breeze.
To illustrate, this figure shows three displays that typically would
be in three sheets of one workbook.
I typically put the top display in a sheet called Control, the middle
display in a sheet called Data, and the bottom display in a sheet called
Often, when several reports need to use the same data, I'll set
up a separate report workbook for each report. Also, of course, the Data
section could have hundreds of rows of data, not merely two rows, as
Each month, you merely need to insert a new column to the left of the
right border column, which is column F for the month shown. You enter
the data for the new month. You add 1 to the value of the MonthNum in
cell D3. Then you recalculate and print.
To set up this worksheet, first set up the middle section. Here, all values are numbers and text entered into
cells; no data is returned by formula. The range names in this section are:
For the top section, use Insert, Name, Create to assign
the labels in the range C1:C4 as names for the adjacent cells in column
D. Cells D1 and D2 have the values shown. The other two cells have these formulas:
Next, define two dynamic range names for the workbook:
Notice that both the OFFSET and INDEX functions have missing
arguments. In the OFFSET function, the missing height argument causes
Excel to use the height of the original Data range reference. Similarly,
in the INDEX function, the missing row argument causes Excel to return
all rows in the CurYrData range.
Finally, in the simple report, cell D13 contains the formula for the
Cell A15 contains the code to report from the
Data range. Cell B15 contains a formula that returns the row number of
that code in the data:
Cell C15 contains a formula to return data from the appropriate row
number of the current month's data:
Cell D15 contains a formula to return the year-to-date total for the
specified account number:
Copy the formulas in the range B15:D15 to the range B16:D16.
I've only touched on the power that dynamic range names offer. Watch for
additional articles on this topic in the months ahead.