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.
Excel Names That Return Values
The examples that follow all return references to spreadsheet ranges. However, Excel names don’t need to return ranges. They also can return values.
To illustrate, these two names are properly defined in Excel:
| ReportDate | =38718 |
| Company | =”Acme, Inc.” |
ReportDate contains the date serial number for 1/1/2006. And Company contains text.
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 could define:
| MyPayment | =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:
| CurrentDate | =Sheet1!$B$5 |
| Sales | =Sheet1!$C$4:$C$7 |
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 your workbook:
=TEXT(CurrentDate,”mmmm yyyy”)
=SUM(Sales)
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 definitions:
| Sheet2!CurrentDate | =Sheet2!$B$5 |
| Sheet2!Sales | =Sheet2!$C$4:$C$7 |
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 Sheet1.
=SUM(Sheet2!Sales) to refer to the name with the worksheet scope defined for Sheet2.
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. Microsoft calls them discontiguous ranges.R
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
You 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 these names:
| NextUp | =INDIRECT(“R[-1]C”,0) |
| NextLeft | =INDIRECT(“RC[-1]”,0) |
| NextRight | =INDIRECT(“RC[1]”,0) |
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 row.
- “RC[1]” One column to the right of the current cell, in the same row.
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… | |
| NextUp | =!B1 |
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… | |
| Sheet1!NextUp | =Sheet1!B1 |
| Sheet1!NextLeft | =Sheet1!A2 |
| Sheet1!NextRight | =Sheet1!C2 |
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 illustrated here:
| MyRow1 | =INDIRECT(“R1C”) |
| MyColumnA | =INDIRECT(“RC1”) |
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… | |
| Sheet1!MyRow1 | =Sheet1!B$1 |
| Sheet1!MyColumnA | =Sheet1!$A2 |
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:
| Data | =MonthlySales.xls!Data |
When you define a name like this, it simplifies formulas that use external references. To illustrate, a formula like…
=INDEX(Data,1,1)
…is much easier to work with than a formula like…
=INDEX(MonthlySales.xls!Data,1,1)
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) | |
| MyList | =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) | |
| MyCell | =INDEX(MyDataRange, RowNum, ColumnNum) |
| MyRow | =INDEX(MyDataRange, RowNum, ) |
| MyColumn | =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, …) | |
| MyRef | =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) | |
| MyChoice | =IF(TestValue=3, MyRef1, MyRef2) |
Excel Names Using Array Formulas
In a 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.
=SUM(IF((Product=”Ties”)*(Region=”Scotland”),Amount,0))
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.
Conclusion
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 names.
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
