Naming constants in Excel 2007
Certain formulas use constant values, such as a 7.5% tax rate or a 10% discount rate. If you don't want to have to enter these constants into a cell of the worksheet in order to use the formulas, you create range names that hold their values and then use their range names in the formulas you create.
For example, to create a constant called tax_rate of 7.5%, you follow these steps:
1) Click the Define Name button on the Ribbon’s Formulas tab or press Alt+MMD to open the New Name dialog box.
2) In the New Name dialog box, type the range name (tax_rate in this example) into the Name text box.
Be sure to adhere to the cell range naming conventions when entering this new name.
3) (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. Only change the scope to a particular worksheet when you're sure that you’ll use it only in formulas on that worksheet.
4) Click in the Refers To text box after the equal to sign (=) and replace (enter) the current cell address with the constant value (7.5% in this example) or a formula that calculates the constant.
5) Click OK to close the New Name dialog box.
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 (or press Alt+MS) 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 (Alt+MN) 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.
Related Articles