Wonderful as spreadsheets are, there are some aspects to their use that drive users crazy. In a large workbook, for instance, if you want to go to a specific location, a great deal of worksheet-changing and scrolling can be involved. It’s worse if you don’t know exactly where you want to go, you just know what you’re looking for. What if you could just type “Total_Sales” or pick from a list of places and be transported to the exact cell you wanted?
Another problem is figuring out what is going on in a formula in a spreadsheet. You read “=$C$6*$B$3” and you still don’t know what you have in the cell or what two elements are being multiplied. Wouldn’t it be nice if you could read “=(Bob Sales)*Commission_Rate” instead? Or “=SUM(Sales)” instead of the cryptic “=SUM($C$6:$C$10,$F$9:$F$14)”?
Benefits of Having a Good Name
Well, the answer is that you can have the easier-to-read version and much more by using Excel 2000’s ability to use labels and named ranges in formulas. With a little planning, you will find it easier to:
- Document worksheets you regularly use
- Figure out what’s going on in the worksheet
- Modify and maintain formulas
- Navigate worksheets and workbooks
If these benefits are important to you, consider our simple example in Figure 1. Imagine this commission register large enough to handle a hundred distributors and also Year-to-Date sales for five different sales regions. The principles you will see here can be extended to worksheets of any size.
Figure 1: A simple example of using labels and named ranges in formulas
The Naming of Parts
To begin with, the commission rate in this sheet is going to be used in several places. Every time we add another salesperson we will have to look up the cell reference in order to enter the formula to calculate commissions. Why not name the cell “Rate” and reference it in plain English rather than as B3?
Begin by highlighting the cell with the rate in it. On the menu bar, choose Insert, Name, Define (Figure 2).
Figure 2: Naming the cell “Rate” to reference it in plain English rather than as B3
The Define Name dialog box will open (Figure 3). Type the name that will refer to cell $B$3 into the Names in workbook box and click OK. A range name — the name you give to a cell or to a range of cells — must begin with a letter.
There are four other rules that apply to range names:
- They can be 1 to 255 characters long (it is better to keep them short).
- They must not be of the same form as a cell reference (“A1” is not a valid name, “Bob” is).
- Single letter names can be any letter of the alphabet EXCEPT “R” or “C”.
- Any other character can be used except for the hyphen (-) or the special characters $, %, &, and #. You can use the underscore (_) or a period (.) to separate parts of a name.
Figure 3: Typing the name that will refer to cell $B$3
In the example, we named the cell “Rate”. From this point on, you will be able to type Rate instead of $B$3 and Excel will understand that you mean the contents of this cell. Incidentally, you can also open the Define Name dialog box by pressing Ctrl + F3 instead of using the menu.
The commission rate is also an example of a “constant” — a value that does not change and is used in formulas in the workbook. You can define a constant and its fixed value as long as you do not give the constant the same name as another range in the workbook. Defined constants do not show up in the workbook, but they are defined using the Define Name dialog box just like a range (Figure 4). In the Refers to box, type the equal sign (=) and then the numeric value of the constant. You can then use the name of the constant in formulas. Now you can use crate to refer to the commission rate in formulas.
Figure 4: Defining a constant using the Define Name dialog box
Most worksheets use labels to identify rows and columns for the reader’s convenience. For example, in this worksheet there is a row named “Joanna” and a column named “Sales.” Excel can use these labels in a couple of ways to identify cell and range contents. To identify Joanna’s sales, we can enter the formula =Sales Joanna (or =Joanna Sales, Excel doesn’t care as long as there is a space between the words). In fact, if we had two columns named “Sales,” one under “Current” and one under “Prior,” we could identify Joanna’s sales as =Current Sales Joanna and as =Prior Sales Joanna. These are called “stacked labels.”
It is important to note that by default Excel does not recognize labels in formulas. To use labels in formulas, choose Tools, Options from the menu bar and then select the Calculation tab. Under Workbook Options, check Accept labels in formulas. Labels will then be available for use in formulas on the worksheet where the labels appear. When enabled, labels work like relative cell references.
In the example, the formula that will calculate Joanna’s commission is now simply =(Sales Joanna)*Rate (Figure 5).
Figure 5: The formula that will calculate Joanna’s commission
Alternatively, you could have entered =(Sales Joanna)*crate to use the constant. Since (Sales Joanna) is a relative cell reference, you can copy the formula down the Commiss column and it will update automatically (Figure 6).
Figure 6: Copying the formula down the Commiss column
A Range of Many Parts
So far you have only identified or named individual cells. A range, however, can consist of many cells. These cells may be contiguous (all in the same rows, columns, or rectangular area) or scattered. They can even be on different worksheets, as long as the cells or ranges to be included are in the same place on all the worksheets. There are several ways to select and name ranges.
In the example, it would be useful to have a named range for each distributor that would include the money paid to them — the commission and the draw. One way to accomplish this is to highlight the cells in the range and then call up the Define Name dialog box (Figure 7). Enter the name you want to give the range and then click OK.
Figure 7: Naming a range
If you need to define a named range that includes cells on multiple worksheets, you will have to use the Define Name dialog box. Hold down the Ctrl key and press F3 to open the dialog box. Type the name of the range in the Names in workbook box. Delete the cell reference in the Refers to box. Click the tab for the first worksheet to be referenced. Then hold down Shift and click the tab for the last worksheet to be referenced. Finally, select the cell or cells to be included by clicking them in the visible worksheet (drag the dialog box out of the way if necessary). Then click OK. You now can use the new 3-D range in formulas simply by entering its name.
Another way to name a range is to highlight the cells in the range and then enter the new designation in the name box (Figure 8). This only works if all the cells in the range are on the same worksheet. If the cells are not contiguous, hold down the Ctrl key as you individually click the cells. When you have entered the name in the Name box, simply press Enter to complete the designation.
Figure 8: An alternate way to name a range
When your multiple-cell ranges are named, you can use them in formulas as you used the named single cells. To summarize the amount paid to Joanna, the formula entered in cell F6 is now simply =SUM(Paid_Joanna). This is much easier to understand than =SUM(D6:E6).
The only drawback is that Excel treats named ranges as absolute references. If you copy the formula for Joanna down to cell F7 for Terrence, you will just see Joanna’s compensation total in F7. The formula does not update. On the other hand, the named range Paid_Joanna is available to any formula on any worksheet, which is a more flexible arrangement than references using labels.
Stupid Range Tricks
You can also use the Formula Palette to create formulas using named ranges (Figure 9). (See the article “Becoming an Excel 2000 Formula Artiste: How to Use the Formula Palette and Paste Function” for details if you are not familiar with the Formula Palette.) You can type the range name directly into the argument box or you can click and drag across the range and Excel will enter the range name for you.
Figure 9: Using the Formula Palette to create formulas using named ranges
Labels can be used to identify ranges of cells without naming them. In Figure 10, you can see that simply entering the label appearing at the top of a column is enough for Excel to understand what is intended. Remember that you must change the default settings in order for Excel to be able to use label names this way.
Figure 10: Using labels to identify ranges of cells without naming them
Named ranges can help you navigate a worksheet as well, especially a large one. Suppose you named the cell containing the total current sales “Current_Sales.” To go to this cell from any place on the worksheet, hold down the Ctrl key while pressing G (or choose Edit, Go To from the menu bar). This will open the Go To dialog box, where you may choose your destination from a list of all named locations in the sheet (Figure 11).
Figure 11: Using named ranges to navigate a worksheet
Finally, suppose you want to document all of the names and constants you have set up in your workbook. You can have Excel put a list of all of them in your worksheet, together with their locations or values. Click in a cell that is outside the main work area of your spreadsheet so there is plenty of room. Choose Insert, Name, Paste from the menu to open the Paste Name dialog box. You can then click the Paste List button or choose the names you want to have listed and then click the button (Figure 12).
Figure 12: Opening the Paste Name dialog box
Excel then pastes the list in two columns into your worksheet (Figure 13)
Figure 13: Documenting the names and constants in the workbook
Labels and named ranges are a powerful pair of tools for the worksheet designer. In large, complex worksheets, thoughtful use of these tools can make it much easier to maintain and update workbooks instead of having to simply rebuild them. Enjoy!