Computer Tips

  All Tips:

EXCEL TIP - Simplify your formulas with named constants

Problem:
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:

Solution:

1. Press Control + 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.

This particular tip was written by Mary Ann Richardson as found in TechRepublic's free Microsoft Office tips. View TechRepublic's tip.