Your Excel file worked perfectly yesterday, but when you entered new data today, the totals didn’t update. Why did the formulas suddenly stop calculating, and how can you fix them? Here’s the most common reason for that problem, and a quick way to fix it. Also, see how to avoid that problem in the future.
For example, here’s a simple SUM formula to add up 7 numbers. Number 3 was changed from 10 to 5, but the total stayed at 70.
What happened to the formula? Why did it stop working?
Why Formulas Stop Calculating
In most cases, formulas stop calculating because the Excel calculation mode has switched to Manual, instead of Automatic.
To see which calculation option is active:
- At the top of Excel, click the Formulas tab
- At the far right, click on Calculation Options
- The active setting has a check mark – Manual, in the screen shot below
To change the calculation mode, click on one of the calculation options
- Automatic
- Automatic Except for Data Tables
- Manual
Why Did Calculation Switch to Manual?
How did the Excel calculation mode get switched to Manual in the first place? You always us the Automatic setting, and nobody else shares this workbook with you.
As my favourite television detective, Monk, would say, “Here’s what happened.”
- You started Excel
- The first workbook you opened had a Manual calculation setting.
- Maybe a co-worker sent that file to you, and they like to work in Manual mode.
- A few minutes later, you opened your other workbook, which you always use in Automatic mode
- Excel ignored its Automatic setting, so you were still in Manual mode, without realizing it
And here’s the reason that Excel ignored your workbook’s calculation setting:
- The Excel calculation mode is an application-level setting
- All open workbooks have the same calculation mode
- When you start a new Excel session, Excel takes its calculation setting from the first workbook you open
- If you open more workbooks later, during the same session, Excel ignores their calculation settings
Video: Manual or Automatic Calculation
To see how a workbook can change to a different calculation mode, without you noticing, watch this short video.
Avoid the Manual Calculation Problem
To avoid creating calculation mode problems:
- If you switch to Manual calculations during an Excel session, switch back to Automatic before closing and saving a workbook
To avoid inheriting calculation mode problems:
- After you open the first workbook in an Excel session, check the calculation mode
- If it’s not the calculation mode that you need, change it, before opening any other files
Quick Way to Check Calculation Mode
If you need to check calculation mode frequently, add the Automatic and Manual settings to your Quick Access Toolbar, or to a custom tab in the Excel Ribbon. There are details on the Excel Calculation Options page, on my Contextures site.
Here are 2 benefits from adding the Automatic and Manual commands to the Ribbon or QAT:
- you can quickly see the current calculation mode
- you can easily change the current calculation mode
_____________________________
Excel Workbook Formulas Not Calculating: How to Fix Them
__________________________