Start with Excel calculation mode: if formulas update only after F9 or not at all, switch to Automatic; if a single cell still fails, also check for text formatting, a leading apostrophe, or broken references. Ignoring the symptom wastes time and can leave reports wrong even when the formula itself is correct. This guide maps each failure pattern to the most likely setting, format, or reference issue before you edit the formula.
Likely causes
Match the visible failure to the first thing to check. That is faster than rewriting a formula that may already be fine.
| Visible symptom | First check | Likely cause | Exact fix |
|---|---|---|---|
| Values stay stale until you press F9 | File > Options > Formulas | Automatic workbook calculation is off | Select Automatic under Workbook Calculation, then recalculate the workbook |
| The formula text appears in the cell | Cell format on the Home tab | Cell formatted as Text | Change format to General, then re-enter the formula with F2 and Enter |
| A formula begins with an apostrophe | Formula bar | Leading apostrophe forces text | Delete the apostrophe and press Enter |
| Copied formula breaks after paste | Separator used in the formula | Wrong list separators for the workbook locale | Use the separator Excel expects in that workbook, often comma or semicolon |
| One formula returns a reference error | Cell references in the formula | Broken or shifted reference | Edit the reference or restore the source cell or sheet |
| The sheet never settles on a final answer | Formula logic | Circular reference | Move the formula, break the self-reference, or rewrite the logic |
| Only one workbook is stale | Links to other files | External link is broken or unavailable | Update the link source or remove the reference to the other workbook |
| The cell shows the formula, not the result | Show Formulas state | Formula Auditing display mode is on | Press Ctrl + ` or clear Show Formulas in Formula Auditing |
| Many pasted cells behave as text | Range format | Text-stored formulas after paste | Use Text to Columns or change format to General, then re-enter |
| Results appear after manual recalc only | Calculation mode | Manual calculation mode | Switch to Automatic in File > Options > Formulas |
| #VALUE!, #REF!, #NUM, or #N/A appears | The formula result cell | Input or reference problem | Trace the bad value, bad address, or invalid argument |
| Only one sheet lags behind the rest | Sheet-specific references | Broken links, circular references, or pasted text in that sheet | Check that sheet’s links, formula display, and reference chain |
Suppose a budget sheet shows =SUM(B2:B12) as plain text after a paste from email. That points first to formatting, not the SUM logic. Change the cell to General, remove any apostrophe, and re-enter the formula before touching the range.
A common first-timer mistake is to rewrite a long nested formula when the workbook is simply in manual calculation mode. If values change only after F9, the formula may be correct and the calculation setting is the real problem.
Why won't my Excel formula calculate?
Most often, Excel is in manual calculation mode, which stops recalculation until you force it. Check File > Options > Formulas and confirm Automatic is selected under Workbook Calculation. If the setting is already automatic, look next for text formatting, broken links, or circular references.
Check calculation mode first
- Open File > Options > Formulas.
- Under Workbook Calculation, select Automatic.
- Click OK.
- Press F9 to recalculate the workbook.
If the workbook updates after that change, the formula logic did not need rewriting. The issue was the calculation setting.
Use a quick recalc check
- Press F9.
- If results update only now, keep Automatic on.
- If only one sheet remains stale, inspect that sheet for broken links or circular references.
Why is Excel showing my formula instead of the result?
That usually means Excel is treating the entry as text or showing formulas on purpose. Check whether the cell format is Text, whether a leading apostrophe sits before the equals sign, and whether Show Formulas is enabled. The fix is usually format, not formula logic.
Confirm the cell is not stored as text
- Select the cell or range.
- On the Home tab, set the number format to General.
- Press F2, then Enter to re-enter the formula.
General format plus F2 then Enter can convert a text-stored formula. That is the fastest repair when the formula itself is already correct.
Remove a leading apostrophe
- Click the cell and look in the formula bar.
- Delete any apostrophe before the equals sign.
- Press Enter.
Excel can treat an apostrophe as text, even if it appears right before a formula. That is a common reason a formula prints instead of calculates.
Turn off formula display mode
- Press Ctrl + `.
- Or go to Formulas > Formula Auditing > Show Formulas.
- Confirm the sheet now shows values instead of formulas.
Show Formulas is a display setting, not a calculation failure. It is easy to confuse the two when the worksheet suddenly fills with formula text.
Why do copied formulas break after paste?
Copied formulas often fail because the pasted version uses the wrong separator, shifts references in an unexpected direction, or lands in a range that was already text-formatted. Check the separators and references before changing the logic. If only pasted cells fail, this is usually a copy-and-format problem.
Match list separators to the workbook
- Open the pasted formula in the formula bar.
- Check whether arguments are separated with commas or semicolons.
- Replace the wrong delimiter with the one Excel expects in that workbook.
- Press Enter and recalculate.
Formulas may need comma separators in some workbooks. A copied formula that uses the wrong delimiter can fail before Excel even evaluates the math.
Check reference shifts after paste
- Compare the pasted formula with the original.
- Look for shifted row or column references.
- If a reference moved incorrectly, lock it with $ signs where needed.
- Paste again and verify the result.
Check for text-formatted paste targets
- Select the pasted range.
- Set the format to General.
- Re-enter one formula with F2 and Enter.
- If the whole range is text-stored, use Data > Text to Columns and finish with the default settings to apply a usable number format.
Text to Columns can fix many cells that were stored as text after paste. It is useful when the same problem repeats across a block of formulas.
Excel formula decision tree
Use the symptom first, then move down the list. This avoids mixing together display problems, calculation mode issues, and broken references.
- If the formula is visible in the cell, press Ctrl + ` first, then check for Text format or an apostrophe.
- If values are stale, go to File > Options > Formulas and confirm Automatic.
- If only pasted formulas fail, compare separators and reference shifts.
- If one workbook fails, check external links, circular references, and any sheet-specific references.
- If many cells were pasted as text, use Text to Columns or convert the format to General and re-enter.
Excel shows formulas in edit mode with highlighted problem areas, so the formula bar can help separate a display issue from a bad argument. That matters when the sheet shows an error such as #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, or #NULL!.
Picture a sales report where one total updates only after someone presses F9, while the neighboring cells work normally. That points first to calculation mode or a workbook-specific link issue, not to the SUM formula itself.
Still not working?
If the quick checks do not fix it, move to the more invasive causes. Rebuild the formula in a blank sheet, inspect external links, and review any circular references that point a formula back to its own cell. If the workbook depends on other files, confirm those files still exist and still open correctly.
Check external links and circular references
- Open Data > Edit Links if that command is available.
- Look for broken or stale references to other workbooks.
- Review formulas for any direct self-reference or loop.
- Move the formula or change the logic so it no longer points back to itself.
Excel warns about broken links from formulas that point to other spreadsheets. That warning is a strong clue when one workbook behaves differently from the rest.
Test the formula in a blank sheet
- Create a new worksheet in the same workbook.
- Enter the same formula with simple test values.
- If it works there, the original sheet likely has formatting, reference, or link issues.
- If it still fails, the problem is broader than one range.
Prevention is simple: leave Workbook Calculation on Automatic unless a manual mode is intentional, and watch for text formatting after paste. That avoids most repeat failures without changing the formula itself.
Frequently asked questions
How do I see Excel formula calculation settings?
Go to File > Options > Formulas and look under Workbook Calculation. The Automatic option should be selected. If it is not, switch it on and press F9 to recalculate the workbook.
Why are my Excel formulas not updating automatically?
Automatic workbook calculation is probably off. Check File > Options > Formulas, select Automatic, and then recalculate. If only one workbook is stale, check that file for broken links or self-referencing formulas rather than changing every formula on the sheet.
How do I fix a formula that only breaks after copy and paste?
Compare the separator characters and the cell references in the pasted formula. Use commas or semicolons as the workbook expects, then check whether relative references shifted. If the pasted range is text-formatted, change it to General or use Data > Text to Columns.
What should I check before rewriting a formula that won't calculate?
Check the calculation mode, cell format, leading apostrophes, Show Formulas, list separators, circular references, and external links first. Those settings explain many failures that look like formula logic problems. Rewriting too early can hide the real cause and waste time.