Excel calculations: automatic, manual, iterative

This tutorial details Excel's calculation settings, explaining how to configure automatic and manual recalculation of formulas. Efficient Excel formula use requires understanding its calculation mechanics, including formula basics, functions, operator precedence, and crucial background settings impacting calculation speed and accuracy.

Three core Excel calculation settings are:

• Calculation Mode: Determines whether formulas recalculate automatically or manually.
• Iteration: Controls the number of recalculations a formula undergoes before meeting a specific numerical condition.
• Precision: Defines the calculation accuracy level.

This tutorial explores each setting's function and modification.

Automatic vs. Manual Calculation (Calculation Mode)

These options govern when and how Excel recalculates formulas. By default, Excel automatically recalculates formulas when dependent values (referenced cells, values, or names) change. However, this behavior is customizable, even allowing calculation pausing.

Modifying Excel Calculation Options

Navigate to the Formulas tab > Calculation group on the Excel ribbon. Click the Calculation Options button and choose:

• Automatic (default): Recalculates all dependent formulas automatically whenever referenced values, formulas, or names change.
• Automatic Except for Data Tables: Automatically recalculates all formulas except data tables (distinct from Excel Tables).
• Manual: Disables automatic calculation; recalculation requires explicit action.

Alternatively, adjust settings via Excel Options:

• Excel 2010-365: File > Options > Formulas > Calculation options > Workbook Calculation.
• Excel 2007: Office button > Excel options > Formulas > Workbook Calculation.
• Excel 2003: Tools > Options > Calculation > Calculation.

Important Notes:

1. Selecting Manual calculation automatically enables the "Recalculate workbook before saving" option. Disable this for faster saving with many formulas.
2. If formulas stop calculating, ensure "Automatic" is selected in Calculation Options. Refer to troubleshooting steps if needed (link to troubleshooting steps omitted as it's not provided in the original text).

Forcing Recalculation in Excel

With manual calculation enabled, force recalculation using:

• Calculate Now: Recalculates all open worksheets and linked chart sheets (Formulas tab > Calculation group).

• Calculate Sheet: Recalculates the active worksheet and linked charts (Formulas tab > Calculation group).

• Keyboard Shortcuts:
  • F9: Recalculates changed formulas and dependents in all open workbooks.
  • Shift F9: Recalculates changed formulas in the active worksheet.
  • Ctrl Alt F9: Forces recalculation of all formulas in all open workbooks.
  • Ctrl Shift Alt F9: Checks formula dependencies and then recalculates all formulas.

Excel Iterative Calculation

Excel uses iteration (repeated calculation) for formulas referencing their own cells (circular references). By default, this is disabled to prevent infinite loops. Enable it by specifying the maximum number of recalculations.

Enabling and Controlling Iterative Calculation

Enable iterative calculation via:

• Excel 2010-2016: File > Options > Formulas > check "Enable iterative calculation".
• Excel 2007: Office button > Excel options > Formulas > Iteration area.
• Excel 2003 and earlier: Tools > Options > Calculation tab > Iterative Calculation.

Configure:

• Maximum Iterations: The maximum number of recalculations allowed.
• Maximum Change: The maximum acceptable difference between recalculated results.

Precision of Excel Calculations

Excel uses 15 significant digits by default. You can change this to use displayed values instead of stored values for recalculations (understand the implications before doing so). Displayed and stored values can differ (e.g., date formatting). Using displayed values permanently alters stored values, potentially affecting future calculations. Adjusting displayed decimal places via number formatting is often a safer alternative.

Setting Precision as Displayed

To use displayed precision:

1. File > Options > Advanced.
2. In "When calculating this workbook," select the workbook.
3. Check "Set precision as displayed".
4. Click OK.

This concludes the guide to configuring Excel's calculation settings.

The above is the detailed content of Excel calculations: automatic, manual, iterative. For more information, please follow other related articles on the PHP Chinese website!