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
▪ 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