Assigning Names to Constant Values in Excel 2007

By Greg Harvey from Excel 2007 For Dummies

Some formulas that you create in Excel 2007 use constant values, such as a 7.5% tax rate or a 10% discount rate. Assign names to these values and then use their names in the formulas that you create. Then, you don’t have to enter these constants into a cell of the worksheet in order to use the formulas.

Follow these steps to assign a name to a constant value:

On the Formulas tab, click Define Name in the Defined Names group.

The New Name dialog box appears.
Type the name into the Name text box.

For example, you could type tax_rate to specify a tax rate.
(Optional) To have the range name defined for just the active worksheet instead of the entire workbook, click the name of the sheet on the Scope drop-down list.

Normally, you’re safer sticking with the default selection of Workbook in the Scope option so that you can use your constant in a formula on any of its sheets. Change the scope to a particular worksheet only when you’re sure that you’ll use it only in formulas on that worksheet.
Click in the Refers To text box after the equals sign (=) and replace the current cell address with the constant value or a formula that calculates the constant.

For example, you could type 7.5% (or .075, either will work) in the Refers To text box for a tax rate.

You can assign a range name to a constant value in Excel 2007.
Click OK.

After you assign a constant to a range name by using this method, you can apply it to the formulas that you create in the worksheet in one of two ways:

Type the range name to which you assign the constant at the place in the formula where its value is required.
Click the Use in Formula command button on the Formulas tab and then click the constant’s range name on the drop-down menu that appears.

When you copy a formula that uses a range name containing a constant, its values remain unchanged in all copies of the formula that you create with the Fill handle. (In other words, range names in formulas act like absolute cell addresses in copied formulas.)

Also, note that when you update the constant by changing its value in the Edit Name dialog box — opened by clicking the range name in the Name Manager dialog box and then clicking its Edit button — all of the formulas that use that constant (by referring to the range name) are automatically updated (recalculated) to reflect this change.