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