Introducing the Power of Excel Range Names
Excel's range names offer more power and flexibility than you might think. And they're not hard to use. Here's an introduction to the power that Excel provides.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
Many Excel users seldom use range names. This fact always surprises
me because Excel names are powerful, flexible, and easy to use.
Excel offers two standard ways to define range names, Define Name and
Create Name. For most purposes, Define Name offers the most power. To
use it, choose Insert, Name, Define. Then, in the Define Name dialog,
enter the name and then enter the name's formula in the Refers edit box.
|Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more.
Excel Names That Return Values
The examples that follow all return references to spreadsheet ranges. However,
Excel names don't need to
They also can return values.
To illustrate, these two names are properly defined in Excel:
ReportDate contains the date serial number for 1/1/2006. And Company
In both these examples, it probably would make more sense to set up
cells in the workbook to contain these two values. But that's not the
point. The point is that Excel isn't limited to naming ranges in a
worksheet; Excel also can name text and numeric values.
Names also can name formulas, which can return values. To illustrate, you
||=PMT(MyRate, MyNumPeriods, MyPV)
Here, the name MyPayment defines a formula that relies on other names
to return a payment amount. Those other names could refer to cells in a
spreadsheet or they could contain the actual values.
Excel Workbook Names That Return References
Most Excel names and definitions look something like this:
These Excel names have a workbook scope. You can use them in any worksheet in
your workbook. To illustrate you could use these formulas anywhere in
These names reference specific ranges. Therefore, when you insert or
delete rows above these ranges, or columns to the left of them, the
definition of the names will adjust accordingly.
Excel Worksheet Names That Return References
You can define Excel names that typically are used only in a single
worksheet. These are said to have a worksheet scope. To define these
names, include the name of the worksheet as part of
the range name. To illustrate, you could create these names and
To define these names, enter Sheet2!CurrentDate and
Sheet2!Sales as the name in the Define Name dialog.
In Sheet2, you would use these names just as you would use a
name with a workbook scope. For example, you could enter =SUM(Sales). When you do so, Excel
returns the sum of the Sales range defined on Sheet2.
In Sheet3, you would enter:
=SUM(Sales) to refer to the name with the workbook
scope, defined for
=SUM(Sheet2!Sales) to refer to the name with the
worksheet scope defined for
As with the first category above, because these names refer to
specific ranges they adjust as you insert or delete rows or columns.
Multiple-Area Excel Ranges
You can define both workbook and worksheet names to refer to more than one
area in an Excel worksheet.
To do so, select the areas you want to define, and then assign a
name as you would for a single area. To illustrate, you first could
select column B. Then you could hold down your Ctrl key and select
column E. With these two areas selected, choose Insert, Name, Define to
define a name as you normally would.
Excel Workbook Names That Use Relative References
can define a name with a workbook scope that refers to one or more cells that are a relative
distance from the cell in which the name is used. These names can be
very useful at times.
To illustrate, the formula in cell A5 is =SUM(A1:A4). Suppose
you insert a row above row 5 and enter a value of 1000 in the new cell
A5. What value will the formula in cell A6 return?
In Excel 2003, Excel will automatically adjust the SUM formula (now
in cell A6) to include the new value. However, Excel does this only if
it has the proper setting. Choose Tools, Options. In the Edit tab, ensure that
"Extend data range format and formulas" is checked.
In other versions of Excel, or in Excel 2003 if the option isn't
checked, the formula won't change.
To get around this problem, I often define the range name NextUp.
This name always refers to the cell immediately above the cell in which
the name is used. At times, I also define NextLeft and NextRight, for
similar reasons. Here is the best way I've found to define
In each of these formulas, the INDIRECT function converts the
reference as text in R1C1 notation into an actual reference. Here's what
the R1C1 notation means in each instance
- "R[-1]C" One row above the current cell, in the same column.
- "RC[-1]" One column to the left of the current cell, in the same
- "RC" One column to the right of the current cell, in the same
The second argument in each of these examples tells Excel that the text
is in R1C1 style. Using 1 or TRUE in the second argument would tell
Excel that the text reference is in A1 style. However, using an A1-style
reference wouldn't work in this instance, because INDIRECT would return a
reference to any specific cell you specify.
By the way, you might have seen a name defined like this:
|With cell B2 active...
This style is dangerous. In fact, any reference like "=![Ref]" is dangerous.
It can cause a crash in some versions of Excel. It also can create
problems when used with VBA.
Avoid the "=![Ref]" reference style in Excel.
Excel Worksheet Names That Use Relative References
If you want to use relative references with names that have a
worksheet scope, you have two choices. First, you can use INDIRECT
formulas with R1C1, as shown above. Second, you can use relative references
that include the sheet name as shown here:
|With cell B2 active...
Notice that the definitions of all of these names exclude the "$"
in their references.
Therefore, all the names return a reference relative to the cell that
was active when these names were defined. Because cell B2 was active,
these three names serve the same function as the names above that use
the INDIRECT formulas.
Excel Names That Use Mixed References
Names with a workbook scope must use the R1C1 style with INDIRECT, as
In all worksheets in your workbook, MyRow1 always refers to row 1 in
the current column of the current worksheet. And MyColumnA always refers to
column A in the current row of the current worksheet.
Notice, however, that the fixed rows and columns don't change if you
insert new rows or columns in your worksheet. They only change if you
redefine your range name.
Names with a worksheet scope can use standard A1-style referencing:
|With cell B2 active...
Here, MyRow1 refers to row 1 in the current column of Sheet1.
And MyColumnA refers to column A in the current row of Sheet1.
Notice, however, that because the names reference specific ranges,
the ranges will change if you insert rows above them or columns to their
left. To illustrate, if you insert a new column A, the name
Sheet1!MyColumnA then will reference column B.
Excel Names That Reference Other Workbooks
Suppose that a workbook named MonthlySales.xls contains a range
named Data. In some other report workbook, you could define:
When you define a name like this, it simplifies formulas that use external
references. To illustrate, a formula like...
...is much easier to work with than a formula like...
If you ever want to change the source of your Data to, say,
NewMonthlySales.xls, it's easy to do. In the report workbook choose
Edit, Links, and then click the Change Source button to point all links
from MonthlySales.xls to the new workbook.
Excel Names Using Formulas That Return References
Names can use spreadsheet formulas to return cell references, as
the following examples illustrate.
Names defined using the
OFFSET function can return a value or
reference based on the top-left cell of a reference range. Here, in the MyList example, the value in the NumRows cell determines the height of
the range that MyList returns.
||=OFFSET(reference, rows, cols, height, width)
||=OFFSET(TopLeftCell, 0, 0, NumRows, 3)
Names defined with the
INDEX function return the value or reference
at the intersection of the row and column numbers...if specified. If a
row or column number is omitted, the name returns the entire column or
row that is specified.
||=INDEX(array, row_num, column_num)
||=INDEX(MyDataRange, RowNum, ColumnNum)
||=INDEX(MyDataRange, RowNum, )
||=INDEX(MyDataRange, , ColumnNum)
Names defined using the CHOOSE function return the value or reference
from a list. Here, the ChoiceNumber could have the value of 1, 2, or 3,
and return the reference specified.
||=CHOOSE(index_num, value_ref1, value_ref2, ...)
||=CHOOSE(ChoiceNumber, MyRef1, MyRef2, MyRef3)
Names defined with the IF function can return values or references,
based on a logical test. Here, if TestValue equals 3, the MyChoice name returns
MyRef1. Otherwise, it returns MyRef2.
||=IF(logical_test, value_if_true, value_if_false)
||=IF(TestValue=3, MyRef1, MyRef2)
Excel Names Using Array Formulas
worksheet, array formulas loop through a range of values, performing
various tests and returning values for the items that pass the test. To array-enter a formula, you type in the formula then
hold down the Ctrl and Shift keys when you press Enter.
Names defined using array-formula logic will use that logic. To
illustrate, suppose you array-enter the following formula in a cell. It
will return the sum of the Ties sold in Scotland.
Here, when Product equals Ties and Region equals Scotland, Excel
puts the Amount in a temporary array. Then SUM returns the sum of
those amounts. In the example, the sum equals 8.
Similarly, if you define
MySales using the same formula, and then enter =MySales into a cell, the
cell returns the value 8.
This introduction hasn't explained the many benefits you can gain
from using each of Excel's wide variety of naming techniques. Instead,
I've tried to provide an overview of the key ways that you can use Excel
I certainly haven't explained every technique you could use. As you
work with Excel, keep this power in mind. I'm certain you'll discover
additional ways to gain even more power from Excel.
Take Your Next Steps
If you're looking for additional help with
this topic, I can help you in three ways. To learn
Excel Training, Coaching, and Consulting.