Normally, Excel 2013 recalculates your worksheet automatically as soon you change any entries, formulas, or names on which your formulas depend. This system works fine as long as the worksheet is not too large or doesn’t contain tables whose formulas depend on several values.
When Excel does calculate your worksheet, the program recalculates only those cells that are affected by the change that you’ve made. Nevertheless, in a complex worksheet that contains many formulas, recalculation may take several seconds (during which time, the pointer will change to an hourglass, and the word “Recalculation” followed by the number of cells left to be recalculated will appear on the left side of the Formula bar).
Because Excel recalculates dependent formulas in the background, you can always interrupt this process and make a cell entry or choose a command even when the pointer assumes the hourglass shape during the recalculation process. As soon as you stop making entries or selecting commands, Excel resumes recalculating the worksheet.
To control when Excel calculates your worksheet, you click the Calculation Options button on the Formulas tab of the Ribbon and then click the Manual option button or press Alt+MXM. After switching to manual recalculation, when you make a change in a value, formula, or name that would usually cause Excel to recalculate the worksheet, the program displays the message “Calculate” on the status bar.
When you’re ready to have Excel recalculate the worksheet, you then click the Calculate Now (F9) command button (the one with a picture of the handheld calculator) on the Ribbon’s FORMULAS tab or press F9 or Ctrl+=. This tells the program to recalculate all dependent formulas and open charts and makes the Calculate status indicator disappear from the status bar.
After switching to manual recalculation, Excel still automatically recalculates the worksheet whenever you save the file. When you are working with a really large and complex worksheet, recalculating the worksheet each time you want to save your changes can make this process quite time-consuming.
If you want to save the worksheet without first updating dependent formulas and charts, you need to deselect the Recalculate Workbook before Saving check box in the Calculation Options section of the Formulas tab of the Excel Options dialog box (File→Options→Formulas or Alt+FTF).
If your worksheet contains data tables used to perform what-if analyses, switch from Automatic to Automatic except Data Tables recalculation by choosing Automatic Except Data Tables from the Options button’s drop-down menu on the Formulas tab or pressing Alt+MXE. Doing so enables you to change a number of variables in the what-if formulas before having Excel recalculate the data table.
Automatic, Automatic Except Data Tables, and Manual are by no means the only calculation options available in Excel.
| Option | Purpose |
|---|---|
| Automatic | Calculates all dependent formulas and updates open or embedded charts every time you make a change to a value, formula, or name. This is the default setting for each new worksheet that you start. |
| Automatic Except for Data Tables | Calculates all dependent formulas and updates open or embedded charts. Does not calculate data tables created with the Data Table feature. To recalculate data tables when this option button is selected, click the Calculate Now (F9) command button on the Formulas tab of the Ribbon or press F9 in the worksheet. |
| Manual | Calculates open worksheets and updates open or embedded charts only when you click the Calculate Now (F9) command button on the Formulas tab of the Ribbon or press F9 or Ctrl+= in the worksheet. |
| Recalculate Workbook before Saving | When this check box is selected, Excel calculates open worksheets and updates open or embedded charts when you save them even when the Manually option button is selected. |
| Enable Iterative Calculation | When this check box is selected, Excel sets the iterations, that is, the number of times that a worksheet is recalculated, when performing goal seeking or resolving circular references to the number displayed in the Maximum Iterations text box. |
| Maximum Iterations | Sets the maximum number of iterations (100 by default) when the Iteration check box is selected. |
| Maximum Change | Sets the maximum amount of change to the values during each iteration (0.001 by default) when the Iteration check box is selected. |