In Excel, Cell Names Spell Speed, Safety: Give a Cell a Name, and Your Work Will Go Faster and Be More Error-Free
Philip L. Bewig · Journal of accountancy online/Journal of accountancy · 2003
Which 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 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 In-come is B5. [ILLUSTRATION OMITTED] 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 oneword 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, page 68). [ILLUSTRATION OMITTED] 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). [ILLUSTRATION OMITTED] 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. [ILLUSTRATION OMITTED] 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, at right. [ILLUSTRATION OMITTED] 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, page 70). The highlighted range, SB$2:$D$6, contains the sales figures for each region for each month. [ILLUSTRATION OMITTED] We'll name that range Sales. …