Microsoft Office Tutorials and References
In Depth Information
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
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:
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
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.