# 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**.