Chapter 9: Introducing Formulas and Functions
9
A3 refers to cell A3. Every time the formula in A3 is calculated, it must be calculated again
because A3 has changed. The calculation could go on forever.When you get the circular reference message after entering a formula, Excel gives you two
options:■ (^) Click OK to enter the formula as is.
■ Click Help to see a help screen about circular references.
Regardless of which option you choose, Excel displays a message on the left side of the sta-
tus bar to remind you that a circular reference exists.
Excel won’t tell you about a circular reference if the Enable Iterative Calculation setting is in effect. You can check
this setting in the Formulas section of the Excel Options dialog box. If Enable Iterative Calculation is turned on, Excel
performs the circular calculation exactly the number of times specified in the Maximum Iterations field (or until the
value changes by less than 0.001 or whatever value is in the Maximum Change field). In a few situations, you may use
a circular reference intentionally. In these cases, the Enable Iterative Calculation setting must be on. However, it’s
best to keep this setting turned off so that you’re warned of circular references. Usually, a circular reference indicates
an error that you must correct.
Often, a circular reference is quite obvious and easy to identify and correct. But when a
circular reference is indirect (as when a formula refers to another formula that refers to yet
another formula that refers to the original formula), it may require a bit of detective work
to get to the problem.
Specifying when formulas are calculated
You’ve probably noticed that Excel calculates the formulas in your worksheet immediately.
If you change any cells that the formula uses, Excel displays the formula’s new result
with no effort on your part. All of this happens when Excel’s Calculation mode is set to
Automatic. In Automatic Calculation mode (which is the default mode), Excel follows these
rules when it calculates your worksheet:
■ (^) When you make a change—enter or edit data or formulas, for example—Excel cal-
culates immediately those formulas that depend on new or edited data.
■ (^) If Excel is in the middle of a lengthy calculation, it temporarily suspends the cal-
culation when you need to perform other worksheet tasks; it resumes calculating
when you’re finished with your other worksheet tasks.
■ (^) Formulas are evaluated in a natural sequence. In other words, if a formula in cell
D12 depends on the result of a formula in cell D24, Excel calculates cell D24 before
calculating cell 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 pro-
cessing can slow to a snail’s pace while Excel does its thing. In such a case, set Excel’s