Microsoft Office Tutorials and References

In Depth Information

**Working with iterative calculation options and automatic workbook calculation**

Working with iterative calculation options

and automatic workbook calculation

Excel formulas use values in other cells to calculate their results. If you create a formula

that refers to the cell that contains the formula, you have created a circular reference.

Under most circumstances, Excel treats circular references as a mistake for two reasons.

First, the vast majority of Excel formulas don’t refer to their own cell, so a circular reference

is unusual enough to be identified as an error. The second, more serious consideration

is that a formula with a circular reference can slow down your workbook. Because Excel

repeats, or iterates, the calculation, you need to set limits on how many times the program

repeats the operation.

You can control your workbook’s calculation options by clicking the File tab on the ribbon,

clicking Options to open the Excel Options dialog box, clicking Formulas to display that

page of the Excel Options dialog box, and selecting the calculation option you want.

The Calculation Options section of the Formulas page in the dialog box has three available

settings:

▪
Automatic
The default setting, which recalculates a worksheet whenever a value

affecting a formula changes

▪
Automatic except for data tables
Recalculates a worksheet whenever a value

changes but doesn’t recalculate data tables