As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.
- Click the Microsoft Office Button
, click Excel Options, and then click the Formulas category.
- Do one of the following:
- To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.
- Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.
- To recalculate all dependent formulas—except data tables—every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic except for data tables.
- Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic Except for Data Tables.
- To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by clicking Calculate Now under Calculation Options in the Calculation group on the Formulas tab), in the Calculation options section, under Workbook Calculation, click Manual.
- Note When you click Manual, Excel automatically selects the Recalculate workbook before saving check box. If saving a workbook takes a long time, clearing Recalculate workbook before saving may improve the save time.
- Tip Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Manual.
- To manually recalculate all open worksheets, including data tables, and update all open chart sheets, on the Formulas tab, in the Calculation group, click the Calculate Now button.
Changing any of the options affects all open workbooks.
Note If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.