Excel 2019 Bible

Chapter 9: Introducing Formulas and Functions

9

Using names for constants

Many Excel users don’t realize that you can give a name to an item that doesn’t appear in

a cell. For example, if formulas in your worksheet use a sales tax rate, you would probably

insert the tax rate value into a cell and use this cell reference in your formulas. To make

things easier, you would probably also name this cell something similar to SalesTax.

Here’s how to provide a name for a value that doesn’t appear in a cell:

1. Choose Formulas ➪ Defined Names ➪ Define Name. The New Name dialog box
   appears.


2. Enter the name (in this case, SalesTax) into the Name field.


3. Select a scope in which the name will be valid (either the entire workbook or a
   specific worksheet).


4. Click the Refers To text box, delete its contents, and replace the old contents
   with a value (such as .075).


5. (Optional) Use the Comment box to provide a comment about the name.


6. Click OK to close the New Name dialog box and create the name.

You just created a name that refers to a constant rather than a cell or range. Now if you

type =SalesTax into a cell that’s within the scope of the name, this simple formula

returns 0.075—the constant that you defined. You can also use this constant in a formula,

such as =A1*SalesTax.

A constant also can be text. For example, you can define a constant for your company’s name.

Named constants don’t appear in the Name box or in the Go To dialog box. This makes sense because these con-

stants don’t reside anywhere tangible. They do appear in the drop-down list that’s displayed when you enter a for-

mula. This is handy because you use these names in formulas.

Using names for formulas

In addition to creating named constants, you can create named formulas. Like a named

constant, a named formula doesn’t reside in a cell.

You create named formulas the same way that you create named constants—by using the

New Name dialog box. For example, you might create a named formula that calculates the

monthly interest rate from an annual rate; Figure 9.13 shows an example. In this case, the

name MonthlyRate refers to the following formula:

=Sheet3!$B$1/12