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
|
| HOME | ARCHIVE | CONTACT | ADVERTISE | SUBSCRIBE | AICPA 
