Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces.
Boldface type is used to identify the names of icons, agendas and URLs.
Sans serif type indicates commands and instructions that users should type into the computer and the names of files. | 
hich of these two spreadsheet formulas would you more easily remember and would be less likely to cause typing lapses?.
=Sales-Expenses
or
=R3-T9 | The first is a hands-down choice because it’s composed of word descriptions (Sales-Expenses) rather than letter-number codes. So if you want spreadsheet formulas that are easy to create and read, follow along with this tutorial to learn how to use a naming system called “named ranges.” I invite you to open a blank Excel worksheet and work along with me. Begin by creating a worksheet with a few sample names. Exhibit 1 , at right, is a spreadsheet illustrating a typical net income computation. The categories are in column A and the data in column B: Revenue is B1, Expense is B2, Pretax Earnings is B3, Income Tax is B4 and Net Income is B5. But instead of just identifying them in column A, let’s actually rename B1 through B5 so we can identify the data by name. | | | Caveat: Excel protocol makes it easier to specify one-word names with no spaces. Thus, while it’s acceptable to use Pretax Earnings (two words) as the caption in cell A3, a cell that contains neither data nor a formula, B3 is better named PretaxEarnings or Pretax_Earnings . Excel even lends a hand in naming cells. For example, if you position your cursor in B2 and press Ctrl+F3 (or click on Insert , Name and Define ), you will evoke the Define Name screen (see exhibit 2 , at right). The screen contains two fields: Names in workbook and Refers to . Because you placed the cursor in B1, which is adjacent to A1, Excel automatically surmised the values in Sheet 1, B1 to be Revenue , and the user has only to click on OK to define the new name. | | | Notice that after clicking on OK , the Name Box , which is to the left of the Formula Bar , shows that the name of the highlighted cell, B1, is Revenue . The Name Box always gives the name of the highlighted cell or range of cells (see exhibit 3 , at right). If the name of the adjacent cell is made up of two words, such as Pretax Earnings in cell A3, Excel will automatically place an underline (_) between Pretax and Earnings—thus Pretax_Earnings.
You also have the option of using the Name Box to create a new name for a cell. For example, position the cursor in B2, click on the Name Box , type Expense and press the Enter key, and the cell is renamed. Now, using either method, fill in the names for B1 to B5. | | | Let’s use the new names in formulas. Position the cursor in B3 and type =Revenue–Expense and press Enter. Likewise, in B4 type =PretaxEarnings*30% and then hit Enter, and in B5 type =PretaxEarnings–IncomeTax and then press Enter. At this point your screen should resemble exhibit 4 , at right—with IncomeTax in the Name Box and =PretaxEarnings*30% in the Formula Bar . Names can refer to things other than cell ranges or formulas, such as percentages. For instance, you can create a name, such as TaxRate , and have it refer to 30% as a constant. To do so, press Ctrl+F3, type TaxRate in the Names in workbook box, and 30% (with no = sign) in the Refers to box, as shown in exhibit 5 , below. | | | Then change B4 to =PretaxEarnings*TaxRate . Although it takes a little more work initially to create names, it should be clear they make formulas easier to write and to read. This is especially true in large spreadsheets where you may have scores of references. ABSOLUTE vs. RELATIVE
Just like any other cell reference, a name may refer to a cell absolutely or relatively . To illustrate some other ways to use names, let’s create a new spreadsheet (see exhibit 6 , below). The highlighted range, $B$2:$D$6, contains the sales figures for each region for each month. We’ll name that range Sales . Now we can easily calculate total sales by entering the formula =SUM(Sales) in E7. The Sales range is absolute, meaning it always refers to $B$2:$D$6.
| | | Creating month and region subtotals requires the use of relative references. Let’s start with month subtotals. Highlight column C by clicking on the column header, then press Ctrl+F3 and type Month in the Names in workbook box. In the Refers to box, Excel has conveniently inserted the reference =Sheet1!$C:$C , which is an absolute reference to the highlighted column, but in this case we want a relative reference that can refer to any month, not just February. To change the formula in the Refers to box, press F2 and use the back arrow and Delete key to remove the two dollar signs. Your screen now should look like exhibit 7 , below. | | | Click on OK to accept the name and return to the spreadsheet. In a similar way, define the name Region to refer to the current row. Click on one of the row headers—it doesn’t matter which region you choose—and press Ctrl+F3 and create the name Region to refer to the current row using relative coordinates (no dollar signs); then click on OK . Note that although Sales was defined absolutely, both Month and Region were defined relatively. THE INTERSECTION OPERATOR
Now comes the payoff from the work we’ve done. Position the cursor in B7, enter the formula =SUM(Month Sales) and then copy the formula to C7 and D7. (Notice we typed Month and Sales as two words; we’ll explain that later.) Your spreadsheet now should look like exhibit 8 , below. | | Let’s review what we just did. The range name Month refers to the current column relatively. If the current cell is in column B, Month refers to column B. If the current cell is in column C, Month refers to column C. | The expression Month Sales refers to the intersection of the current column, Month , with the absolute range Sales . Notice in the Formula Bar the space between the words Month and Sales . That space is significant. Excel considers a space between two ranges as an operator that returns the range of cells which is the intersection of the two ranges. In January, Month is the relative range B:B, Sales is the absolute range $B$2:$D$6 and their intersection is the range $B$2:$B$6 . Thus, in cell B7 the formula =SUM(Month Sales) calculates the sum of the range $B$2:$B$6 , or 2194. | | | When you copy the formula to C7 and D7, the absolute range referred to by Sales remains the same, $B$2:$D$6 , but the relative range referred to by Month changes, always referring to the current column. Thus, Month in C7 refers to C:C , and Month Sales in C7 refers to the intersection of C:C and $B$2:$D$6 , which is $C$2:$C$6 . Likewise, Month Sales in D7 refers to the intersection of D:D and $B$2:$D$6 , which is $D$2:$D$6 . In each case the formula computes the sum of the sales amounts in the current column.
Computing row sums is similar. Enter =SUM(Region Sales) in E2 and copy the formula to E3:E7 (see exhibit 9 , at right). | | | It’s even possible to mix absolute and relative references in a single name. To do that add a year-to-date total in row 8. Type the caption YTD in cell A8, position the cursor anywhere in column B and press Ctrl+F3 to access the Define Names dialog box. Type YTD in the Names in workbook field and edit the Refers to field to read =Sheet1!$B:B (see exhibit 10 , at right). Note that the first B is preceded by a dollar sign, so it is absolute, but the second B is not, so it is relative; thus, wherever YTD is used, it will refer to the range of columns from column B to the current column. Now enter the formula =SUM(YTD Sales) in B8 and copy it to C8 and D8. Your completed spreadsheet should look like exhibit 11, below. | | | Let’s review. Using the names Sales (absolute), Region (relative), Month (relative) and YTD (mixed absolute/relative) we have written formulas using the intersection operator to select subranges of those names and create various totals. There are only four different formulas: =SUM(Region Sales) , =SUM(Month Sales) , =SUM(YTD Sales) and =SUM(Sales) . Each is easier to read than the corresponding formula without names. For instance =SUM(YTD Sales) corresponds to =SUM($B2:D6) in March. STYLE GUIDE
Although each spreadsheet author develops his or her personal named-range style, there are some guidelines we all can follow. | | 
Consistency. The name for the rent expense caption can be written many different ways, including RENT , rent_expense and RentExp . Although the particular style doesn’t matter, pick one and use it consistently so there is no question what it represents.
Brevity. Some formulas are long and complex, and using long names makes the situation worse. While it’s OK to use Exp for Expense or PY2 for SecondPriorYear , don’t abbreviate too much. For example, A could mean many things, only one of which is Actual .
Use of smart names. Try to think of names you won’t have to change each year. For example, use CyAct (for Current Year Actual) instead of Act2003 . But don’t make names so similar one can be confused with another.
Specificity. In a spreadsheet that has both GrossSales and NetSales , neither should be named Sales . And if you do happen to specify Sales in a formula, Excel fortunately will recognize the ambiguity and respond with this error message: #NAME? .
Pronounceability. Many people understand difficult or complex formulas by saying them aloud or by sounding out the words in their minds. I prefer CapEx to CPX or CapitalExpenditures and TaxRate to IncTaxRat (“ink tacks rat” evokes a strange image). When in doubt apply the “telephone test”—if you can’t sensibly read your formula to a colleague over the phone, you should rewrite it. Make named ranges a habit. Don’t skip them just because a spreadsheet is small, simple or because you think you don’t have enough time. Once you’re familiar with them, you’ll find they always save you time in the long run. PHILIP L. BEWIG, CPA, lives in St. Louis. His e-mail address is pbewig@swbell.net |
