By default, Microsoft Excel operates in “Automatic Calculation” mode. This means that the exact millisecond you change a number in a single cell, Excel instantly recalculates every single formula, chart, and pivot table across the entire workbook that might be affected by that new number. For small spreadsheets, this happens so fast you don’t even notice it.
However, if you are working with a massive financial model containing hundreds of thousands of rows and complex VLOOKUPs, this automatic recalculation can completely freeze your computer. You might type a single number, press Enter, and then sit staring at an hourglass cursor for thirty seconds while the software chugs through the data. To regain control of your workflow, you can completely disable this automatic process and force Excel to only calculate when you specifically tell it to.
Disabling Automatic Calculation
You can toggle this setting directly from the main ribbon menu; you do not need to dive into the deep configuration options.
- Open the Microsoft Excel desktop application and load your massive workbook.
- Look at the main ribbon menu across the top of the screen and click on the Formulas tab.
- Look to the far right side of the Formulas ribbon for a group titled Calculation.
- Click on the button labelled Calculation Options (it has an icon of a small calculator).
- A dropdown menu will appear with three choices: Automatic, Automatic Except for Data Tables, and Manual.
- Click on Manual.
The change takes effect immediately. You can now rapidly type data into dozens of cells, and Excel will remain completely fluid and responsive because it is no longer processing the math in the background.
How to Manually Trigger a Calculation
Once you are in Manual mode, your formulas will not update. If you change the underlying data for a “Total Sum” cell, the total will still show the old number. When you have finished data entry and are actually ready to see the updated results, you must trigger the calculation yourself.
You can do this in two ways:
- Calculate the Entire Workbook: Press the F9 key on your keyboard. This forces Excel to recalculate every formula on every sheet.
- Calculate the Active Sheet Only: Press Shift + F9 on your keyboard. This forces Excel to only recalculate the specific worksheet you are currently looking at, which is much faster if you have a massive multi-tab document.