Excel formulas stop updating when calculation is Manual, a circular reference exists, or the entry is text instead of a real formula.
Stuck with totals that won’t change, a cell that shows “=SUM(…)” instead of a number, or results that lag behind your edits? This guide walks you through fast checks and reliable fixes. You’ll find the likely cause in minutes, with clear steps and two handy tables you can skim and act on right away.
Excel Formulas Not Updating: Fast Checks That Work
Most stalls trace back to three common issues: workbook calculation set to Manual, an entry stored as plain text, or a circular reference. Start with the quick table below, then dive into the sections that follow.
Quick Causes And Fixes
| Cause | How To Check | Fix |
|---|---|---|
| Workbook set to Manual | Numbers don’t refresh after edits | Formulas > Calculation Options > Automatic; press F9 to refresh all |
| Plain text instead of a formula | Cell shows “=SUM(…)” as text | Remove leading apostrophe; retype with “=” at the start |
| Numbers stored as text | Left-aligned numbers; green error tag | Convert to Number from error menu or Data > Text to Columns > Finish |
| Show Formulas mode | All cells display expressions | Press Ctrl+` or Formulas > Show Formulas |
| Circular reference | Status bar hint; warning on open | Fix the loop or enable iterative calculation with sane limits |
| Wrong cell or range | Totals skip rows or include blanks | Check references; use structured references for Excel Tables |
| External links stale | Values freeze until links update | Data > Edit Links > Update Values or break links if not needed |
| Calculation scoped per sheet | Only one sheet lags | Activate that sheet, press Shift+F9 |
| Volatile formulas heavy | Slow or partial recalc | Limit NOW/TODAY/INDIRECT/OFFSET; switch to stable patterns |
Set The Workbook Back To Automatic
Excel inherits the calculation mode from the first file opened in the session. If that file was saved in Manual, everything you open afterward follows suit. Switch it back: go to Formulas > Calculation Options > Automatic. Kick a full refresh with F9. If only the active sheet needs a refresh, press Shift+F9. For a deeper reference on modes and shortcuts, see Microsoft’s calculation mode guide.
Stop Manual Mode From Sneaking Back
Close all workbooks, then open a clean file first and set it to Automatic. Keep that file as your first file of the day. If a legacy template flips your session to Manual, fix the template and resave it so the setting stays put.
Confirm You’re Using A Real Formula
Formulas only work when the entry starts with an equals sign. If a cell shows text like “=A1+A2” but won’t evaluate, there’s a good chance it’s plain text. Remove any leading apostrophe, then retype beginning with “=”.
Convert Numbers Stored As Text
Imports, copy-pastes, or CSVs often bring in “numbers” as text. Clues include left alignment and a small green tag. Select the cells, open the error menu, and pick Convert to Number. Another quick path is Data > Text to Columns > Finish. Microsoft covers more methods here: fix numbers stored as text.
Remove Hidden Apostrophes
A leading apostrophe forces text. It’s visible in the formula bar but not in the grid. Use Find & Replace with ' in Find what and leave Replace with empty, limited to the selection, when safe. Test a small range first.
Turn Off “Show Formulas” View
If every cell displays expressions instead of results, the sheet is in Show Formulas view. Toggle it with Ctrl+` (the grave accent key) or via Formulas > Show Formulas. See Microsoft’s page on showing formulas for more tips.
Fix Circular References The Right Way
A circular reference is a formula that loops back to its own cell, directly or through a chain of cells. Excel warns you and may pause evaluation. The best fix is to break the loop by splitting the logic across helper cells or adjusting the dependency.
Find And Remove The Loop
Go to Formulas > Error Checking > Circular References to jump to the offending cell. Trace arrows with Formulas > Trace Precedents/Dependents. Microsoft’s step-by-step is here: remove or allow a circular reference.
When You Intend The Loop
Some models need controlled feedback (goal seeking, running totals that depend on prior results, loan schedules with interest on carrying balances). In those cases, turn on iterative calculation in File > Options > Formulas, set a small Maximum Iterations and a tight Maximum Change, and document the choice. Keep in mind that the web version doesn’t run iterative logic like the desktop app.
Check References, Ranges, And Table Logic
Totals that miss rows or include blanks usually point to range issues. If you’ve inserted rows between data and a plain range, the formula might not follow. Convert the data to an Excel Table (Ctrl+T) and switch to structured references; your totals will expand with the data.
Named Ranges And Sheets
Names can drift when sheets are copied, hidden, or deleted. Open Formulas > Name Manager to spot broken or mis-scoped names. Fix the Refers To range, then test the formula again.
External Links
If a formula pulls from another workbook, the value might freeze until links refresh. Go to Data > Edit Links and click Update Values. If a linked file is retired, replace the link with a local value or a query to a maintained source.
Spot Features That Slow Or Block Recalc
Heavy, volatile functions (OFFSET, INDIRECT, NOW, TODAY, RAND, RANDBETWEEN) recalc often. Large 3-D references or array formulas across big ranges can lag as well. Trim the footprint: limit whole-column references, cache expensive results in helper cells, or switch to non-volatile patterns like INDEX with pre-set ranges.
Precision, Iteration, And Data Tables
Three options influence behavior: Set precision as displayed, Enable iterative calculation, and Automatic Except Data Tables. Each has trade-offs. Microsoft outlines these choices in its recalculation and iteration settings. Pick the lightest setting that gets your model across the line.
Common Error Messages And What They Mean
When a cell shows an error, the code points you to the fix. Use this table as a quick decoder, then apply the repair.
| Message | What It Means | What To Do |
|---|---|---|
| #VALUE! | Wrong data type in an argument | Convert text numbers; check concatenations and math inputs |
| #NAME? | Misspelled function or bad name | Fix typos; verify named ranges; confirm add-ins |
| #REF! | Broken reference to a deleted range | Rebuild the range or restore the sheet/columns |
| #N/A | Lookup didn’t find a match | Check keys; trim spaces; consider IFNA |
| #DIV/0! | Divide by zero or blank | Wrap with IF or IFERROR; handle blanks |
| #NUM! | Overflow or invalid numeric input | Bound the inputs; review iterative settings |
Step-By-Step: Triage That Solves Most Cases
- Set Automatic mode: Formulas > Calculation Options > Automatic. Press F9.
- Toggle out of Show Formulas: press Ctrl+`.
- Confirm a real formula: retype with “=” and remove any leading apostrophe.
- Fix numbers as text: use the error menu or Text to Columns quick finish.
- Hunt circular references: use Error Checking > Circular References; break the loop or enable iteration with limits.
- Review ranges and links: switch data to an Excel Table, update external links, and repair names.
- Calm heavy formulas: reduce volatile functions, trim whole-column references, and cache results.
Pro Tips To Keep Calculations Healthy
Open A “Good” File First
The first workbook sets the session’s calculation mode. Keep a blank workbook saved in Automatic and open it first each day. If a legacy file flips your session to Manual, close all, open your “good” file, then reopen the legacy file.
Design For Growth
Convert data ranges to Tables so new rows and columns flow through totals. Prefer INDEX with fixed bounds over OFFSET or whole columns. Keep lookups on trimmed keys; remove stray spaces with TRIM or CLEAN in staging columns.
Document Choices That Affect Math
If you enable iteration or precision settings, leave a short note on a cover sheet. Mention the limits you set and why. That way, the next editor understands the model’s rules.
FAQ-Style Quick Hits (No Fluff—Just Fixes)
Pressing F9 Doesn’t Update Anything
Check whether calculation is Manual and the active sheet is protected. If the sheet is protected, some functions can’t update certain cells. Unprotect, refresh, then reapply protection if needed.
My Sum Ignores New Rows
Switch the data to an Excel Table and use the total row or a structured reference. Plain ranges don’t always expand after insertions.
Only One Sheet Is Frozen
Activate that sheet and press Shift+F9. If it updates, the workbook is fine; the sheet needed a local recalc.
Web Version Behaves Differently
The web app omits some desktop features, including certain iterative behaviors. If your model relies on iteration or advanced add-ins, run it in the desktop app.
Resources For Deeper Detail
Two official pages worth bookmarking:
- Microsoft’s overview of recalculation and iteration settings.
- How to handle circular references in a workbook.
Copy-Ready Checklist
- Switch to Automatic; press F9.
- Toggle off Show Formulas (Ctrl+`).
- Retype entries as real formulas; strip apostrophes.
- Convert text numbers to real numbers.
- Fix or permit circular logic with iteration limits.
- Stabilize references; prefer Tables and structured references.
- Trim volatile functions and whole-column ranges.