How to turn Excel's calculation mode Off / On. - Tutorial

How to turn Excel's calculation mode Off / On with the Ribbon.

Microsoft Excel has the capability for a user to control its workbook calculation mode. A user will set this mode to manual disabling all calculations when a workbook takes a long time to calculate when new values are entered. Generally this time lag occurs when you have thousands of complex formulas in a workbook or if it has large array formulas.

To set the calculation mode to manual, proceed to the Ribbon, select the Formulas tab and then find the Calculation grouping on the tab. Click on the Calculation Options button and select Manual. To turn it back on, select Automatic. Please note that this feature effects all open workbooks.

How to calculate a workbook / worksheet when the calculation mode is set to manual.

Once manual calculation mode is engaged, Excel will only recalculate its workbooks when you tell it to. You can recalculate all workbooks by pressing the F9 function key or you can click the Calculate button in the status bar at the bottom left-hand corner of the Excel screen. Note that Shift+F9 can be pressed to only calculate the worksheet that you are on.

You can also press the Calculate Now and Calculate Sheet buttons in Ribbon pictured above to perform the same calculations. By the way, looking at this button when a workbook first opens or is being used is a great way to know instantly if the calculation mode has been set to manual. The Calculate button in the Status bar only appears when the workbook needs to be calculated.

Also if you set the calculation mode to manual and save your workbook, that setting will be stored with the workbook so when the file is reopened, Excel reengages manual calculation mode if it is opened first before any other workbooks. This feature can vary depending upon what is open so always check it.

Be careful saving workbooks in this mode because many Excel users are not used to seeing the calculation mode disabled and become confused when first encountering it. Also look on the Excel Options dialog box (File / Options) on the Formulas tab for the settings just discussed. There is also a check box on that tab that controls recalculating the workbook before saving if it is in manual calculation mode.