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 in your spreadsheet.

by Charley Kyd, MBAMicrosoft Excel MVP, 2005-2014
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 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 definitions:

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

However, Excel names aren't limited merely to cell addresses.
To illustrate, you could assign the name "Test" to either of
these definitions:

="Hi There!"
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.

=SQRT(Sheet1!$B$2)
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:

=$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 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 reference argument.

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 choose OK.

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 range B7:B9.

(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:

=B7:B9

However, you can use MyData in any formula that expects a
reference. For example, the formula...

=SUM(MyData)

...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

Suppose
that 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:

=INDEX($B$5:$B$11,Month)

Here, with Month equal to 4, the
INDEX
function in the CurMonth cell 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:

=OFFSET(RefCell,1,0,Month,1)

And you could define CurMonth (the current month) as:

=INDEX(CurYr, Month)

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 the
chart.

As above, you could define RefCell as cell B4 and CurYr (the
current year) as:

=OFFSET(RefCell,1,0,Month,1)

Then
chart the range B5:B7 in this figure. This would produce a
SERIES function something like this:

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

Edit this formula to be:

=SERIES(,,Sheet1!CurYear,1)

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

Using
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 Report.

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 shown here.

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 use the
MATCH
function in these formulas:

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 current month:

=TEXT(Month,"mmm yyyy")

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:

=MATCH($A15,Acct,0)

Cell C15 contains a formula to return data from the
appropriate row number of the current month's data:

=INDEX(CurMoData,$B15,)

Cell D15 contains a formula to return the year-to-date total
for the specified account number:

=SUM(INDEX(CurYrData,$B15,))

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. Why don't you see what you can discover?