Excel formulas not working — showing as plain text instead of calculating, returning 0 when they shouldn’t, or displaying wrong results — is fixable once you understand which of the common causes applies. These are all distinct problems with distinct symptoms. This fits into the wider topic we cover in our Complete Guide to Fixing Windows, Browser, and Software Errors.
Which symptom do you see?
- Formula shows as text (you see the formula itself, not the result): the cell is formatted as Text, or the formula starts with a space
- Formula shows #NAME?, #REF!, #VALUE!: a specific formula error — each has a different cause
- Formula calculates to 0 or wrong result: likely a manual calculation mode or circular reference
- Formula works in some cells but not others: likely cell formatting inconsistency or mixed data types
Fix 1: Cell formatted as Text — the most common cause
When a cell is formatted as “Text,” Excel treats everything typed into it as a string — including formulas. The formula appears as typed rather than calculating. This is the most common cause of “my formula shows as text.”
Select the affected cells → Home → Number format dropdown → change from “Text” to “General” → press Delete to clear the cells → retype the formula. The format change alone isn’t enough — the formula needs to be re-entered (or press F2 → Enter on each cell) for Excel to re-evaluate it as a formula rather than text after the format change.
Fix 2: Calculation mode set to Manual
Excel in Manual calculation mode doesn’t recalculate formulas when data changes — formulas show results from the last calculation and appear “wrong” or stale. This mode is intentional for large spreadsheets (to avoid constant recalculation) but confusing when enabled unexpectedly.
Formulas tab → Calculation Options → Automatic. Press F9 to force an immediate recalculation if you want to see results update without changing the mode. Manual mode is sometimes enabled by opening a spreadsheet from a colleague who uses it intentionally — check calculation mode when any formula seems stale.
Fix 3: Formula starts with a space or apostrophe
A space before the equals sign ( =SUM(A1:A10) instead of =SUM(A1:A10)) or an apostrophe at the start ('=SUM(A1:A10)) prevents Excel from recognising the formula. The apostrophe explicitly tells Excel to treat the content as text. The space makes it look like text to Excel.
Check the formula bar when the cell is selected — if there’s a leading space or apostrophe: click in the formula bar, remove it, and press Enter. The cell immediately calculates.
Excel error codes — what each means
| Error | Meaning | Common cause |
| #NAME? | Unrecognized name in formula | Misspelled function name; missing quotes around text; using a function not in your Excel version |
| #REF! | Invalid cell reference | Rows/columns that the formula referenced were deleted |
| #VALUE! | Wrong data type | Text in a cell that formula expects to be a number |
| #DIV/0! | Division by zero | Dividing by a cell that’s empty or zero |
| #N/A | Value not found | VLOOKUP/INDEX-MATCH not finding the lookup value |
| #NUM! | Invalid numeric value | Square root of negative number; number too large |
| ###### | Column too narrow to display result | Widen the column — the formula itself is correct |
Fix 4: Numbers stored as text
If formulas that should sum or calculate numbers return 0 or wrong results: some “numbers” in the referenced cells may actually be text strings that look like numbers. Excel shows a small green triangle in the corner of cells with this issue.
Select the affected cells → you see a warning triangle → click it → “Convert to Number.” Or: paste special (Ctrl+Alt+V) → Values only over the range → this strips the text formatting and leaves pure numbers. SUMIF and COUNTIF formulas that skip some cells despite matching criteria: this is almost always the cause — the skipped values are text, not numbers.
Fix 5: Circular references
A formula that refers to its own cell (directly or through a chain of references) causes a circular reference. Excel either returns 0 or shows a “Circular Reference” warning. The status bar at the bottom of Excel shows “Circular References: [cell address]” when one exists.
Formulas tab → Error Checking → Circular References → Excel shows the specific cell(s) involved. Trace Precedents and Trace Dependents (also under Formulas) help visualise which cells are involved in the circular reference.
Our guide on Excel performance issues covers the calculation settings that affect formula recalculation timing, which connects to manual/automatic calculation mode. For Excel formula functions that work differently across versions, our Office version guide covers the version differences that affect function availability. Microsoft’s Excel formula documentation covers all error types in detail, the formula auditing tools (Trace Precedents, Trace Dependents, Evaluate Formula), and the IFERROR wrapper for gracefully handling formula errors.
Formula auditing tools
Excel’s Formulas tab has a full set of auditing tools for understanding why formulas aren’t working as expected:
- Trace Precedents: draws arrows showing which cells the selected formula reads from — confirms the formula is looking at the right cells
- Trace Dependents: shows which cells depend on the selected cell — useful for understanding the impact of a changed value
- Evaluate Formula: steps through the formula one calculation at a time, showing the intermediate result at each step. The most useful debugging tool for complex formulas — it shows exactly where a formula produces an unexpected result
- Show Formulas (Ctrl+`): switches all cells to show formulas rather than results, making it easy to identify cells that should have formulas but show values, or vice versa
VLOOKUP not finding values
VLOOKUP is one of the most common formula types and has predictable failure patterns:
- Returns #N/A: the lookup value doesn’t exist in the lookup range — check for extra spaces, case differences (VLOOKUP is case-insensitive but trailing spaces cause mismatches), or leading apostrophes in the data
- Returns wrong value: the last argument (range_lookup) is TRUE or omitted — use FALSE for exact match:
=VLOOKUP(A1,B:D,2,FALSE) - Returns #REF!: the column index number is larger than the number of columns in the lookup range
XLOOKUP (available in Excel 365 and Excel 2021) is easier to use and avoids many VLOOKUP pitfalls — if you’re creating new lookups, prefer XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array).
Conditional formatting vs actual value
Sometimes a formula is working correctly but the cell colour from conditional formatting makes it look wrong. Specifically: a formula returns the correct number but the cell’s conditional formatting makes it appear red/empty/different. Use Ctrl+` (Show Formulas mode) to see the actual formula, or check the formula bar while the cell is selected — the formula bar shows the raw value, bypassing any formatting.
Date and time formula issues
Excel stores dates as serial numbers (Jan 1, 1900 = 1, each subsequent day increments). Formulas involving dates can show numbers instead of dates if the cell format is General rather than Date. If a DATE formula returns a number (like 45291): the cell is formatted as General or Number — change to Date format to display it correctly. The formula itself is working; the display format needs adjustment.
Date arithmetic: today is =TODAY(), which returns the current date as a serial number. =TODAY()-A1 calculates the number of days between today and the date in A1 — but the result cell needs to be formatted as Number, not Date, to show the count of days rather than trying to display it as a date (which would show something like “Jan 1, 1900” for small day counts).
Array formula limitations
Legacy array formulas (entered with Ctrl+Shift+Enter in Excel 2019 and earlier) have specific behaviour that regular formulas don’t:
- They must be entered with Ctrl+Shift+Enter, not just Enter — entering without the Ctrl+Shift produces a different formula that often returns wrong results
- Curly braces
{=SUM(A1:A10*B1:B10)}indicate an array formula — these can’t be edited by pressing Enter alone - In Excel 365: dynamic arrays eliminate the need for Ctrl+Shift+Enter — functions like SUMPRODUCT, FILTER, and XLOOKUP handle array operations natively
Iterative calculation and circular references intentionally
Some advanced formulas (running totals, amortisation schedules) intentionally use circular references and require Iterative Calculation. File → Options → Formulas → check “Enable iterative calculation” → set Maximum Iterations to the appropriate count. Without iterative calculation enabled: intentional circular references return 0 or cause the circular reference error even when the calculation logic is correct.
Copy-paste breaking formula references
When copying formulas: Excel adjusts relative references automatically. A formula in A1 that says =B1+C1, when copied to A2, becomes =B2+C2. This is usually desired. When it’s not: use absolute references with dollar signs: =$B$1+$C$1 keeps the reference fixed regardless of where the formula is pasted. Mixed references (=$B1 fixes the column but not the row; =B$1 fixes the row but not the column) provide fine-grained control.
Paste Special → Paste Special (Ctrl+Alt+V) → “Formulas” only pastes the formula without adjusting formatting; “Values” pastes just the result. Understanding which type of paste to use prevents the most common copy-paste reference problems.
IFERROR — hiding formula errors gracefully
For formulas that sometimes return errors (VLOOKUP when the lookup value doesn’t exist, DIVIDE when the denominator might be zero): wrapping in IFERROR provides a cleaner result:
=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),"Not found")
=IFERROR(A1/B1,0)
IFERROR catches any error the inner formula produces and substitutes the specified value. It’s the correct approach for lookup formulas where “not found” is a valid state, not a formula bug. IFNA (catches only #N/A errors) is more precise for lookup formulas: =IFNA(VLOOKUP(...),"Not found").
Named ranges and formula errors
Formulas that reference named ranges (like =SUM(Sales_Q1)) return #NAME? if the named range doesn’t exist or was deleted. Formulas tab → Name Manager → confirms which named ranges are defined and shows any with errors (broken references appear in red). Deleting an error-state name and redefining it correctly resolves #NAME? errors from named range references.
Quick checklist: when a formula isn’t working
- Select the cell → check the formula bar — is there a leading space or apostrophe?
- Check the Number Format — is it “Text”? If so, change to “General” and re-enter the formula
- Formulas → Calculation Options — is it “Manual”? Change to “Automatic” and press F9
- If the formula shows an error code: look up the specific error in the table above
- Use Evaluate Formula to step through complex formulas and find where they go wrong
Excel formula problems are almost always configuration or data issues rather than bugs — the formula engine itself is reliable. The five-step checklist above resolves the large majority of “my formula doesn’t work” situations in under 5 minutes, and Evaluate Formula solves complex formula logic issues that the basic checks don’t catch.
Excel version differences and function availability
Functions introduced in newer Excel versions (XLOOKUP, FILTER, UNIQUE, SEQUENCE in Excel 365; IFERROR in Excel 2007+) cause #NAME? errors in older Excel versions. If a spreadsheet was built in Excel 365 and opened in Excel 2019: functions like XLOOKUP return #NAME? because they don’t exist in that version. Options: use older equivalent functions (VLOOKUP instead of XLOOKUP), or save the spreadsheet in a format that converts to compatible formulas.
File → Account → About Excel shows the current version. If you share spreadsheets with users on older versions: sticking to functions that exist in Excel 2016 ensures compatibility across all current business versions.
Worksheet protection blocking formula changes
If formula cells show as locked and can’t be edited: the worksheet or workbook is protected. Review → Unprotect Sheet (or Unprotect Workbook) → enter the password if required. If you don’t know the password: the protection can sometimes be removed for legacy .xls files but not for modern .xlsx files without the correct password.
For worksheets you want to protect while allowing formula cells to remain editable: right-click the formula cells → Format Cells → Protection → uncheck “Locked” → then apply sheet protection. Only cells with “Locked” checked are protected; unlocked cells remain editable even when protection is active.
A practical note on Excel’s formula recalculation: very large spreadsheets with thousands of formulas sometimes display stale results because Excel’s background calculation hasn’t finished yet. The status bar at the bottom of Excel shows “Calculating: X%” during background calculation. If formulas appear wrong: check whether calculation is in progress. Pressing Escape stops editing → F9 forces an immediate full recalculation — if results change after F9, the formulas were correct all along and the display was just stale from incomplete background calculation. See also Excel Not Responding for a related case.
Understanding the difference between a formula that’s wrong (returns incorrect results) and a formula that’s broken (shows an error or text) changes the troubleshooting approach. For wrong results: Evaluate Formula and checking referenced data (numbers stored as text, calculation mode) are the tools. For broken formulas (showing as text, showing errors): cell format and error code interpretation are the starting points. Mixing up which type of problem you’re dealing with is the most common reason troubleshooting takes longer than it should. You might also run into Excel Crashes in Windows 11.
