Perform calculations on data
- By Curtis Frye and Joan Lambert
- 1/29/2022
- Name data ranges
- Create formulas to calculate values
- Summarize data that meets specific conditions
- Copy and move formulas
- Create array formulas
- Find and correct errors in calculations
- Configure automatic and iterative calculation options
- Skills review
- Practice tasks
Configure automatic and iterative calculation options
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, the result is a circular reference.
Under most circumstances, Excel treats a circular reference as a mistake for two reasons. First, most 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 must set limits on how many times the app repeats the operation.
You can control how often Excel recalculates formulas. Three calculation options are available from the Formulas tab and from the Formulas page of the Excel Options dialog.
)
The calculation options work as follows:
Automatic recalculates a worksheet whenever a value that affects a formula changes. This is the default setting.
Automatic Except for Data Tables recalculates a worksheet whenever a value changes but doesn’t recalculate data tables.
Manual recalculates formulas only when you tell Excel to do so.
You can also use options in the Calculation Options section to allow or disallow iterative calculations (repeating calculations of formulas that contain circular references). The default values (a maximum of 100 iterations and a maximum change per iteration of 0.001) are appropriate for all but the most unusual circumstances.
To manually recalculate the active workbook
On the Formulas tab, in the Calculation group, select Calculate Now.
Press F9.
To manually recalculate the active worksheet
In the Calculation group, select the Calculate Sheet button.
To set worksheet calculation options
Display the worksheet whose calculation options you want to set.
On the Formulas tab, in the Calculation group, select Calculation Options, and then select Automatic, Automatic Except for Data Tables, or Manual.
To enable iterative calculations
Open the Excel Options dialog and display the Formulas page.
In the Calculation options section, select the Enable iterative calculation checkbox.
In the Maximum Iterations box, enter the maximum iterations allowed for a calculation.
In the Maximum Change box, enter the maximum change allowed for each iteration.
Select OK.