2020-4-3 By default, Excel 2007 calculates your formulas automatically as they are entered or when you modify the worksheet. You can switch to manual calculation when necessary. Leaving the setting on automatic is usually not an issue, but if you’re working on a hefty workbook with lots of calculations, you may need to rethink this one. This is driving me a little bit crazy. In my old PC days I used to press either F5 or F9 (I can't remember) to have Excel recalculate. What is the Mac equivalent? PS: Perhaps this is not the place to ask. If so, please redirect me.
![Excel Excel](https://www.howtogeek.com/wp-content/uploads/2013/04/01_clicking_file_tab1.png)
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.
- Enter a formula that refers to values in other cells. In a sheet that contains columns of numbers, click the cell where you want the formula results to appear. Type an equal sign =. Click the first cell that you want to include in your calculation. Type an operator.
- If you accidentally replace a formula with a value and want to restore the formula, click Undo immediately after you enter or paste the value. Select the cell or range of cells that contains the formulas. Click the arrow next to Paste Options, and then click Values Only.
- The user can trigger recalculation in Microsoft Excel in several ways, for example: Entering new data (if Excel is in Automatic recalculation mode, described later in this topic). Explicitly instructing Excel to recalculate all or part of a workbook. Deleting or inserting a row or column.
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.
![Microsoft Excel On A Mac Recalculate Formulae Microsoft Excel On A Mac Recalculate Formulae](https://www.ablebits.com/_img/excel-count-sum-color/sum-count-pane-excel.png)
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).
Using Excel On A Mac
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.
Recalculate Style
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. |