Let’s dive right into the most common cause of formulas not updating:
Calculation options set to ‘Manual calculation’ mode
Could it really be that simple?
In Excel, it is actually possible to change the calculation setting.
You can check and set the current calculation mode like this:
1. Click the Formulas tab.
2. Click on Calculation Options.
3. Verify that the calculation setting is Automatic.
4. Formulas will not recalculate automatically if Excel is set to Manual calculation mode.
In the practice Excel workbook, the formula in cell C2 is a simple addition formula:
=A2 + B2
You can change the values of A2 & B2 as you wish…
…but the formula result will not change while the setting is still in Manual calc mode.
5. To get the correct result, set the Calculation Options to Automatic calculation mode.
Now you can go back to working with Excel as usual!
You can also change the calculation mode by going into File > More… > Options > Formulas tab.
There are four calculation modes to choose from:
- Automatic – All dependent formulas in the entire workbook are recalculated as cell values change.
- Automatic Except for Data Tables – Same as Automatic. But Data Tables are only recalculated if a cell inside the table has changed
- Manual – The entire workbook is only recalculated if you press F9. Or if you click Calculate Now or Calculate Sheet in the Formulas ribbon
- Manual / Recalculate before saving – Same as Manual calc mode. But formulas are also recalculated every time you save the file.
But why did my Calculation Mode change? 🤔
Bear in mind that the calculation setting is an application-level setting.
If you change the Calculation Mode, this applies to the entire workbook and all other open workbooks.
Also, Excel uses the last saved calculation mode of the first workbook opened. All workbooks opened in the same session will use the same mode.
Try to open the practice Excel file without any other open workbooks.
You will notice that it opens in Manual calculation mode. This was the setting it was last saved with.
You can learn more about Calculation Mode behavior from the official Microsoft website.
Running a macro can also change the calculation mode
This is especially true if the macro or VBA developer used any of the lines below:
- Application.Calculation = xlCalculationAutomatic
- Application.Calculation = xlCalculationManual
If you are not working with a macro-enabled workbook, then you can definitely rule this out!
Why use ‘Manual calculation’ mode?
Well, if you are working with a large amount of data, you may notice a slight lag or delay in Excel. 🐌
This is usually caused by Excel automatically recalculating formulas with every change.
This can slow your work down quite a lot.
💡 So, you can change to Manual calculation mode as you enter or change data and switch back to Automatic later on.
Manual calculation shortcuts
In Manual mode, you can refresh formulas by pressing F9.
You can also click the Calculate Now or Calculate Sheet buttons in the Formulas ribbon.
This can help you save time and avoid the stress of waiting for Excel to finish updating formulas!