Change formula recalculation, iteration, or precision

Excel calculates formulas and then displays the results as values in the cells that contain the formulas. Typically, Excel automatically recalculates all open workbooks when the value in a cell changes. However, you can control when calculation occurs, the number of times a calculation iterates (repeats), and the precision of numbers.

Do any of the following:

Change when Excel calculates

While Excel is calculating, you can continue to choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to perform the other commands or actions and then resumes calculation.

Note: The calculation process can take longer if the workbook contains many formulas, or if the sheets contain data tables or functions that automatically recalculate. Also, the calculation process can take longer if the sheets contain links to other sheets or workbooks. You can set Excel to only calculate formulas when you tell it to.

  1. On the Excel menu, click Preferences.

  2. Under Formulas and Lists, click Calculation   Calculation Preferences button .

  3. Under Calculate sheets, select Manually, and then click OK.

  4. Do any of the following:

To manually calculate

Press

All sheets in all open workbooks

COMMAND + =

Only the active sheet

COMMAND + SHIFT + =

Change the number of times Excel calculates, or iterates, a formula

You can set the maximum number of iterations and the maximum amount that a calculation can change between iterations. When iteration is turned on, calculation will continue until one of those boundaries is met.

  1. On the Excel menu, click Preferences.

  2. Under Formulas and Lists, click Calculation   Calculation Preferences button , and then under Iteration, select the Limit iteration check box.

  3. In the Maximum iterations box, type the number of iterations that you want to set as the maximum number of times Excel recalculates.

    The higher the number of iterations, the more time Excel needs to calculate a sheet.

  4. In the Maximum change box, type the amount that you want to set as the maximum amount of change between calculation results.

    The smaller the number, the more accurate the result and the more time Excel needs to calculate a sheet.

    Note: Excel can't automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula it is calculating. This is known as a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate. For more information, see Remove or allow a circular reference.

Change the precision of all numbers in a workbook

By default, Excel stores and calculates numbers with 15 significant digits of precision regardless of the cell format. For example, if two cells each contain the number 10.005, but the cells are formatted to display numbers as currency, then $10.01 is displayed in each cell because Excel rounds up. If you add the two cells together:

Original numbers in the two cells

10.005

Original numbers, formatted as currency

$10.01

Original numbers added together

$20.01

The result is $20.01 because Excel adds the precise numbers 10.005 and 10.005, not the formatted numbers $10.01 and $10.01.

However, if you format numbers as currency and then change the precision of numbers in a workbook to match the cell format, then the precise number 10.005 is permanently rounded up to 10.01 because the currency cell format displays only two digits of precision.

Original numbers

10.005

Original numbers, formatted as currency

$10.01

Original numbers after precision is set to match display

$10.01

Original numbers added together

$20.02

Now the original number is permanently rounded. This can be useful on workbooks like simple balance sheets.

Caution: This procedure permanently changes the numbers on all sheets in the workbook, and the original, precise numbers cannot be restored.

  1. On the Excel menu, click Preferences.

  2. Under Formulas and Lists, click Calculation   Calculation Preferences button .

  3. Under Workbook options, select the Set precision as displayed check box.

See also

Calculation operators and order of operations

Round a number

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×