Simplify your Excel formulas with named constants

by Mary Ann Richardson | Nov 29, 2005 5:38:00 PM

Tags: Microsoft Office, Mary Ann Richardson, Microsoft Excel, Microsoft Office Suite Excel Tips Newsletter

Takeaway: If use a constant in your Microsoft Excel workbook formulas, such as sales tax or car mileage allowance, then check out how using a named constant can save yourself considerable time.

Do you use a constant in your Microsoft Excel workbook formulas, such as sales tax or car mileage allowance? If so, you know that if the value changes, you have to manually make the change where it appears in every single formula in your workbook.

You can save yourself considerable time searching for and updating those formulas by creating a named constant. For example, suppose employees are reimbursed $0.37 per mile for car travel. To create a named constant for all formulas that calculate mileage allowance, follow these steps:

1. Press [Ctrl][F3].
2. Enter MileageAllowance in the Names In Workbook text box.
3. In the Refers To text box, enter the following formula:

=.37

4. Click Add and then click OK.

Now you can use the named constant in a formula. For example, if the number of miles for car travel is entered in B10, the formula for Mileage Reimbursement would be: =B10*MileageAllowance.

If the allowance changes to $0.40 per mile, you would follow the following steps to change the constant where it appears in every formula in your workbook:

1. Press [Ctrl][F3].
2. Select MileageAllowance.
3. In the Refers To text box, enter the following formula:

=.4

4. Click Add and then click OK.

You'll be amazed by how much time named constants will save you with your workbook formulas.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

This is great !! bschaettle@... | 11/30/05

I agree AtCollege | 11/30/05

One Caveat JamieM | 11/30/05

Best of Both Worlds? SmartLinkRAF | 11/30/05

...but that's still a cell reference bschaettle@... | 12/01/05

You're right ... SmartLinkRAF | 12/01/05