Register now or log in to join your professional community.
When working on large spreadsheets in Excel 2013, particularly on a slow computer, the calculation process can take a long time to complete. An easy way to "cut the wait time" is to change the calculation mode in Excel. There are different types of calculation available, why an Excel user might want to change from one mode to another and some of the pitfalls to watch out for with manual calculation.
Types of Calculation Available in Excel
There are three types of calculation available in Excel. The default setting is "Automatic." In this mode, all formulas in the spreadsheet recalculate whenever any data is changed.
In small files, the time lag for recalculation is unnoticeable. For large files with many data tables, the time lag can feel enormous. For this reason, the "Automatic except for data tables" option can be useful. Data tables suck up system resources. Multiple data tables in a spreadsheet can take a few seconds each to refresh with every change made to the spreadsheet.
The "Manual" calculation holds all updates until you are ready for them. The shortcut key for updating the entire file is "F9," also called "Calculate Now." The downside of this option is calculation updates can be forgotten and incorrect data displayed.
Using the Ribbon to Change Calculation Modes
The fastest place to change the calculation mode is on Excel's Formula ribbon. In the Calculation grouping, on the right side of the ribbon, is a drop-down button for "Calculation Options." Choose an option that works best for the current file. There are two other important buttons in the Calculation grouping. "Calculate Now" updates the entire workbook. "Calculate Worksheet" updates only the current sheet, which is much faster in a large file.
Using the Menu to Change Calculation Options
For those who prefer using their menu to make the change, calculation modes are located in the Excel Options dialog box on the formula tab. This can be found in the File menu.
Microsoft considers the option under Manual calculation to "Recalculate workbook before saving" the fourth calculation mode, but it is unavailable from the Ribbon. Keeping the box checked is invaluable for those times when the Manual setting is forgotten.
For those who prefer to use the keyboard, "Alt-F" opens the File Menu, then "T" chooses Excel Options. Click the down arrow to navigate to Formulas in the dialog box. Finally, choose a calculation mode: "Alt-A" for Automatic, "Alt-D" for Automatic with Data Tables or "Alt-M" for Manual calculation.
Important Considerations about Calculation Modes
It is vital to remember that a change to the calculation mode affects all open files, regardless of how they were originally saved. Templates are the only exception to this; they hold the mode of calculation they were created with, until they are saved again. For example, if "Sheet 1" of File A is active but File B is also open, when the mode of calculation is changed in File A, it affects every sheet in both files. For this reason, if you plan to change the calculation mode, close any unnecessary files first.
Agree with expert ansewrs above
only press F9 as long as the automatic calculation in inactive
