By default, Excel automatically recalculates all formulas when you open a worksheet or when you change values, formulas, or names in dependent formulas. The recalculation process can take from a few seconds to several minutes, depending on the number and complexity of the formulas in the workbook.
While the formulas are being recalculated, the mouse pointer changes to an hourglass, and you cannot make any changes to the worksheet. To save time, you can temporarily turn off the mode automatic calculations in Excel until data entry is complete. This can be done without much difficulty, and we will show you how.
If you don’t want to disable automatic calculation mode and your computer has multiple processors, then you can apply the mode multithreaded computing. It will help speed up the recalculation process a little, depending on the number of computer processors. How to enable multi-threaded calculation mode in Excel, we will tell further.
- To turn off automatic calculation mode, open Excel and select the tab Fillet (File).
- Select item Options (Options).
- In the dialog box Excel Options (Excel Options) select tab Formulas (Formulas) located in the menu on the left.
- To disable data recalculation whenever values, formulas, names are changed, or when an Excel sheet containing formulas is opened, select the option Manual (Manually) in the section Calculation options (Computation options). Then click OK.
Calculation Options
The following list explains the options that are available under Calculation options (calculation parameters):
- automatically (Automatic) – Recalculates all dependent formulas and updates all open or embedded charts whenever the value, formula, or name changes. This setting is set by default for every new Excel worksheet.
- Automatic except for data tables (Automatic except data tables) – Recalculates all dependent formulas and updates all open or embedded charts except data tables. To recalculate data tables when this option is selected, use the command Calculate Now (Recalculation) located on the tab Formulas (Formulas) or key F9.
- Manual (Manual) – recalculates open worksheets and updates open or embedded charts only when a command is pressed Calculate Now (Recount) or keys F9, as well as when using the key combination Ctrl + F9 (only for the active sheet).
- Recalculate workbook before saving (Recalculate workbook before saving) – recalculates open worksheets and updates open or embedded charts when they are saved, even if the option is enabled Manual (Manually). If you don’t want dependent formulas and charts to be recalculated every time you save, just disable this option.
- Enable iterative calculation (Enable iterative calculations) – enables iterative calculations, i.e. allows you to set the limit number of iterations and the relative error of calculations when formulas will be recalculated when selecting a parameter or when using cyclic references. More detailed information about the selection of parameters and the use of circular references can be found in the Microsoft Excel Help.
- Maximum Iterations (Iteration limit) – defines the maximum number of iterations (default is 100).
- Maximum Change (Relative error) – sets the maximum allowable difference between the recalculation results (default is 0.001).
You can also switch between the three main calculation modes using the command Calculation Options (Computation Options) under Calculation (Calculation) tab Formulas (Formulas). However, if you need to configure the calculation parameters, you still have to turn to the tab Formulas (Formulas) Dialog Box Excel Options (Excel Options).
Multithreaded calculations in Excel
Excel 2013 has a multi-threaded calculation mode that allows you to reduce the time it takes to recalculate formulas. If you prefer not to disable automatic calculation, try using this option (if your computer has multiple processors) to reduce the recalculation time.
- To enable multi-threaded computing mode, click on the tab Fillet (File) and select Options (Options) to open the dialog box Excel Options (Excel Options). Then press Advanced (Additionally).
- Drop down to the section Formulas (Formulas) and check the box next to Enable multi-threaded calculation (Enable multi-threaded calculations). You can manually set the number of compute threads you want to use, or you can tell Excel to use all of the computer’s processors by selecting Use all processors on this computer (Use all processors of this computer).
If other programs are already running on your computer, you can limit the number of processors used to recalculate Excel spreadsheet formulas.
Try different settings for automatic and multi-threaded calculation modes to determine which one works best for your computer.