Home | Business News | Browse by Publication | J | Journal of Accountancy

In Excel, cell names spell speed, safety: give a cell a name, and your work will go faster and be more error-free.

Article Excerpt
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 "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 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...