Microsoft Office Tutorials and References

In Depth Information

**Using Names**

When you display the Name Manager after creating the named formula, the Refers To column

displays a formula that is relative to the current active cell. For example, if cell D32 is the active

cell, the Refers To column displays

=Sheet1!B32^Sheet1!C32

Notice that Excel qualifies the cell references by adding the worksheet name to the cell

references used in your formula. This, of course, will cause the named formula to produce incorrect

results if you use it on a worksheet other than the one in which it was defined. If you’d like to use

this named formula on a sheet other than Sheet1, you need to remove the sheet references from

the formula (but keep the exclamation points). For example:

=!A1^!B1

After you understand the concept, you might discover some new uses for named formulas. One

distinct advantage is apparent if you need to modify the formula. You can just change the

formula one time rather than edit each occurrence of the formula.

The companion CD-ROM contains a workbook with several examples of named

formulas. The workbook is called
named formulas.xlsx
.

When you’re working in the New Name dialog box, the Refers To field is normally in

“point mode,” which makes it easy to enter a range reference by clicking in the

worksheet. Press F2 to toggle between point mode and normal editing mode, which allows

you to use the arrow keys to edit the formula.

The secret to understanding cell and range

names

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

throughout this chapter. Actually, this terminology isn’t quite accurate.

Here’s the secret to understanding names:

When you create a name for a cell or a range in Excel, you’re actually creating a named formula
—

a formula that doesn’t exist in a cell. Rather, these named formulas exist in Excel’s memory.

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

Name field contains the formula’s name. You’ll find that the contents of the Refers To field

always begin with an equal sign — which makes it a formula.

This isn’t exactly an earthshaking revelation, but keeping this “secret” in mind could help you

understand what’s going on behind the scenes when you create and use names in your workbooks.