Page 353 - Excel 2013 All-in-One For Dummies

Controlling Formula Recalculation 335
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 recal- culated 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 hand- held 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. (See Book VII, Chapter 1 for more on performing what-if analyses.)
Automatic, Automatic Except Data Tables, and Manual are by no means the only calculation options available in Excel. Table 1-3 explains each of the options that appear in the Calculation Options section of the Formulas tab of the Excel Options dialog box.
Book III Chapter 1
Building Basic Formulas