In Excel, manual calculation refers to a setting where Excel does not automatically recalculate formulas in your worksheet every time you make a change.
Instead, you must initiate the calculation process manually.
When you enable manual calculation in Excel, you can change data on your worksheet without triggering recalculation until you’re ready.
Benefits of Manual Calculation Mode in Excel
Manual calculation mode in Excel allows you to have greater control over the computation process. It can benefit you in various ways:
- Optimizes performance for large datasets by preventing unnecessary calculations.
- It enables you to calculate specific parts of a workbook, avoiding overwhelming your system resources.
- It provides more flexibility when working with complex models and functions.
For instance, when I work with sizeable financial models, switching to manual calculation mode helps me minimize lag and reduce errors.
Suppose you have a worksheet with several data tables in multiple worksheets, and you only need to update tables in one sheet.
In such cases, manual calculation allows you to recalculate that specific section without updating the entire workbook, saving you time and processing power.
You can even have more accurate outcomes by controlling the calculation sequence. When using automatic mode, some cells may not have all the necessary information for accurate calculations.
By switching to manual mode, you can ensure that each cell has the correct input data before initiating the calculation process.
How to Enable Manual Calculation Mode in Excel?
This tutorial shows three methods of enabling manual calculation in Excel.
Method #1: Use the Formulas Tab
You can enable manual calculation in Excel using the Formulas tab by using the below steps:
- On the Formulas tab, on the Calculation group, click the Calculation Options button on the Calculation group and choose Manual.
With the manual calculation enabled, Excel will not automatically recalculate formulas in the spreadsheet but only when you press F9 or click the Calculate Now button on the Calculation group of the Formulas tab.
Alternatively, if you prefer using the keyboard, you can enable manual calculation using the below shortcut:
Alt + M + X + M
Here is how to use this shortcut:
- Press the Alt key to activate the keyboard shortcuts for the Ribbon menu:
- Press the M key to open the Formulas tab.
- Press the X key to open the Calculation Options.
- Press the M key to choose the Manual calculation option.
The Manual calculation option is now enabled:
Add The Calculation Options Button to the Quick Access Toolbar (QAT)
If you want to enable manual calculation with just two clicks, you can add the Calculation Options button to the Quick Access Toolbar using the below steps:
- Click the Customize Quick Access Toolbar button at the end of the QAT on the top left of the Excel window.
- Select More Commands from the drop-down menu.
- On the Excel Options dialog box that appears, open the Choose commands from drop-down list and choose the All Commands option. Scroll down the list of commands until you find Calculation Options and select it.
- Click the Add button to add the Calculation Options button to the QAT.
- Click OK to close the Excel Options dialog box.
Now, the Calculation Options button appears on the QAT.
If you want to switch Excel to manual calculation mode, click the Calculation Options button and select Manual.
Method #2: Use the Excel Options Dialog Box
You can enable manual calculation using the Excel Options dialog box using the following steps:
- Click File to open the Excel Backstage window.
- Click Options on the left sidebar of the Excel Backstage window.
- On the Excel Options dialog box, click the Formulas category on the left, select the Manual calculation option on the right, and click OK.
After completing these steps, Excel will no longer calculate formulas automatically when you change data on your worksheet.
Therefore, you must manually calculate the formulas by pressing the F9 key or clicking the Calculate Now button on the Calculations group on the Formulas tab.
Method #3: Use Excel VBA
You can use Excel VBA to enable manual calculation in Excel by using the below steps:
- On the active worksheet, press Alt + F11 to open the Visual Basic Editor.
- Open the Insert menu and choose Module to insert a module.
- Copy the subroutine below and paste it into the module.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub EnableManualCalculation()
Application.Calculation = xlManual
End Sub
- Place the cursor anywhere in the subroutine and press F5 to run the code.
The code enables the manual calculation mode, and you can confirm this by clicking the Calculation Options button on the Calculation group of the Formulas tab. You will see that the Manual calculation option is selected:
Once you have created the macro, you can assign it to a shortcut key for easy access.
How to Assign the Macro to a Shortcut Key
You can use the below steps to assign the macro you have created to a shortcut key:
- On the Developer tab, click the Macros button on the Code group to open the Macro dialog box.
Alternatively, you can launch the Macro dialog box using the shortcut Alt + F8.
- Select the macro name on the Macro dialog box and click the Options button.
- On the Macro Options dialog box, on the Shortcut key box, type an uppercase or lowercase letter you want to use for the shortcut and press OK. In this example, we have typed in the uppercase letter “M” by pressing the Shift key and then the M key.
- Dismiss the Macro dialog box by clicking the Cancel button.
After these steps, you can enable manual calculation by pressing the shortcut Ctrl + Shift + M.
Drawbacks of Using Manual Calculation Mode in Excel
One drawback of using the manual calculation mode in Excel is that it can lead to errors if you forget to manually update your formulas.
This can result in outdated or incorrect data in your worksheets, impacting your analysis or decision-making process.
Here are other drawbacks of using manual calculation mode:
- Limited interactivity: Manual calculations can be frustrating for users collaborating on a shared spreadsheet, requiring them to constantly update calculations as they make changes.
- Risk of inconsistency: If multiple users are working on the same worksheet and forget to update calculations, inconsistent and inaccurate data can be shared or reported.
I remember working on a budget report with a team, and we didn’t realize the spreadsheet was set to manual calculations.
As a result, we spent hours trying to identify discrepancies before discovering the issue, which led to a lot of wasted time for the entire team.
Thus, it’s essential to weigh the benefits and drawbacks of using manual calculations in Excel before deciding if it’s the right approach for your specific use case.
Scope of the Manual Calculation Mode
Excel calculation mode is an application-level setting that applies to all the open workbooks in Excel.
So, for example, if you enable manual calculation mode in one workbook, all the other open Excel files are also set to manual calculation mode.
Note also that if you enable manual calculation in the first Excel file you open, all subsequent workbooks you open next will be set to manual calculation mode.
This tutorial showed three techniques for enabling manual calculation mode in Excel. The methods involve using the Formulas tab, the Excel Options dialog box, and Excel VBA.
The tutorial also explained that Excel calculation mode is an application-level setting that affects all open workbooks and the files that are opened later after the mode has been set.
We hope you found the tutorial helpful.
Other Excel articles you may also like:
