When Excel won’t calculate formulas, set calculation to Automatic, clear Text formatting, and resolve circular references.
If a sheet stops updating, don’t panic. Excel usually has a simple reason: manual calculation, text-formatted cells, show-formulas view, or a circular reference. The steps below walk through fast checks first, then deeper fixes. No setup needed.
Quick Fix Checklist
The items below cover the issues most users hit.
| Symptom | Likely Cause | Where To Fix |
|---|---|---|
| Values don’t change after edits | Workbook set to Manual | Formulas › Calculation Options |
| Cells display the formula text | Show Formulas is on, or cells formatted as Text | Formulas › Show Formulas, or Home › Number group |
| Only some cells recalc | Sheet or selection not recalced | Shift+F9 for the sheet, F9 for all |
| Totals skip new rows | Range not including new data | Convert range to an Excel Table, then use structured refs |
| Formulas show as text | Leading apostrophe or space | Remove with Find & Replace |
| Status bar shows Circular References | Self-referencing formula chain | Formulas › Error Checking › Circular References |
Excel Not Calculating Formulas: Causes And Fixes
Workbook Is In Manual Calculation Mode
Manual mode tells Excel to hold updates until you recalc by hand. Switch back to Automatic:
- Open the workbook.
- Go to Formulas › Calculation Options › Automatic.
- Press F9 once to refresh every open workbook.
Microsoft’s guide on calculation settings explains the options and what F9 shortcuts do; see Change formula recalculation, iteration, or precision in Excel.
Calculation mode can also carry over from the last file you closed. If you save a workbook while set to Manual, a new workbook may open in Manual as well. Microsoft documents how Excel picks the current calculation mode here: How Excel determines the current mode of calculation.
Cells Are Formatted As Text
If a cell is stored as text, Excel treats the formula as plain characters. Fix it this way:
- Select the cells.
- Home › Number group › set the format to General or Number.
- Click into each cell and press Enter, or use Data › Text to Columns › Finish to re-enter everything at once.
Formula Starts With An Apostrophe Or Space
A leading apostrophe (‘) or space tells Excel to treat the entry as text. Edit the cell and remove it. For a whole column, use Find & Replace:
- Ctrl+H.
- Find what: ^’ (or a single space). Replace with: nothing.
- Match entire cell contents if needed.
Show Formulas Mode Is On
Press Ctrl+` (the key under Esc) to toggle formula view. Or Formulas tab › Show Formulas. When this view is on, every formula appears as text and columns widen.
Circular Reference Stops Updating
A formula that refers to itself, even through other cells, is a circular reference. Excel will show a status bar warning and may stop at a previous value. To track it down:
- Formulas tab › Error Checking › Circular References. Follow the link to each cell.
- Rewrite the logic so the loop disappears, or turn on iterative calculation if the loop is by design.
If you enable iteration, add caps for “Maximum Iterations” and “Maximum Change” so results settle predictably.
Volatile Functions Or Data Tables Delay Recalc
Functions like NOW(), RAND(), OFFSET(), INDIRECT(), and large tables can slow updates. Limit volatile functions where you can, or move heavy models to a separate sheet and recalc with F9 only when needed.
External Links Are Broken
If a formula points to a closed or moved workbook, Excel may hold the last known value. Check Data › Edit Links (or Queries & Connections) and update the source path. When possible, import the data to a table.
Mixed Separators Or Wrong Locale
If your Windows region uses commas for decimals, Excel expects semicolons between arguments. Files shared across regions can break. Set the correct regional format in Windows or rebuild the formulas with the proper separators.
Step-By-Step: Turn Calculation Back On
Follow this once and most stuck sheets begin to move again:
- Open any affected workbook.
- Formulas tab › Calculation Options.
- Choose Automatic.
- Save the workbook. Close every other workbook that was open in Manual.
- Reopen a new blank file and check that it now starts in Automatic.
Tip: To keep new files in Automatic, close Excel when a workbook with Automatic is the last one open.
Force A Fresh Recalculation
Keyboard shortcuts give you instant control:
- F9 – Recalculate all open workbooks.
- Shift+F9 – Recalculate the active worksheet.
- Ctrl+Alt+F9 – Rebuild dependencies and recalc all formulas.
- Ctrl+Alt+Shift+F9 – Full dependency rebuild, slower but thorough.
Fix Formulas That Show Text Instead Of Results
Here are reliable ways to turn text into working formulas or numbers:
Method A: Clear The Text Format
- Select the range. Set Number format to General.
- Double-click each formula cell or press F2 then Enter. For many cells, use Data › Text to Columns › Finish.
Method B: Convert Values By Math
- Enter 1 in an empty cell, copy it.
- Select the “text numbers”, right-click › Paste Special › Multiply.
- Delete the helper cell.
Method C: Strip Leading Apostrophes In Bulk
- Select the range.
- Ctrl+H to open Replace.
- Find what: ^’ and leave Replace with blank. Replace All.
Recalc Shortcuts At A Glance
| Shortcut | Scope | Use When |
|---|---|---|
| F9 | All workbooks | You changed inputs in many sheets |
| Shift+F9 | Active sheet | You changed a small section |
| Ctrl+Alt+F9 | All formulas, rebuild links | You suspect stale dependencies |
| Ctrl+Alt+Shift+F9 | Full rebuild | You changed names or table structure |
Prevent It Next Time
- Before saving shared models, set Calculation Options to Automatic.
- If a slow model needs Manual, add a bold banner cell at the top that says “Manual Calc is ON — press F9.”
- Avoid saving a template while in Manual mode. New workbooks inherit the last mode that was closed.
- Use Excel Tables for running totals so new rows join the formula automatically.
- Keep volatile functions to a minimum and replace OFFSET with INDEX where possible.
- Store external data in a table via Power Query so formulas depend on local data.
Troubleshooting Flow You Can Follow
- Press Ctrl+` once. If formulas appear, toggle back.
- Look at the status bar. If you see “Calculate” or “Circular References,” fix those first.
- Switch Calculation Options to Automatic and hit F9.
- Change any “Text” number formats to General, then re-enter.
- Try Shift+F9. If only the active sheet was stale, that will show it.
- Use Go To Special › Formulas to spot cells that still hold text or errors.
- Review named ranges, table names, and spilled ranges that may have shrunk or shifted.
- If nothing moves, press Ctrl+Alt+F9 once for a full rebuild.
Why Excel Opens New Files In Manual Mode
Excel picks its starting mode based on the last workbook you closed in that session. If the last file saved and closed was set to Manual, the next new file often starts in Manual. Close Excel with a workbook set to Automatic as the final file, and new files should start in Automatic again. This catches many people after a teammate shares a model that runs in Manual to speed up data entry.
Extra Causes That Keep Formulas Stuck
- Hidden characters: copied data may contain non-breaking spaces. Clean with CLEAN and TRIM, or run Text to Columns with delimiter None to force a fresh parse.
- Merged cells: formulas that spill (such as FILTER, SORT, UNIQUE) won’t spill into a merged area. Unmerge the target range.
- Protected sheets: formulas can’t change if cells are locked and the sheet is protected. Unprotect the sheet, make changes, then protect again.
- Data validation: an entry that fails validation can leave a cell untouched. Review Data › Data Validation rules.
- Array entry from older files: legacy CSE arrays don’t resize. Convert them to dynamic arrays where possible.
Power Tips For Reliable Models
- Convert key ranges to Tables (Ctrl+T) and use structured references like Table1[Amount]. Formulas adjust to added rows.
- Use LET to store repeated subexpressions, which makes formulas cleaner and cuts recalcs.
- Replace nested IFs with IFS or CHOOSECOLS/TAKE combos for clarity.
- For dates, use EDATE and EOMONTH instead of adding 30 or 31.
- When sharing, include a readme sheet with the calculation mode and the refresh steps at the top.
Common Error Messages And Quick Fixes
When formulas do run yet return an error, the message often points you in the right direction. Here’s what the most common ones mean and how to fix them fast:
- #DIV/0! — The formula divides by zero or a blank cell. Test the denominator first, for example with IF($B$2=0,””,A2/$B$2) or wrap the entire formula with IFERROR.
- #NAME? — Excel doesn’t recognize text in the formula. Check for misspelled function names, missing quotes around text, or names that were deleted.
- #VALUE! — A function got text where a number was expected. Convert text-numbers with VALUE, or fix the input cell. Look for stray spaces and non-printable characters.
- #REF! — A reference points to a cell that no longer exists, often after deleting rows or columns. Rebuild the range, and prefer Tables so references adjust when data moves.
- #NUM! — A calculation can’t produce a valid number. Check inputs for impossible math, like SQRT of a negative, very large exponent results, or an out-of-range rate in IRR/XIRR.
- #N/A — A lookup did not find a match. Confirm lookup keys, set exact match in XLOOKUP/VLOOKUP, or wrap with IFNA if a blank result is fine.
You now have a clear path: confirm calculation mode, fix text formats, toggle show-formulas, hunt down any circulars, and refresh with the right shortcut. With these steps, sheets that felt frozen start updating again without a rebuild right away.