Microsoft Office Tutorials and References

In Depth Information

**The Secret to Understanding Names**

The Secret to Understanding Names

Excel users often refer to
named ranges
and
named cells.
In fact, I’ve used these terms frequently

throughout this chapter. Actually, this terminology is not quite accurate.

Here’s the secret to understanding names: When you create a name, you’re actually creating a

named formula. Unlike a normal formula, a named formula doesn’t exist in a cell. Rather, it exists

in Excel’s memory.

This is not exactly an earth-shaking revelation, but keeping this “secret” in mind will help you

understand the advanced naming techniques that follow.

When you work with the Name Manager dialog box, the Refers To field contains the formula, and

the Name field contains the formula’s name. The content of the Refers To field always begins

with an equal sign, which makes it a formula.

As you can see in Figure 3-16, the workbook contains a name
(InterestRate)
for cell B1 on Sheet1.

The Refers To field lists the following formula:

=Sheet1!$B$1

Figure 3-16:
Technically, the name
InterestRate
is a named formula, not a named cell.

Whenever you use the name
InterestRate,
Excel actually evaluates the formula with that name

and returns the result. For example, you might type this formula into a cell:

=InterestRate*1.05

When Excel evaluates this formula, it first evaluates the formula named
InterestRate
(which exists

only in memory, not in a cell). It then multiplies the result of this named formula by 1.05 and

displays the result. This cell formula, of course, is equivalent to the following formula, which uses the

actual cell reference instead of the name:

=Sheet1!$B$1*1.05