You change a number in your spreadsheet. You wait for the total to update. Nothing happens. The formula is still sitting there, looking completely normal, but the result is frozen like it belongs to a different version of the file.
Most people's first instinct is to assume they broke something. In almost every case, that's not what happened. Excel has a handful of settings that quietly control whether formulas recalculate at all, and once you know what they are, this stops being a mystery and becomes a two minute fix.
Quick Answer: What's usually going on?
About 80% of the time, the workbook's calculation setting has switched from Automatic to Manual, often without you doing anything. Go to the Formulas tab, click Calculation Options, and check that Automatic is selected. If that's already on and the formula still won't budge, work through the other eight causes below in order, starting with the easiest ones to rule out.
On this page
- 1. Calculation Mode Is Set to Manual
- 2. The Cell Is Formatted as Text
- 3. Show Formulas Is Turned On
- 4. A Circular Reference Is Blocking the Calculation
- 5. A Macro Switched Calculation Mode and Didn't Switch It Back
- 6. You Opened a File Someone Else Saved in Manual Mode
- 7. The Formula References the Wrong Cell or an Old Named Range
- 8. External Links or Data Connections Haven't Refreshed
- 9. Iterative Calculation Settings Are Fighting the Formula
- Quick Diagnostic Checklist
- How to Stop This From Happening Again
- Key Takeaways
- Frequently Asked Questions (FAQs)
1. Calculation Mode Is Set to Manual
This is the cause behind the vast majority of "my formulas stopped working" complaints. Excel has three calculation modes, and only one of them updates formulas the way you'd expect by default.
| Mode | What it does |
|---|---|
| Automatic | Recalculates every dependent formula in the workbook the moment a cell changes. This is the default, and the one you almost always want. |
| Automatic Except for Data Tables | Same as Automatic, but Data Tables only recalculate when you tell them to, which speeds things up in models that lean heavily on Data Tables. |
| Manual | Nothing recalculates until you press F9 or click Calculate Now. Formulas can sit there showing outdated numbers indefinitely. |
To check and fix this, go to the Formulas tab, click Calculation Options in the Calculation group, and select Automatic. If you'd rather not dig through menus every time, F9 recalculates the active sheet on the spot, and Ctrl+Alt+F9 forces a full recalculation of everything open, which is worth trying if a formula still looks stuck after switching modes.
2. The Cell Is Formatted as Text
When a cell is formatted as Text, Excel treats whatever you type into it as plain characters, including a formula. It won't calculate anything. It will just sit there showing =SUM(A1:A10) as literal text instead of a number.
To fix it, select the cell, go to Home, and change the format from Text to General or Number in the number format dropdown. Then click into the cell, press F2 to edit it, and hit Enter. Just changing the format alone usually isn't enough. Excel needs you to re-enter the formula before it will actually run it.
3. Show Formulas Is Turned On
If your whole sheet suddenly shows formulas instead of results, you probably hit Ctrl+` (the backtick key, usually above Tab) by accident. This toggles Show Formulas mode, which is meant for auditing a sheet, not for daily use.
Press Ctrl+` again to turn it off, or go to the Formulas tab and click Show Formulas to toggle it manually. This one isn't really a calculation problem at all. The formulas were working the whole time, you just couldn't see the results.
4. A Circular Reference Is Blocking the Calculation
A circular reference happens when a formula, directly or through a chain of other cells, ends up referring back to itself. Excel can't solve that kind of loop with a normal calculation, so depending on your settings it will either warn you or quietly return zero.
Go to Formulas, then Error Checking, then Circular References to see which cell is causing the loop. From there you have two options: restructure the formula so it doesn't reference itself, usually by adding a helper cell to break the chain, or if the circularity is intentional (some interest-expense and cash-balance models genuinely need it), turn on Iterative Calculation under Calculation Options and set a reasonable maximum number of iterations.
5. A Macro Switched Calculation Mode and Didn't Switch It Back
If a workbook uses VBA macros, one of them may include a line that sets calculation to Manual on purpose, usually to speed up the macro while it's running. A well-written macro switches calculation back to Automatic when it finishes. Not every macro does that, especially if it was interrupted or written a bit sloppily.
If you notice formulas freezing right after running a macro, that's your clue. Check Calculation Options again, and if you write or edit macros yourself, make sure any line that sets manual calculation has a matching line further down that sets it back to automatic.
6. You Opened a File Someone Else Saved in Manual Mode
This is really a variation of cause #1, but it deserves its own spot because it explains one of the most confusing versions of this problem: formulas work fine on your coworker's computer, but not on yours, even though you're both opening the exact same file.
The calculation mode saved inside a workbook can override your own Excel settings when you open it. If a colleague saved a large model in Manual mode to keep their computer from lagging, and then sent it to you, your session inherits that setting the moment you open the file. Everything else you open afterward can inherit it too, until you manually switch back to Automatic.
7. The Formula References the Wrong Cell or an Old Named Range
Sometimes a formula isn't frozen at all. It's calculating correctly, just from the wrong source. This tends to happen after rows or columns get inserted, deleted, or moved, and a formula that used to point to B5 now needs to point to B6 but doesn't.
Click into the formula and look at which cells are actually highlighted. If a named range is involved, check Formulas > Name Manager to confirm the range still points where you think it does. This is a formula-logic issue rather than a calculation-setting issue, so switching calculation mode won't fix it. Tracing the reference will.
8. External Links or Data Connections Haven't Refreshed
If your formula pulls from another workbook, a Power Query connection, or an external data source, the calculation itself might be running fine while the source data behind it is stale. Excel doesn't always refresh external links automatically, especially if you clicked "Don't Update" on a prompt at some point.
Go to Data > Queries & Connections and refresh manually, or go to Data > Edit Links if the formula references another workbook, and check the update setting there. This is easy to mistake for a calculation problem because the symptom looks identical: a number that just won't move.
9. Iterative Calculation Settings Are Fighting the Formula
If iterative calculation is turned on for a model that doesn't actually need it, or the maximum iterations or maximum change settings are too low, Excel can stop recalculating a formula before it reaches a stable answer. The result looks frozen, but what's really happening is that Excel gave up early.
Check Formulas > Calculation Options > Enable Iterative Calculation. If it's on and you don't have an intentional circular reference (see cause #4), turn it off. If you do need it, try raising the maximum iterations and lowering the maximum change threshold so Excel keeps working the problem until the numbers actually settle.
Quick Diagnostic Checklist
About 80% of cases resolve at the first box. Work left to right, top to bottom.
Work through these in order. Most people find their answer in the first two or three steps.
- Formulas > Calculation Options > is it set to Automatic?
- Press F9. Did anything change?
- Press Ctrl+` to check if Show Formulas is accidentally on.
- Select the problem cell. Is its number format set to Text?
- Formulas > Error Checking > Circular References. Any hits?
- Did you just run a macro? Check calculation mode again.
- Did this file come from someone else? Same question.
- Click into the formula. Is it referencing the cell you actually think it is?
- Data > Queries & Connections. Anything waiting to refresh?
How to Stop This From Happening Again
- Keep calculation mode on Automatic unless you're working in a genuinely huge model where Manual mode is needed for performance. If you do use Manual mode temporarily, set a personal reminder to switch it back before you close the file.
- Add a small helper cell somewhere out of the way with the formula
=NOW(). If that cell isn't updating either, you know instantly that the whole workbook is in Manual mode rather than chasing one specific formula. - Before sending a workbook to a colleague, double check your own calculation mode is set to Automatic, so you're not passing the problem along to them.
- If you're building a model with deliberate circularity, like an interest expense schedule tied to a revolving cash balance, document that clearly in the workbook so the next person doesn't "fix" it by breaking the loop.
If your formulas are working fine but you want to go further and build in AI-assisted forecasts on top of a clean, correctly calculating model, our guide on Copilot in Excel with Python walks through that next step. And if the formulas in question are part of a sensitivity or scenario table, see our full tutorial on sensitivity and scenario analysis in Excel for how Data Tables specifically behave under manual and automatic calculation.
Key Takeaways
- Manual calculation mode causes the large majority of "formulas won't update" problems. Check Formulas > Calculation Options first, every time.
- Calculation mode applies to your whole Excel session, not just one file, so a file from someone else can silently change your settings.
- Text-formatted cells and Show Formulas mode both look like calculation bugs but are really formatting or display settings.
- Circular references and iterative calculation settings can make a formula look frozen when it's actually stuck trying (and failing) to resolve.
- Not every stuck number is a calculation problem. Sometimes the formula is calculating correctly from the wrong cell or a stale data connection.
Frequently Asked Questions (FAQs)
Why did my Excel formulas suddenly stop updating?
The most common reason is that the workbook's calculation mode switched from Automatic to Manual. This can happen on its own if you open a file that someone else saved in Manual mode, or if a macro changed the setting and never changed it back. Go to Formulas, then Calculation Options, and check whether Automatic is selected.
How do I force Excel to recalculate everything?
Press F9 to recalculate the active sheet, or Ctrl+Alt+F9 to force a full recalculation of every open workbook, including cells Excel thinks haven't changed. This is useful when a formula looks stuck even though calculation mode is already set to Automatic.
Why does my formula show as text instead of a result?
The cell is probably formatted as Text rather than General or Number. Excel treats anything typed into a Text-formatted cell as literal characters, formula and all, so it never calculates. Change the cell format to General, then click into the cell, retype or re-enter the formula, and press Enter.
Can a circular reference stop a formula from updating?
Yes. If a formula refers back to its own cell, directly or through a chain of other cells, Excel can't resolve it and will either show a warning or return zero, depending on your settings. Go to Formulas, then Error Checking, then Circular References to find the loop, and either fix the logic or turn on iterative calculation if the circularity is intentional.
Why do formulas update fine on one computer but not another?
Excel's calculation mode is an application-level setting, not a file-level one, so it can carry over between workbooks on the same computer. If a colleague's file was saved in Manual mode and you open it, every other workbook you open afterward on that machine can inherit Manual mode too, until you switch it back.
Related Articles
External References
- Microsoft Support. "Change formula recalculation, iteration, or precision." support.microsoft.com
- Excel Campus. "How to Fix Excel Formulas that are Not Calculating or Updating." excelcampus.com
- Spreadsheeto. "How to Fix Excel Formulas Not Calculating (Refresh Formulas)." spreadsheeto.com
0 Comments