Microsoft Office Tutorials and References
In Depth Information
The Secret to Understanding Names
Naming text constants
In the preceding example, the constant consisted of a numeric value. A constant can also consist
of text. For example, you can define a constant for a company’s name. You can use the New
Name dialog box to create the following formula named MS:
Then you can use a cell formula such as
=”Annual Report: “&MS
This formula returns the text, Annual Report: Microsoft Corporation.
Names that do not refer to ranges do not appear in the Name box or in the Go To
dialog box (which appears when you press F5). This makes sense because these constants
don’t reside anywhere tangible. They do appear in the Paste Names dialog box and in
the Formula AutoComplete drop-down list, however, which does make sense because
you’ll use these names in formulas.
As you might expect, you can change the value of the constant at any time by accessing the
Name Manager dialog box and simply changing the formula in the Refers To field. When you
close the dialog box, Excel uses the new value to recalculate the formulas that use this name.
Although this technique is useful in many situations, changing the value takes some time. Having
a constant located in a cell makes it much easier to modify.
Using worksheet functions in named formulas
Figure 3-18 shows another example of a named formula. In this case, the formula is named
ThisMonth, and the actual formula is