Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

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:

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

    The New Name dialog box appears.

  2. Type the name into the Name text box.

    For example, you could type tax_rate to specify a tax rate.

  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. Change the scope to a particular worksheet only 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 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.
    You can assign a range name to a constant value in Excel 2007.
  5. 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.

About This Article

This article is from the book:

About the book author:

Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, Greg went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience: the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.

This article can be found in the category: