In this chapter
Defining a Name for a Cell by Using the Name Box 842
Naming a Cell by Using the New Name Dialog 843
Using the Name Box for Quick Navigation 844
Using Scope to Allow Duplicate Names in a Workbook 845
Using Named Ranges to Simplify Formulas 846
Using a Name to Hold a Value 856
Assigning a Formula to a Name 857
Long before Microsoft introduced tables and formulas like =Revenue-Cost, spreadsheets have offered the ability to assign a name to a cell, to a range of cells, or to a formula. The theory is that using a name for a range would be easier to understand when used in a formula. =SUM(MyExpenses) would make formulas more self-documenting than =SUM(Sheet5!AB2:AB99).
With Excel 2007, Microsoft added a new Name Manager interface to allow you to assign and use names effectively.
There are a variety of uses for names in a workbook. A name can be applied to any cell or range. Names are also useful for the following:
Note
Excel 2007 offers the new Table functionality (described in Chapter 8, “Fabulous Table Intelligence”). Although that feature allows you to create formulas using column names, the individual column names and the table name are not considered named ranges.
There are various ways to name a cell. The easiest way to define a name for a cell is to use the Name box. To do so, you select any cell in your worksheet. To the left of the formula bar is a box with the address of that cell. This box is known as the Name box. The quick way to assign a name is to click inside the Name box and type a name, such as Revenue. Figure 30.1 shows the Name box.
When you press Enter, Excel centers the name in the Name box, which indicates that the name has been assigned. This is your only indication that the name is valid and has been accepted.
The following are some basic rules for valid names:
Table 30.1 provides some examples of valid and invalid names.
The Formulas ribbon contains a group called Named Cells. The following example introduces the New Name dialog:
As you can see in Figure 30.3, the name was applied because the Name box now shows COGS instead of B8.
One advantage of using names is that you can use the dropdown in the Name box to quickly jump to any named cell. This includes cells that might be in distant sections of the worksheet or even on other sheets in the workbook.
If you plan to use the Name box for navigation, assign a name to the upper-left corner of each section of your workbook. The Name box dropdown will then provide a mini-table of contents and people can use the Name box to quickly jump to any section of the workbook.
To illustrate this concept, follow these steps:
As you can see, named ranges are a great tool for quickly navigating a workbook. Note that names are presented in the Name box alphabetically. If you want the names to appear in the Name box sequentially, you can add names such as Section1, Section2, Section3, and so on. Or prefix the section names with letters, such as A-Income, B-Costs, C-Expense, D-Tax, E-Income. Then, you can quickly jump to a section by choosing it from the alphabetical list in the Name box. When used in this way, names in Excel are almost like bookmarks in Word.
Ideally, you would keep the names unique throughout a workbook. Although it is technically legal to add a name such as SectionOne to both Sheet1 and Sheet2, it is not a good idea. When you define the name on the first sheet, it is defined as a name with workbook-level scope. This means that you can easily navigate to SectionOne from any sheet in the workbook. If you attempt to set up the same name on a second worksheet, that name will have to be set up with worksheet-level scope. Names with worksheet-level scope override the workbook-level scope only on the sheets on which they are defined. For example, say you have a workbook with Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5:
You can see that this is fairly confusing. In general, you should stick with unique names that can have workbook-level scope. You should switch to using duplicate names with worksheet-level scope only when you have many nearly identical sheets in a workbook.
As introduced at the start of this chapter, the original reason for having named ranges was to simplify formulas. In theory, it is easier to understand a formula such as =(Revenue-Cost)/Revenue.
Be sure to define the names before entering formulas that refer to those cells. When you create a formula using the mouse or arrow key methods, Excel will automatically use the names in the formula.
In the following example, the worksheet in Figure 30.6 has a name of “Revenue” assigned to A6 and a name of “Cost” assigned to A8. Rather than typing =A6-A8 in cell A9, follow these steps to have Excel create a formula using names.
=
.=Revenues-COGS
, as shown in Figure 30.6. In theory, this formula is self-documenting and easier to understand than =C6-C8.You can also type a formula that uses names directly in a cell. For example, Figure 30.7 shows =Revenue*1.1
entered in Cell E6. When you press Enter, Excel recognizes this formula and multiplies Cell A6 by 1.1.
However, a problem crops up when one of the cells in the formula contains a name—especially if that name is defined strictly for navigational purposes. In this case, Excel will create an absolute reference to that cell. When you copy a formula that contains a name, the copied formula always points to the name. This can lead to unhappy results.
Here is an example to show how easily this can happen.
Figure 30.8 shows Cell A2 named SectionThree so that the name can be used as a bookmark.
In Cell E2 you enter a formula to calculate a due date. Using the mouse method, you type =
, touch Cell A2 with the mouse, type +
, and then touch Cell D2 with the mouse. Instead of entering the formula =A2+D2
, you end up with the formula =SectionThree+D2
.
Select Cell E2 and double-click the fill handle to copy the formula down to all rows. Examine the formula in Cell E5. As shown in Figure 30.9, although Cell D2 was correctly changed to Cell D5 in the copied formula, this cell and all the remaining cells in Column E are incorrectly pointing to Cell A2 because it was previously defined as a named range.
To overcome this problem, you need to use care when entering the original formula: You need to type =
, type A2+
, and then touch Cell D2. This override Excel’s default behavior of automatically converting relative reference names to preexisting range names.
When you learn the trick discussed in “Using Named Ranges to Simplify Formulas,” you might start naming all the input cells in your workbook, hoping that all the preexisting formulas will take on the new names. Unfortunately, this does not automatically work.
In Figure 30.10, the formula in B16 was entered first. Later, cells B9 and B14 were given the names GrossProfit and TotalExpenses, respectively. However, the preexisting formula in B16 continues to reflect the cell addresses instead of the names.
In order to make the names become part of existing formulas, you have to use the Apply command. Here is what you do:
It is possible to define a name that refers to a larger range of cells. For example, you could select C11:C13 in Figure 30.12 and type a name, such as Expenses, in the Name box.
If you later select Expenses from the Name box, your cursor moves to Cell C11, and the entire range is selected. Having a name apply to a range allows formulas such as =Sum(Expenses)
.
To prevent confusion, a valid cell address may not be used as a name. In prior versions of Excel, this eliminated names from A1 through IV65536.
Excel 2007 now has columns named A through Z, AA through ZZ, and AAA through XFD. The same rule applied to Excel 2007 now invalidates names that start with IW through ZZ and AAA through XFD.
You can think of many three-letter names that might have been common in Excel 2003—names like Tax2007, ROI5, and so on. While those are perfectly legal in Excel 2003, they are no longer valid in Excel 2007 because they duplicate existing cell addresses in Excel 2007.
Figure 30.13 shows an Excel 2003 workbook that contains names such as Tax2004, Tax2005, and so on.
You can open this workbook in Excel 2007. The workbook initially opens in legacy mode, with columns only through IV. When you attempt to save the file as an Excel 2007 workbook, Excel warns you that there are defined names that are now equivalent to valid cell addresses in Excel 2007, as shown in Figure 30.14.
After you close the message box in Figure 30.14, Excel attempts to warn you about every existing name. The first message box warns that Tax2004 will become _Tax2004. You can either click OK to each message or skip them by clicking OK to All.
Excel does a great job of updating the names and the formulas that use invalid names. Figure 30.15 shows the Excel 2003 worksheet after it is converted to Excel 2007. Each name now has an underscore at the beginning.
There are some formulas that Excel is not able to update. It would be efficient to rewrite the formula in Figure 30.13 as =B7*INDIRECT("TAX"&YEAR(A7))
. Although this works perfectly in Excel 2003, as shown in Figure 30.16, Excel 2007 is not able to detect this type of name.
When you save the Excel 2003 workbook as an Excel 2007 workbook, each name changes so that it begins with an underscore character (for example, from Tax2004 to _Tax2004). When you do the first recalculation, all the formulas from Cell C7 down fail, as shown in Figure 30.17. You have to edit the formula to manually add the underscore before TAX
: =B7*INDIRECT("_TAX"&YEAR(A7))
.
With Excel 2007 you can add many names in a single command, particularly if the names exist as labels or headings adjacent to the cells.
Say you have a worksheet with a series of labels in Column A and values in Column B. One example is shown in Figure 30.18. To do a wholesale assignment of names to the cells in Column B, you follow these steps:
Excel does a fairly good job of assigning the names. Spaces are replaced with underscores to make the names valid. In this example, cell B4 is assigned the name Net_Sales. Cell C8 is assigned the name Cost_of_Good_Sold. In Row 12, where the label contains an ampersand (&), Excel replaces the ampersand with an underscore, to form the name G_A_Expenses. Although this is not as meaningful as it could be if you wrote the name yourself, it is still pretty good.
Caution
If Cell A12 were G & A Expenses, then Excel would replace every space and ampersand with an underscore, creating the name G___A_Expenses.
Excel uses the labels to the left of the selected cells to apply names.
In Excel 2007, you can apply names by using both the row labels and column headings at the same time. In Figure 30.19, the selections in the Create Names from Selected Ranges dialog mean that six new names will be added to the workbook. For example, Jan will refer to B2:B4.
The Create Names from Selected Range dialog is so flexible that it will even let you choose all four options at once. If you select all the check boxes in the Create Names from Selected Range dialog and then click OK, as shown in Figure 30.20, 12 new names will be added to the workbook.
In this example, the name Mar will refer to D2:D4. The name Month3 will also refer to D2:D4. If you select Month3 from the drop-down, Excel selects D2:D4, as shown in Figure 30.21. However, the name in the Name box reflects Mar because that is the first name, alphabetically, that applies to that range.
Excel 2007 is a great improvement over previous Excel versions in terms of managing names. Whereas the previous six versions of Excel use the Insert Names dialog to manage names, Excel now offers the Name Manager dialog, shown in Figure 30.22. To open this dialog, you click the Name Manager icon on the Formulas ribbon.
The Name Manager dialog shows the five fields for each name. Initially, certain columns may not be wide enough to show all the text in each column. You can resize the entire dialog by using the triangle in the lower-right corner. You can also resize columns by dragging the vertical bars between the column headings.
These are the columns in the Name Manager dialog:
Working with the Name Manager dialog is simple:
#REF!
errors.In the upper-right corner of the Name Manager dialog is a button labeled Filter. You can click this button to access many powerful options. If you have defined names that have scope only to a worksheet, you can select Names Scoped to Worksheet in order to limit the Manages Names dialog to only those names, as shown in Figure 30.23.
These are the options in the Filter drop-down:
#REF!
errors. You can use the Names with Errors filter to find those names.So far all the names defined in this chapter have referred to a cell or a range of cells. It is possible to assign a constant value to a name by using the New Name dialog. You might do this to hold a value that could possibly change, but would likely rarely change. One example might be a sales tax rate.
To use a name to hold a value, follow these steps:
=C2*Sales_Tax
. In Figure 30.25, the range has been defined as a table. Thus, the formula of =[MerchAmt]*Sales_Tax
uses both a table name in square brackets and the defined name Sales_Tax
.Caution
Use care when viewing potentially ambiguous references such as Sales_Tax and [Sales Tax] as shown in Figure 30.26. Remember, the name in square brackets is a table name assigned automatically by Excel.
The advantage of using a name to refer to a constant is that if your tax rate changes, you can simply edit the value defined in the name, and all the formulas in the workbook will recalculate. To edit an existing name, you can click the Name Manager, click on the name, and choose Edit Name.
While names are traditionally used to refer to cells or constant values, an interesting use is to use a name to refer to a formula.
Notice the Refers To box in Figure 30.27. Although the value there is a standard name that refers to a single cell, the Refers To box contains an = at the beginning, which means this named range is actually a formula.
As described in the following sections, there are a variety of situations in which assigning a formula to a name can be useful.
A named formula can allow you to replace a complicated formula with an easy to remember name. In this basic case, the formula contains no cell references.
For example, let’s say you have discovered a fairly complex formula that would be difficult to remember as shown in Figure 30.28
In this case, you could assign the formula =Date(Year(Today()),Month(Today())+1,1)-1
to a name such as MonthEnd, as shown in Figure 30.29
You could then use =MonthEnd
in any cell to calculate the end of the current month.
One cool example of a named formula is a reference that dynamically expands as more data is filled down a column.
Say that you have a list of valid sales reps on a hidden Sheet2 to be used as the list for a data validation drop-down. The list might extend from A1:A10 today, but as new sales reps are hired, the list may expand to A11, A12, A13, and so on.
The OFFSET()
function has a parameter which specifies that a range should extend for X rows. If you use CountA(A:A)
to return the number of rows, you can create a formula to dynamically expand or contract as cells are filled in or deleted. In theory, you would set up a formula to point to this range: =OFFSET(Sheet2!A1,0,0,CountA(Sheet2!A:A),0)
. However, use absolute references in the definition. In Figure 30.30, for example, the formula assigned to RepList is =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
.
The name will automatically expand as new entries are added. To make use of this name in an in-cell dropdown, follow these steps:
Choose Data, Data Validation.
Change the Allow dropdown to List
In the source box, type =RepList.
Leave the In-cell dropdown box checked.
The completed dialog box is shown in Figure 30.31.
Initially, the cell with validation offers a drop-down that lists the 10 current reps, as shown in Figure 30.32.
When the list on Sheet2 is edited, the drop-down in Sheet1 is automatically updated. In Figure 30.33, for example, you can see that Barb replaced Jeanette, and two new reps were added. The window on the left shows the new list on Sheet2, and the window on the right shows the current drop-down list.
You can use a similar technique to make a chart series expand as new months are added.
→ For details on charting, see Chapter 16, “Using Excel Charts,” page 299.
In the example shown in Figure 30.30, it was important to make sure that all references were absolute. Although it seems strange, it is possible to make use of a relative reference in a named formula, as long as you understand one slightly buggy gotcha and two cautions if you use VBA or share the workbook with someone using Excel 97.
In Figure 30.34, the cell pointer is on Sheet1 in Cell A3. The name AboveMe is being defined as pointing to Cell A2. However, in the Refers To box, you need to press F4 three times to remove all the dollar signs from the reference.
This relative formula initially appears to work perfectly. Cells A3:E3 in Figure 30.35 all contain the formula =AboveMe
.
However, if you go to Sheet2 and enter the formula =AboveMe
in Cell A3, the formula returns the value from Cell A2, but from Sheet1 instead of Sheet2. Figure 30.36 shows the answer 6 where you would expect 106.
The solution is to edit the name. To do so, you remove the reference to Sheet1, leaving a definition of =!A2, as shown in Figure 30.37.
Now, when you close the Name Manager dialog and press F9 to recalculate, the formulas work as expected, as shown in Figure 30.38.
=!A2
now works as expected.3.133.158.230