Excel Range Names
How to Create and Use
Dynamic Range Names in Excel
Dynamic range names in Excel are easy to use and give your formulas more power. Here's how to set them up.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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 mostrecent 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:
 Select the entire formula in your formula bar.
 Press Ctrl+c to copy it.
 Press Esc to return to the Ready mode.
 Press the F5 function key to launch the Go To dialog.
 Paste the formula into the dialog's Reference box.
 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))
Take Your Next Steps
You can take the next step with dynamic names in two ways. First, you
can
download the example workbooks
here.
Second, if you want to use dynamic names in more advanced
ways in your normal reports and analyses, I can help you in
two ways. To learn more, see
Excel Coaching and Consulting.
