Section 12.2. Descriptive Names for Cell References

12.2. Descriptive Names for Cell References

One of the obvious problems with Excel formulas is that they don't make for the easiest reading in the world. For example, consider this formula:

 =(A1+A2)*B1

It's immediately obvious that this formula adds together two numbers (the numbers in the cells A1 and A2), and multiplies the result by a third number (the number in cell B1). However, the formula gives absolutely no clue about the purpose of this calculation. There's no way to know whether it's converting currencies, calculating a discount, or measuring the square footage of your llama day care center. To answer these questions, you need to look at the worksheet and track down the cells this formula references.

On the other hand, consider the next formula, which uses descriptive names in place of cryptic cell references. Although it performs the same calculation, this formula provides much more information about what it's actually trying to accomplishcalculating the retail price of a product:

 =(ProductCost + ShippingCost) * MarkupPercentage

Excel lets you build formulas using such descriptive names, or named ranges . All you have to do is define the ranges as you create your worksheet, then you can use these names instead of cell references.

GEM IN THE ROUGH
Getting a List of Names

After defining dozens of different names on a worksheet, you may appreciate an easy way to review them. Excel lets you paste a list of all names into your worksheet, complete with their cell references. You might use this approach to create a quick list, print it out, and then remove it from your worksheet.

To generate the list of names, click the cell where you want to start the list. Remember, you'll need two free columns

one for the range names and one for the range addresses and you don't want to overwrite any existing data. Once you're at the right place, select Insert Name Paste. When the Paste Name dialog box appears, click the Paste List button.

This name list is static, which means Excel doesn't update it if you add more names to your worksheet. Instead, you'll need to generate the list again.

Named ranges provide other benefits besides conveying the meaning of a formula:

They make complex, nested formulas more understandable.
They make it easy to quickly find a cell or select a group of cells. That makes them ideal for navigating large worksheets or for applying formatting to cell ranges that frequently change.
They use absolute references. That way, you don't need to worry about having a formula change when you copy a formula from one cell to another. (Although 99.9 percent of all Excel fans use absolute references for named ranges, this is one Excel rule you can break for some unusual tricks. To learn about this technique and other unusual tricks, check out Excel Hacks by David and Raina Hawley [O'Reilly].)
They add an extra layer between your formulas and your worksheet. For example, if you change the structure of your worksheet, you won't need to modify your formulas. Instead, you simply need to edit the named ranges so they point to the new cell locations. This ability is particularly useful when writing macros because it lets you avoid writing direct cell references into your code.

In the next few sections, you'll learn how to create named ranges. You'll also pick up a few tricks for defining and applying names automatically.

12.2.1. Creating and Using a Named Range

Creating a named range is easy. Just follow these steps:

Select the cells you want to name.

You can name a single cell or an entire range of cells.
Look at the left end of the Formula Bar, which indicates the address of the current cell (for example, C5) in the Name Box. Click the Name Box once.

When you click the Name Box, it turns into a text box with the cell address selected.
Start typing the name you want to use.

What you type replaces the cell address.
Press Enter to confirm the new name.

You can now select the name at any time (see Figure 12-5). Click the drop-down arrow to the right of the Name Box, and a list appears with all the names that are defined in the workbook. When you click a name, Excel jumps to the appropriate position and selects the corresponding cell or range of cells.

Figure 12-5. In this worksheet, a named range makes it easier to use the student grades in your formulas without remembering specifically where the cells are. (It also lets you change the range and update all your formulas in one step, if the worksheet changes.) To define the range for student scores, simply select the cells with the numeric scores (B2 to B10), and enter the new name using the Name Box on the Formula Bar (top). Cells B2 to B10 are now named Scores. Once the name is defined, you can use it in two ways (bottom). You can use it in existing formulas (as demonstrated with the AVERAGE( ) function here), or you can select the name from the drop-down list in the Formula Bar (which automatically selects the cells in the range).

Note: When you name a range, you have to follow certain rules. All names must start with a letter, and they can't contain spaces or special charactersexcept the underscore ( _ ). Also, all names must be unique. Finally, you can't create a range name that matches a valid cell address. For example, BB10 is not a valid range name, because there's a cell in every worksheet with the address BB10.

Once you've created a name, you can use the name in any formula just as you would any cell or cell range. As a shortcut, you can pick the name from a list that includes all the names in your workbook. To do so, start entering your formula, and then select Insert Name Paste. A Paste Name dialog box appears showing the list of names. Select the name you want to use, and then click OK to insert it into the formula.

12.2.2. Managing Named Ranges

The Formula Bar is a quick and handy way to create named ranges, but it doesn't let you delete a named range, change its name, or redefine it (so that it designates a different area, for example). For these tasks , you need to use the Define Name dialog box (Figure 12-6).

Figure 12-6. Editing names is not quite as intuitive as creating them. If you've already named a group of cells, and then you select these cells on your worksheet and enter a new name using the Formula Bar, you'll actually end up with two named ranges, both of which point to the same range. To delete the original range, you need to select Insert Name Define. This command opens the Define Name dialog box shown here. Using this dialog box, you can create new names or delete existing ones (just select the name from the list and click the Delete button).

Consider what happens with the worksheet of student test scores if you add a new student. In that case, you need to expand the Scores name range to include the new student. Here's how it works:

Select the cells that represent the new location for the named range.

You need to select both the cells in the old range plus the new cells you want to add.
Select Insert Name Define.
In the text box at the top of the dialog box, enter the range name. In this case, the range name is the same as the name used earlier: Scores.

Don't select the Scores name from the list. If you do, the "Refers to" text box at the bottom of the window reflects the old name range, which isn't what you want.
Click the Add button.

Excel replaces the named range, if it already exists. You won't receive any warning or confirmation message.
Click OK to close the window.

The Define Name dialog box also provides the ability to remove names that you don't want to use. To remove a name, simply select it and click Delete. Any formulas using that name display the error code #NAME? , indicating that Excel can't find the named range.

12.2.3. Naming Formulas and Constants

The Define Name dialog box also provides the key to unlock additional naming features. Using this dialog box, you can create names that point to things other than cell ranges. For example, you can create nicknames for frequently used formulas (such as "My_Net_Worth"). This section describes how.

Internally, Excel treats all names as formulas. When you create a named range, Excel simply generates a formula that points to that range, like =$A$1:$A$10 . You can check by opening the Define Name dialog box (choose Insert Name Define) and selecting a named reference. The corresponding formula appears in the "Refers to" text box at the bottom of the window.

Although most names refer to cell ranges, there's no reason why you can't create names with different formulas. For example, you can use this approach to define a fixed constant value that you want to use in several formulas. Here's an example:

 =4.35%

To create this constant, enter the name in the text box at the top of the Define Name dialog box (for example, SalesCommission), enter the formula in the "Refers to" text box, click Add, and then click OK. You can now use this name in place of the constant in your worksheet calculations:

 =A10*SalesComission

One common reason to use named constants is to make it easier to insert text that you use frequently. For example, you might want to declare a company name constant:

 ="Acme Enterprises, Incorporated."

You can then use that text in a number of different cells. And best of all, if the company name changes, you simply need to update the named formula, no matter how many times it occurs on the worksheet!

Similarly, you can create map names that reference more complex formulas. These formulas can even use a combination of functions. Here's an example that automatically displays the name of the current day:

 =TEXT(TODAY( ), "dddd")

Tip: Overall, you'll probably find that the most useful type of name is one that refers to a range of cells. However, don't hesitate to use named constants and named formulas. They're particularly useful if you have static text or numbers that you need to use in multiple places.

12.2.4. Automatically Creating Named Ranges

Excel also has the built-in smarts to automatically generate named ranges for you. To perform this trick, Excel searches a group of cells you select and, with a little help from you, identifies which cell or cells would serve as appropriate names. For example, if you have a column title in cell A1, Excel can use the text in that cell to label the range of cells underneath it.

To use automatic naming, follow these steps:

Select the cells for which you want Excel to create one or more named ranges.

Excel lets you quickly create one or more named ranges, as shown in Figure 12-7. Your selection must include the cells that you want to be part of the named range, plus the cell or cells that contain the descriptive text.
Select Insert Name Create.

The Create Names dialog box appears.
Specify which part of your selection has the text label (or labels) you want to use by clicking the appropriate checkbox.

In order to create a name, Excel has to find some text to use as a label. You use the Create Names dialog box to tell Excel where that text is. For example, if the text is contained in column headings, select "Top row." If there are row and column headings, select "Top row" and "Left column." Excel selects the options that it thinks make sense based on your selection.
Click OK to generate the names.

Excel's automatic naming ability seems like a real timesaver, but it can introduce as many problems as it solves . Here are some of the quirks and annoyances you should be on the lookout for:

The named ranges might not be the ones you want, especially if you use long, descriptive column settings. For example, the name Length is more manageable than the name Length_in_Feet which is what Excel sticks you with if your name-containing cell happens to contain the text Length in Feet . Unfortunately, there's no way to rename a named reference. Instead, you need to redefine it by hand. (Another, slightly easier option is to choose Insert Name Define, select the named range, and then change the name and click OK. Doing so creates a new named range that has the same reference. You can then delete the original named range by selecting it and clicking Delete.)

Figure 12-7. Top : Excel helps you quickly create named ranges. Use the Create Names dialog box to indicate which cells should be used to generate the names. If you just want to create a single named range (from a vertical list, for example) you'd pick Top Row in the Create Names dialog box. Here, the named range is Pet_Wish_List (Excel automatically inserts underscores in place of spaces), and the included cells in this range are A2 to A6.
Bottom : You can also use Create Names with a table of data. In this case, by picking Left Column, each of the animals in column A will be used as the title for a different named range. The Rhino range, for example, contains cells B3 to E3. If you pick the Top Row check box, Excel will also create named ranges for each column, like Choice_1 (cells B3:B6), Choice_2 (cells C3:C6), and so on. It's up to you whether you want to create ranges for row headings, column headings, or both.

If you frequently use the Create Names tool, you may suddenly find yourself with dozens of names, depending on the complexity of your worksheet. Since you haven't created these names yourself, you won't necessarily know what each one references. One way to figure it out is to use the technique described in the box on Sidebar 12.2.
Excel won't generate names if your column or row labels contain only numbers. For example, if you have a table with a list of part numbers or customer IDs, Excel assumes the numbers are a part of the data, and not labels it can use for naming.
If you don't have any row or column labels, Excel can't generate any names.

12.2.5. Applying Names to Existing Formulas

Named ranges are extremely helpful, but unless you've planned out your spreadsheet writing in a careful, orderly manner, you may find that you spend more time managing these names than actually using them. For example, suppose you've written a bunch of formulas that work their magic on a table of data that doesn't use any named ranges. Then, after you've written the formulas, you catch the named range bug and apply names to, say, all the columns in your table. Your formulas still work, but they don't use the names you've just created.

Note: There's one thing to remember before you get started with the Apply Names feature. Excel can use named ranges only if you've defined them. If your worksheet doesn't include any named ranges, or if they don't match the ranges used in your formulas, this feature won't do anything.

So you've either got to go through and manually revise each formulaso that, for example, =SUM(A2:A85) becomes =SUM(My_Stocks) or, if you're not a fan of carpal tunnel syndrome, you can use one of Excel's most valuable shortcuts: the ability to automatically replace old-school alphanumeric cell references with reader-friendly named range labels. To use this time-saving technique, follow these steps:

Select the cells that contain the formulas you want to change.

Of course, you'll want to use this shortcut only if you've got a bunch of formulas that don't yet use one or more existing named ranges. Select the cells that contain the formulas you want to change. This shortcut can work on more than one formula at a time (even if each formula references a different named range), so it's okay to select a group of formulas.

Select Insert Name Apply.

The Apply Names dialog box appears (Figure 12-8), with a list of all the names in your workbook. Excel searches for and highlights the appropriate names. If there are ranges you don't want to use in your formulas, deselect them.

If the Ignore Relative/Absolute checkbox is turned on, Excel changes any matching reference, whether it's absolute or relative. This behavior is usually what you want because most people writing formulas use relative references (like A2 instead of $A$2)unless they explicitly need absolute references to make copying and pasting easier. If you don't turn this option on, Excel replaces only absolute references.

You'll also notice the "Use row and column names" checkbox. This setting applies only if you generated names using the Insert Name Create technique described in the previous step. If you've used this technique to create row and column names, you can turn off the "Use row and column names" checkbox to prevent Excel from using these names in your formulas. You might skip this step because the formulas Excel creates with the row and column names can be horribly complex. That's because in order to get a specific cell in a range, Excel uses range intersection . For example, consider the grid of pet names in Figure 12-7. If you apply names with the "Use row and column names" checkbox turned on, Excel converts a simple formula like =B3 into =RHINOS Choice_1 . In other words, Excel notices that the intersection of the RHINOS range (B3:E3) and the Choice_1 range (B3:B6) is the single cell B3. You may consider this either an elegant adjustment or an unnecessary way to complicate your life.

Figure 12-8. When you use the Insert Names Apply command, the formula in B7 changes from =FV(B2/B4,B3*B4,-B5,-B1) to the more understandable (but longer) formula =FV(Rate/Payments,Years*Payments,-Contribution,-Principal).