One of the obvious problems with Excel formulas is that they don't make for the
It's immediately obvious that this formula adds together two numbers (the
On the other hand, consider the
=(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
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
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
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.
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
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
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.
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
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.
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:
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:
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")
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
The named ranges might not be the ones you want,
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
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.
Named ranges are extremely helpful, but unless you've planned out your spreadsheet writing in a careful, orderly manner, you may find that you
So you've either got to go through and manually
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
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.