Excel formulas fail due to calc mode, wrong references, text numbers, or errors; switch to Automatic, correct ranges, and clean data.
You enter a function, press Enter, and the cell shows nothing helpful. The good news: most issues trace to a short list. Here’s a quick map and the fixes that stick right away. You’ll fix issues fast—start here now. Start now.
Fast Checks And Fixes
| Issue | Symptom | Fix |
|---|---|---|
| Manual calculation | Numbers change but results don’t | Formulas > Calculation Options > Automatic, then F9. |
| Missing equals sign | Text shows instead of a result | Start with = then the function. |
| Text stored as numbers | SUM or AVERAGE returns zero | Select cells, Data > Text to Columns > Finish, or multiply by 1. |
| Wrong list separator | Comma vs semicolon mismatch | Match your locale; use the correct delimiter. |
| Typos or wrong case | #NAME? appears | Use AutoComplete or Insert Function. |
| Bad range | #REF! after edits | Rebuild the link or use structured references. |
| Hidden spaces | LOOKUPs miss matches | TRIM and CLEAN the source fields. |
| Dates as text | Sorting or math breaks | VALUE, DATE, or DateValue to convert. |
| Spill blocked | Spill warning icon | Clear the target range. |
| Protected or merged cells | Edits fail or sums skip | Unmerge; review sheet protection. |
Why A Formula Fails In Excel: Quick Map
Excel calculates only when it’s told to. If the workbook sits in Manual, updates sit idle. If references point to deleted cells, results break. If your sheet mixes text numerals, separators from another locale, or mismatched parentheses, Excel punts. Fixes line up with those patterns.
Set Calculation To Automatic
Open the workbook. Go to Formulas, pick Calculation Options, then choose Automatic. Tap F9 to refresh the file. Shift+F9 refreshes the active sheet. This single setting explains a surprising share of late-night spreadsheet mysteries. See Microsoft’s note on calculation mode for background.
Start With The Equals Sign
If a cell holds “SUM(A1:A10)” without the leading equals, Excel treats it like plain text. Re-type with “=SUM(A1:A10)”. If the sheet shows formulas instead of results, toggle Show Formulas on the Formulas tab or hit Ctrl+`.
Watch Your List Separator
Excel uses a list separator based on system locale. Some regions expect commas, others want semicolons. A function built on one laptop can fail on another. Re-type the delimiter to match the machine. When you share workbooks across regions, use helper cells or named ranges to reduce edits. Microsoft’s guide on broken formulas and separators spells this out.
Fix Typos And #NAME? Issues
Miss a letter in VLOOKUP or SUMIFS and you get #NAME?. Let AutoComplete finish function names. If you typed a range name that doesn’t exist, define it on Formulas > Name Manager, or replace with a valid address.
Repair Broken References
Delete a row or move a range and a link can fall apart, returning #REF!. Rebuild the range, or restructure your sheet to avoid brittle addresses. When the source lives in a table, a structured reference keeps links stable through inserts and deletes.
Tame Circular References
A circular reference points a cell back to itself, directly or through other cells. Excel flags it with a banner. Either rewrite the logic to break the loop or enable iterative calculation with sane limits. Keep iterations low and watch for convergence.
Stop Text-Numbers From Breaking Math
Imports, copy-pastes, and CSV dumps often bring numerals in as text. SUM and AVERAGE then miss them. Quick fixes: multiply the range by 1, add zero, or run Text to Columns with no changes. For decimals, match the decimal symbol to your region.
Clean Dates So They Act Like Dates
Dates that look fine can still be text. You can’t add days or plot timelines with them. Build real dates with DATE(year,month,day), or convert with VALUE. If you ingested a day-month order that your locale interprets as month-day, rebuild with DATE using split parts.
Mind Relative, Absolute, And Mixed References
Copy a formula across a sheet and the address shifts. That’s by design. Use $ to lock a row, a column, or both. $A$2 keeps both fixed; A$2 locks the row only; $A2 locks the column only. Choose based on how you’ll fill the range.
Check Parentheses And Argument Count
Too few or too many brackets trip Excel. The Function Wizard shows required fields. Keep arguments in the right order and double-check closing parentheses.
Deal With Spill Behavior
Dynamic array functions spill. If something blocks the target range, you’ll see a warning. Clear the cells, resize the output, or shape with TAKE, DROP, or CHOOSECOLS. Older builds lack these functions.
Use Structured References With Tables
Convert ranges to tables with Ctrl+T and write logic with column names. Formulas stay readable and survive inserts. Outside the table you can still point to columns by name.
Harden Lookups
Mismatched text breaks matches. Clean both sides with TRIM and CLEAN. Align types with VALUE or a double-unary. Prefer XLOOKUP or INDEX/MATCH over legacy VLOOKUP.
Watch Decimal And Thousand Symbols
Some regions use periods for decimals and commas for thousands; others swap them. If an import uses the opposite pattern, Excel may treat values as text or as wrong magnitudes. Align the symbols in regional settings or fix with find-and-replace on source column copies.
Diagnose With Evaluate Formula
The Evaluate tool steps through a calculation. Pick a cell, then Formulas > Evaluate Formula. Step to the failing part, then correct the logic or the source data.
Control External Links And Trust
If your sheet pulls values from other files, blocked content can stop refreshes. Check the yellow bar near the formula bar or open Trust Center to allow the content you need. When you no longer rely on an external file, break the link and keep values.
Keep Sheets Unmerged And Unprotected When Editing
Merged cells break ranges and block features like sort and filter. Unmerge, then rebuild the layout with alignment and spacing. Locked sheets can also hide the cause of a failure. Lift protection when you troubleshoot, then restore it once the logic is solid.
Check Data Types In Power Query
When a query loads text where you expected numerals, downstream totals stall. In Power Query, set the right type for each column before loading. Keep an eye on locale during CSV or TXT import steps.
Know Your Version
Some functions live only in newer builds. If a colleague on an older desktop opens a workbook built with newer dynamic array functions, cells may show #NAME?. Share a range with pre-dynamic alternatives or keep everyone on the same channel.
Second Table: Error Codes Cheat Sheet
| Error | Meaning | Quick Fix |
|---|---|---|
| #DIV/0! | Divide by zero or an empty cell | Wrap with IFERROR or handle blanks. |
| #VALUE! | Wrong type: text where a number is needed | Convert types; clean spaces. |
| #REF! | Reference points to a deleted cell | Recreate the range or table link. |
| #NAME? | Function or range name is misspelled | Pick from AutoComplete; fix names. |
| #N/A | No match in a lookup | Confirm lookup values and ranges. |
| #NUM! | Result out of range or bad math | Check inputs, limits, or iteration. |
| #NULL! | Space used where an operator was needed | Use commas or intersections as designed. |
| #SPILL! | Spill range is blocked | Clear the target cells or reshape the spill. |
Step-By-Step Fix Flow
- Press F9. If results change, you had a calc problem. Switch to Automatic.
- Pick one broken cell. Open Evaluate Formula. Step until you hit the culprit.
- If you see a pound error, match it to the cheat sheet and apply the fix.
- If no pound error, scan for text-numbers, wrong separators, or typos.
- Rebuild brittle ranges with tables and named ranges.
- Rewrite complex chains into smaller cells and test each piece.
Safer Patterns That Prevent Failures
Prefer names and tables over raw addresses. Keep input data clean through Power Query or data validation. Avoid merged cells. Use helper columns rather than one mega-formula that’s hard to debug. Add comments to tricky cells with Alt+Enter for clear notes.
Excel Menu Paths To Remember
Key paths: Calculation Options, Name Manager, Evaluate, Text to Columns, Data Validation, Unprotect Sheet, Total Row.
Small Gotchas That Waste Time
A leading apostrophe forces text. Clear it or use VALUE. Non-breaking spaces from web pages look like blanks but TRIM misses them; CLEAN or SUBSTITUTE(CHAR(160),””) helps. Hidden characters from PDFs can lurk in imports; paste into Notepad first or scrub in Power Query.
When Should You Use Iterative Calculation?
Some models need loops. Turn on iterative calc, set modest limits, and leave a note that explains why.
Spill Tricks That Save Clicks
Use LET to name parts. FILTER pulls matches, SORT orders them, UNIQUE lists distinct values. TOCOL, TOROW, and WRAPROWS reshape results. Keep the output area clear.
Build Resilient Lookups
Use XLOOKUP with exact match. Point the lookup value to a trimmed, upper-cased helper when data is messy. If the return column moves, XLOOKUP still returns the right field.
Protect Results From Input Errors
Use data validation lists, date pickers, and whole-number limits. For imports, give users a staging sheet with clear headers and short notes.
When Sharing Across Regions
Spell out decimal and list separators in a short note near inputs. If a teammate runs a different locale, use helper cells that assemble arguments with TEXTJOIN, then pass them into functions in a way that works across machines.
Final Sweep Before You Ship
Run Error Checking. Step through hard cells. Recalc, save, close, and reopen to confirm calc mode. Then protect sheets that should stay read-only. Leave a short note near complex logic for the next person.