Microsoft Office Tutorials and References
In Depth Information
Tip 32: Using Named Constants
The preceding steps create a named formula that doesn’t use any cell references. To try it out, enter
the following formula into any cell:
This simple formula returns .075, the result of the formula named SalesTax. Because this named
formula always returns the same result, you can think of it as a named constant. And you can use this
constant in a more complex formula, such as this one:
A named 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 MSFT:
Then you can use a cell formula, such as this one:
=”Annual Report: “&MSFT
This formula returns the text Annual Report: Microsoft Corporation .
Names that don’t refer to ranges don’t 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, however, appear in the Paste Name dialog box
(which appears when you press F3) and in the intellisense drop-down list when you’re
creating a formula. This does make sense because you use these names in formulas.
As you might expect, you can change the value of the constant at any time by using the Name
Manager dialog box (choose Formulas➜Defined Names➜Name Manager). Just click the Edit button
to open the Edit Name dialog box. Then change the value in the Refers To field. When you close the
dialog box, Excel uses the new value to recalculate the formulas that use this name.