Formula errors
Excel formulas not calculating: what to check before rewriting them
When an Excel formula stops calculating, the formula is not always wrong. Sometimes the workbook is in manual calculation mode, the cell is formatted as text, or the formula was copied in a way that changed how Excel reads it.
Short answer
Check calculation mode, cell formatting, leading apostrophes, missing equals signs, separator settings, circular references, and copied ranges before replacing a formula that may only need a small setup fix.
First checks
Start with the workbook settings before blaming the formula
A formula can look broken even when the logic is fine. The quickest first check is calculation mode. If Excel is set to manual calculation, formulas may keep showing old results until the workbook recalculates. That is why a copied formula can appear to work in one file and stay stale in another.
After that, look at the cell itself. If the cell is formatted as text, starts with an apostrophe, or does not begin with an equals sign, Excel may display the formula instead of evaluating it. These are setup problems, not formula logic problems.
- Set calculation back to automatic if the workbook is stuck in manual mode.
- Confirm the formula starts with = and is not preceded by an apostrophe.
- Change text-formatted cells back to General, then re-enter the formula.
- Use Calculate Now when you need to confirm whether stale results are the issue.
Text display
If Excel shows the formula instead of the answer, inspect formatting first
When a cell displays =SUM(A:A) instead of the result, the usual cause is text handling. The cell may have been formatted as Text before the formula was entered, or the formula may have been pasted with a leading apostrophe. Excel then treats the formula as a label rather than something to calculate.
The clean fix is usually simple: change the cell format to General, remove any leading apostrophe, and re-enter the formula. Changing the format alone may not be enough if Excel has already stored the entry as text.
| Symptom | Likely cause | First fix |
|---|---|---|
| Formula text appears in the cell | Cell stored as text | Switch to General and re-enter the formula |
| Formula starts with '= on the formula bar | Leading apostrophe | Remove the apostrophe |
| Formula appears without = at the start | Missing formula marker | Add = before the function |
| Only one sheet seems stale | Manual calculation or cached result | Recalculate and check workbook settings |
Copied formulas
If formulas stop working after copy and paste, check references and separators
Copying formulas between files can change more than the cell location. Relative references may shift, external workbook links may point somewhere unexpected, and regional separators can turn a valid formula into one Excel cannot parse. A formula copied from a semicolon-based setup may need commas in another workbook, or the other way around.
Before rewriting the formula, compare the copied version with a known working version. Check whether ranges shifted, whether sheet names still exist, and whether the separators match the workbook locale. Small differences here explain many formula failures after paste.
Comma-based SUMIF
=SUMIF(B:B,"East",C:C)Common in US-style comma separator workbooks.
Semicolon-based SUMIF
=SUMIF(B:B;"East";C:C)Common in some locale settings where semicolons separate arguments.
Real formula errors
Move to formula logic only after the setup checks are clean
Once calculation mode, formatting, separators, and references are ruled out, the formula itself deserves a closer look. Common causes include mismatched ranges in SUMIFS or COUNTIFS, dirty lookup keys, missing exact-match settings, circular references, and formulas that expect numbers while the source cells contain text.
At this point, use one failing row and trace the formula in small pieces. If the formula is malformed or already showing an error value, the formula fixer is the better next step. If the result is technically valid but hard to understand, use the explainer before changing the business logic.
- For lookups, confirm the key exists and both sides use the same format.
- For SUMIFS and COUNTIFS, confirm every criteria range covers the same rows.
- For date formulas, confirm the source dates are real dates, not text.
- For circular references, decide whether iteration is intentional or accidental.
FAQ
Frequently asked questions
Why is Excel showing my formula instead of the result?
The cell is often formatted as text, the formula starts with an apostrophe, or the entry does not begin with an equals sign.
Why are my Excel formulas not updating automatically?
The workbook may be set to manual calculation. Switch calculation back to automatic or recalculate the workbook to test whether the result is stale.
Should I rewrite a formula that is not calculating?
Not immediately. Check workbook settings, cell format, separators, and copied references first. Rewrite only after those setup issues are ruled out.
Can the formula fixer help with this?
Yes, especially when the formula is malformed, uses the wrong separator, has a broken reference, or returns a clear error value.
Next pages
Use the matching tool when you need a custom formula
These pages turn the guide decision into spreadsheet output that matches your platform, ranges, and task.
Fix Excel Formula Errors
Diagnose why a formula is failing and get a safer corrected version to test.
Excel Formula Explainer
Understand what an existing formula does before you edit or replace it.
Excel Formula Generator
Start from a plain-English task and get a full Excel formula you can adapt.
Formula Examples
Browse copy-ready reference formulas before generating something more custom.