How to Create and Use Dynamic Range Names in Excel

(Download the example workbooks.)

We Excel users often 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. Then, in the following month, we must point our existing report to new data.
• We might use the TREND function to forecast weekly sales. But then, in the 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 Formulas, Defined Names, Name Manager (or press Ctrl+F3), the Name Manager dialog will show Refers To formulas that look something like these:

=Sheet1!$B$2
=Sheet1!$B$3:$C$6

However, Excel names aren’t limited merely to cell addresses. In fact, range names (which Microsoft calls merely “names”) probably should be called “formula names.”

To illustrate, you could assign the name “Test” to either of these formulas:

=”Hi There!”

Here, when you enter the formula =Test in a cell, Excel returns “Hi There!”. That is, names can define formulas that return text.

=SQRT(9)

And here, the name Test returns the value 3, which is the square root of 9, of course. That is, names can define formulas that use worksheet functions.

However, neither of these definitions references a range in a spreadsheet, so they aren’t very useful. And and they’re certainly not dynamic.

To define dynamic names, we need to define formulas that use worksheet functions to return references.

Functions That Return References

In Excel, a reference points to an area of a worksheet . To illustrate, these are references:

=$A$1
=Sheet1!$F$10:$F$11
=Sheet1!$D$4,Sheet1!$E$7

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 specific formula returns a reference:

1. Select the entire formula in your formula bar.
2. Press Ctrl+c to copy it.
3. Press Esc to return to the Ready mode.
4. Press the F5 function key to launch the Go To dialog.
5. Paste the formula into the dialog’s Reference box.
6. Choose OK.

If Excel selects an area of your worksheet, the formula returns a reference.

You’ll typically use two functions to return references for dynamic range names: OFFSET and INDEX.

Using the OFFSET Function in Dynamic Names

The OFFSET function returns a reference that’s offset from a specified reference. It has this syntax:

=OFFSET(reference, rows_offset, cols_offset, height, width)

The height and width arguments are optional. If they’re excluded, the resulting reference takes the shape of the reference argument.

In this spreadsheet, the Total cell has this formula for the cell shown:

B6:   =SUM(MyData)

And the name MyData currently references the area that I’ve highlighted in blue.

I’ve defined MyData as a dynamic name, so that it can change size and position in response to the settings at the top of the sheet.

But before you define MyData, your work will be easier to understand if you define five normal range names.

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 Formulas, Defined Names, Create From Selection, to launch the Create Names dialog. (Or you can press Ctrl+Shift+F3.)

Then, in the Create Names dialog, make sure that only Left Column is checked, and then choose OK.

Second, select the range A8:A14. Then launch the Create Names dialog again to assign the name Month to the data below the Month label.

Then, to define the MyData name, choose Formulas, Defined Names, Defined Name to launch the New Name dialog. (Or you can press Ctrl+Alt+F3.) Then type in MyData as the Name; and in the Refers To box, enter this formula…

=OFFSET(Month, Rows, Cols, Height, Width)

…where…

…Month is the named reference range.
…Rows contains the number of rows offset from the Month range.
…Cols contains the number of columns offset from the Month range.
…Height contains the height of the resulting reference.
…Width contains the width of the resulting reference.

Then choose OK.

To test that your name is working correctly, press the F5 function key to launch the Go To dialog; type in MyData as the Reference; then choose OK. If you’ve defined MyData correctly, Excel should select the range marked in blue in the figure above.

(Note that the Go To dialog doesn’t display dynamic range names in its list box. This is why you must type in MyData manually, rather than selecting the name from a list.)

You can use MyData in any formula that expects a reference, as I show in cell B6 in this figure and in the previous one.

And because this is a dynamic range name, you could change the Rows value to 2 in cell B1, and the Width value to 2. This would cause the SUM formula to return the sum of the range B11:C13 as I’ve marked in blue in this figure.

Using the INDEX Function in Dynamic Names

Suppose that the Data column in this figure contains monthly performance data. We can define the name MyData to return the data for the month specified in cell B2.

In this example, I used the Create Name dialog to assign the name Month to the yellow cell. And I used the dialog to assign the Date and Data names to the columns shown beneath those labels.

And then I defined MyData as…

=INDEX(Data,Month)

…where Month is the index value within the Data range. Here, for example, March is the third value in the range.

The Value cell contains this formula for the address shown:

B4:   =MyData

Using INDEX with the MATCH Function

In the prior example, you need to enter the value 3 in the yellow cell. Then, in the following month, you would need to enter 4. But instead, you could enter a formula that returns the Month number by looking up the CurDate value in the Date column.

Here, I’ve assigned the yellow fill to cell B1 to indicate that it contains the setting value. (Cell B1 was ignored in the previous figure.)

And now, here’s the formula that returns the Month value:

B2:   =MATCH(CurDate,Date,0)

…where…

…CurDate is the value to look up.
…Date is the range in which to look.
…0 tells the MATCH function to return an exact match.

Often in Excel formulas you can combine INDEX and MATCH into one formula, like this:

=INDEX(Data,MATCH(CurDate,Date,0))

You can download the example workbooks here.

For more information about INDEX-MATCH see:

• Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
• How to Use INDEX-MATCH, Part 1: The INDEX Function