Perform calculations on data

Set iterative calculation options and enable or disable automatic 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, the result is a circular reference.

Under most circumstances, Excel treats a circular reference 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 app repeats the operation.

You can control your workbook’s calculation options by using the controls on the Formulas page of the Excel Options dialog box.

Set iterative calculation options on the Formulas page of the Excel Options dialog box.

The Calculation Options section of the Excel Options dialog box has three available settings:

• Automatic The default setting; 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

• Manual Requires you to press F9 or, on the Formulas tab, in the Calculation group, click the Calculate Now button to recalculate your worksheet

You can also use options in the Calculation Options section to allow or disallow iterative calculations. If you select the Enable Iterative Calculation check box, Excel repeats calculations for cells that contain formulas with circular references. The default Maximum Iterations value of 100 and Maximum Change of 0.001 are appropriate for all but the most unusual circumstances.

To recalculate a workbook

1. Display the workbook you want to recalculate.

2. Press F9.

   Or

   On the Formulas tab, in the Calculation group, click Calculate Now.

To recalculate a worksheet

1. Display the worksheet you want to recalculate.

2. In the Calculation group, click the Calculate Sheet button.

To set worksheet calculation options

1. Display the worksheet whose calculation options you want to set.

2. In the Calculation group, click the Calculate Options button.

3. Click the calculation option you want in the list.

To set iterative calculation options

1. Display the Backstage view, and then click Options.

2. In the Excel Options dialog box, click Formulas.

3. In the Calculation options section, select or clear the Enable iterative calculation check box.

4. In the Maximum Iterations box, enter the maximum iterations allowed for a calculation.

5. In the Maximum Change box, enter the maximum change allowed for each iteration.

6. Click OK.