Microsoft Office Tutorials and References

In Depth Information

Figure 2-3:
Excel's Formula AutoCorrect feature often suggests a correction to an erroneous formula.

Don’t hard-code values

When you create a formula, think twice before using a literal value in the formula. For example, if your formula

calculates a 7.5 percent sales tax, you may be tempted to enter a formula such as

=A1*.075

A better approach is to insert the sales tax rate into a cell and use the cell reference in place of the literal value.

This makes it easier to modify and maintain your worksheet. For example, if the sales tax range changes to 7.75

percent, you need to modify every formula that uses the old value. If the tax rate is stored in a cell, you simply

change one cell, and all the formulas recalculate using the new value.

Calculating Formulas

You've probably noticed that the formulas in your worksheet get calculated immediately. If you change any

cells that the formula uses, the formula displays a new result with no effort on your part. This occurs when Ex-

cel's Calculation mode is set to Automatic. In this mode (the default mode), Excel follows certain rules when

calculating your worksheet:

• When you make a change (enter or edit data or formulas, for example), Excel calculates immediately those

formulas that depend on new or edited data.

• If working on a lengthy calculation, Excel temporarily suspends calculation when you need to perform other

worksheet tasks; it resumes when you finish.

• Formulas are evaluated in a natural sequence. For instance, if a formula in cell D12 depends on the result of

a formula in cell F12, cell F12 is calculated before D12.

Sometimes, however, you may want to control when Excel calculates formulas. For example, if you create a

worksheet with thousands of complex formulas, you may find that things can slow to a snail's pace while Excel

does its thing. In this case, you can set Excel's Calculation mode to Manual. Do this by choosing Formu-

las
⇒
Calculation
⇒
Calculation Options
⇒
Manual.

When you work in manual Calculation mode, Excel displays Calculate in the status bar when you have any un-

calculated formulas.

The Formulas
⇒
Calculation group contains two controls that, when clicked, perform a calculation: Calculate

Now and Calculate Sheet. In addition to these controls, you can use click the Calculate word on the status bar,

or use the following shortcut keys to recalculate the formulas: