readers follow the instructions in this
article, we use two different typefaces.
Boldface type is used to
identify the names of icons, agendas and
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?.
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.
instead of just identifying them in
column A, let’s actually rename B1
through B5 so we can identify the data
|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 ,
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.
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). |
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
You also have the option of using the
Box to create a new
name for a cell. For example, position
the cursor in B2, click on the
Box , type Expense and
press the Enter key, and the cell is
Now, using either
method, fill in the names for B1 to B5.
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
=PretaxEarnings*30% in the
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.
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.
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
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.
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.
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.
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
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.
row sums is similar. Enter =SUM(Region
Sales) in E2 and copy the formula to
E3:E7 (see exhibit 9 , at
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
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.
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
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? .
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
PHILIP L. BEWIG, CPA, lives in St.
Louis. His e-mail address is