Cells not updating automatically
Solution 1:
A likely cause is that Calculation is set to manual. To change this to automatic in the various versions of Excel:
2003: Tools > Options > Calculation > Calculation > Automatic.
2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
2010 and newer: File > Options > Formulas > Workbook Calculation > Automatic.
On Mac:
- 2008: Excel Preferences > Calculation > Automatically
In some cases you may prefer to keep it set to manual, for example if there are many heavy calculations to perform. In such cases, you can simply press F9 when you want the calculations to update.
Solution 2:
Confirm with Excel 2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
Short key to refresh
Ctrl+F9
Solution 3:
I had a case of this just now on Excel 2010: a particular spreadsheet that would not auto-recalculate. I changed the setting as indicated above; but the auto-recalculate still did not work, and upon rechecking the "Calculation" option, found it had reset itself back to "Manual" all by itself. Three attempts later and it was still adamant it wanted to be "Manual" and nothing else.
So here is my solution to the problem:
Copy the contents of the spreadsheet to a new one, and deleted the old one (it wasn't a particularly important spreadsheet, thankfully), and everything was fine. I can only assume that somehow the file had become corrupted.
A good reason to keep backups.
Solution 4:
Reposting this snippet that user RFB had (inappropriately) attempted to edit into my answer:
A possible cause is that the Office Prefs file is corrupted. In OSX this can be found in:
Library/Group containers/*.office/com.microsoft.officeprefs.plist.
Delete this file and restart the OS. A new plist file will be created when Office is restarted. Formulas recalculated again perfectly.
Solution 5:
It looks like it is a "known bug" going on at least since 2012, given google results: once you open one file where the "automatic calculation" flag is set to off, all subsequent workbooks you will open will e automatically set to "automatic calculation = off". So the turn-off of automatic calculation is...automatic.
It also happens sometimes that a file gets corrupted and prevents you from re-enabling automatic calculation.
Sometimes there also can be a macro, "hidden" inside a workbook, which programmatically turns off calculation in a specific situation, but there is no way you can know it... until you notice formulas results keep not changing!
Application.Calculation = xlManual
Application.Calculation = xlAutomatic
Sources:
https://answers.microsoft.com/en-us/office/forum/office_2003-excel/excel-sometimes-sneakily-changes-automatic/d9d29b5e-e09d-4fd6-8001-2efeaaf2a71a
https://answers.microsoft.com/en-us/msoffice/forum/all/the-calculation-option-keeps-switching-from-auto/2ed29346-b946-4aaf-9c19-83911eea812e
https://www.automateexcel.com/vba/on-off-calculations/