Excel Errors — the complete guide
Every Excel error code, every cause, every fix. From the humble #DIV/0! you saw on day one to the modern #SPILL! that only appears in Excel 365 — plus the systematic debugging workflow that turns "why is my spreadsheet broken?" into "here's exactly what's wrong and how to fix it in 30 seconds".
📑 What's in this guide
- Why Excel errors exist (and why that's actually good)
- All 9 Excel errors at a glance
- #REF! — deleted reference
- #NAME? — unrecognized identifier
- #VALUE! — wrong data type
- #DIV/0! — division by zero
- #N/A — value not available
- #NULL! — invalid range intersection
- #NUM! — number problem
- #SPILL! — blocked dynamic array
- #CALC! — dynamic array calculation problem
- Non-error problems (circular refs, formulas as text)
- IFERROR vs IFNA vs ISERROR — which to use
- The 5-step debugging workflow
- Prevention strategies
- When errors are helpful (don't hide them)
- Frequently asked questions
Why Excel errors exist (and why that's actually good)
Excel errors are diagnostic signals, not failures. When you see #REF! in a cell, Excel is telling you something specific: a reference in that formula no longer points to a valid location. The alternative — silently returning the wrong number — would be catastrophic in a financial model or dashboard.
Every Excel error code represents a distinct type of problem. Learning to read them is the difference between a spreadsheet user who panics at the first error and one who fixes it in seconds. This guide covers all nine error types with plain-English explanations, exact fix formulas, and prevention strategies you can apply immediately.
The frustration many people feel with Excel errors comes from treating them as noise to suppress. Once you treat them as messages to interpret, the entire relationship with Excel changes. That's the goal of this guide.
Each error section follows the same pattern: what it means, the top 3-4 causes (in order of frequency), the exact fix for each cause, and the prevention strategy. Skim the "at a glance" table below to find your specific error, or read straight through as a systematic reference.
All 9 Excel errors at a glance
| Error | Meaning in one sentence | Excel version |
|---|---|---|
| #REF! | A cell reference points to something that no longer exists. | All versions |
| #NAME? | Excel doesn't recognize a function name or named range. | All versions |
| #VALUE! | Wrong data type — text where a number is expected or vice versa. | All versions |
| #DIV/0! | Division by zero (or by an empty cell that Excel treats as zero). | All versions |
| #N/A | A lookup couldn't find its target value. | All versions |
| #NULL! | Two ranges don't intersect where the formula expected them to. | All versions |
| #NUM! | Number is invalid — too large, too small, or breaks a math rule. | All versions |
| #SPILL! | A dynamic array formula can't fill its target cells. | Excel 365, 2021+ |
| #CALC! | A dynamic array calculation returned an unusable result. | Excel 365, 2021+ |
Each error below has its own deep dive with causes and fixes. If you already have a specific error, use the table of contents above to jump straight to it. Otherwise, read through — the patterns build on each other.
#REF! — deleted reference
A cell reference in your formula points to something that was deleted or moved out of range.
Most common in: Formulas after you delete rows/columns · VLOOKUP with wrong col_index_num · INDEX with out-of-range row or column arguments
Cause 1 — You deleted a row or column that was referenced
The classic case. A cell held =A5+B5. You deleted row 5. Now that cell holds =#REF!+#REF!. Excel had no way to update the formula because the referenced cells are gone entirely.
The proper fix: rewrite the formula pointing to a valid cell. IFERROR is only a band-aid — it hides the real issue. See the full #REF! guide for the systematic recovery process.
Cause 2 — VLOOKUP col_index_num exceeds the table_array width
VLOOKUP with =VLOOKUP(A1, B:D, 5, FALSE) asks for column 5 of a range only 3 columns wide. Fix: either expand the range or reduce the column number.
Cause 3 — INDEX row/column arg is out of range
=INDEX(A1:A10, 15) asks for the 15th row of a 10-row range. Guard dynamic index calculations with MIN or bounds checks.
Prevention
Use Excel Tables (Insert → Table). Table references adjust automatically when you delete rows. Also use XLOOKUP or INDEX/MATCH instead of VLOOKUP — both reference columns by range, not hardcoded index, so they survive column inserts.
#NAME? — unrecognized identifier
Excel doesn't recognize a function, named range, or text token in the formula.
Most common in: Typos in function names · Missing quotes around text · Modern functions in older Excel · References to deleted named ranges
Cause 1 — Typo in the function name
=VLOKUP(...) instead of VLOOKUP. =SUMFI(...) instead of SUMIF. Excel finds no matching function and returns #NAME?.
Fix: read the function name character by character. Turn on formula autocomplete (File → Options → Formulas → Formula AutoComplete) and let Excel suggest as you type.
Cause 2 — Missing quotation marks around text
=VLOOKUP(Wireless Mouse, B:D, 3, FALSE) — Excel tries to interpret Wireless Mouse as a name, doesn't find it, returns #NAME?.
All text arguments in formulas need double quotes.
Cause 3 — Modern function in older Excel
XLOOKUP in Excel 2019 or older = #NAME?. LAMBDA in Excel 2021 perpetual or older = #NAME?. IFS in Excel 2013 or older = #NAME?.
Fix: either upgrade Excel or use the compatibility equivalent. INDEX/MATCH for XLOOKUP. Nested IF for IFS.
Cause 4 — Deleted named range
You had a named range called "TaxRate" used in formulas. Someone deleted the name in Name Manager. All formulas using it now show #NAME?.
Fix: open Formulas → Name Manager (Ctrl+F3) and recreate the missing name. See the full #NAME? guide.
Prevention
Use the Formula Wizard (fx button in the formula bar) for unfamiliar functions — it validates syntax as you build. Document named ranges centrally so nobody deletes ones others depend on.
#VALUE! — wrong data type
The formula uses the wrong data type — text where a number is expected, or vice versa.
Most common in: Math with text-formatted cells · Formulas after data imports · Concatenation attempting on ranges
Cause 1 — Math on text-formatted numbers
A cell shows "100" but is actually stored as text (maybe from a pasted CSV or web import). =A1+B1 where either cell is text returns #VALUE!.
VALUE() converts text-formatted numbers into real numbers Excel can compute with.
Cause 2 — Function argument is wrong type
=LEFT(A1, "three") — LEFT expects a number for the length argument, got text. =DATE("2024", 1, 1) — DATE expects numbers for year/month/day.
Fix: ensure each argument matches the function's expected type. Consult the function's documentation for what each argument requires.
Cause 3 — Range passed where a single value was expected
=UPPER(A1:A10) in old Excel — UPPER expects one value, got 10. Excel 365 handles this via implicit intersection or spilling, but older versions return #VALUE!.
Prevention
Use Data → Text to Columns after importing data — this often auto-detects text-formatted numbers and converts them. Add data validation to ensure user-entered values match the expected type. See the full #VALUE! guide.
#DIV/0! — division by zero
Something was divided by zero — including empty cells that Excel treats as zero in math contexts.
Most common in: Ratios where denominator can be 0 · AVERAGE of empty range · Percentage calculations before data is entered
Cause 1 — Explicit division where divisor is zero or blank
Or the more expressive check:
The IF version is more explicit — it tells the reader you specifically expect zero divisors. IFERROR catches everything, which hides other bugs.
Cause 2 — AVERAGE or similar of an empty range
=AVERAGE(A1:A10) where all 10 cells are empty returns #DIV/0! (Excel divides sum by count, count is 0).
Fix: =IFERROR(AVERAGE(A1:A10), 0) or check first with =IF(COUNT(A1:A10)=0, 0, AVERAGE(A1:A10)).
Cause 3 — Percentage calculations before source data exists
A dashboard shows =Sales/Target. Sales and Target cells are empty (data hasn't been entered yet). Every percentage cell shows #DIV/0!.
Fix: wrap in IFERROR globally, or use IF to check both operands: =IF(OR(Sales="", Target=""), "-", Sales/Target).
Prevention
Use data validation to reject 0 in cells that will be denominators (Data → Data Validation → Allow: Whole Number → Greater than 0). Design formulas defensively when the input might be zero. See the full #DIV/0! guide.
#N/A — value not available
A lookup function couldn't find its target value. Most common Excel error by volume.
Most common in: VLOOKUP/XLOOKUP/INDEX-MATCH with missing values · Extra spaces in lookup values · Text vs number mismatches
Cause 1 — Value genuinely doesn't exist in the lookup range
IFNA catches only the #N/A error — other errors like #REF! or #VALUE! still show, which is usually what you want for lookups. If you use IFERROR instead, you'll hide bugs that indicate genuine data problems.
Cause 2 — Invisible trailing or leading spaces
Data pasted from PDFs, web pages, or emails often has non-breaking-space characters at the ends of values. VLOOKUP sees "Product1" and "Product1 " as different values.
TRIM removes leading, trailing, and duplicate internal spaces. Solves about 30% of "impossible" #N/A errors.
Cause 3 — Type mismatch (text vs number)
The lookup column has product IDs stored as text ("1001", "1002"). Your lookup value is a real number (1001). Excel treats them as different.
Converts the lookup value to text to match the column format. Or convert the column to numbers via Text to Columns.
Cause 4 — Missing FALSE causes approximate match on unsorted data
The most insidious version: no error appears, but wrong values are returned. Always specify FALSE explicitly. See the full #N/A guide.
Prevention
Standardize your data — always TRIM before storing, always convert types to match. Use a lookup validation cell that checks presence: =IF(ISNUMBER(MATCH(A1, B:B, 0)), "OK", "Missing").
#NULL! — invalid range intersection
Two ranges that don't intersect were combined with a space operator.
Most common in: Accidental space instead of comma · Union of non-overlapping ranges
Rare error. Almost always a typo. =SUM(A1:A10 B1:B10) uses a space between the two ranges — Excel interprets space as "intersection" and finds none, returning #NULL!.
Replace the space with a comma (for union of both ranges) or use a colon (for a single continuous range).
Prevention
Be careful when typing formulas with multiple ranges. If you see #NULL!, look for accidental spaces where commas or colons should be. See the full #NULL! guide.
#NUM! — number problem
A number is invalid for the function — too large, too small, or breaks a math rule.
Most common in: SQRT of negative number · LOG of zero or negative · IRR/RATE that can't converge · Numbers exceeding Excel's numeric range
Cause 1 — Math impossibility
=SQRT(-25) returns #NUM! because there's no real square root of a negative number. Same for =LOG(0) or =LOG(-5).
ABS() converts negatives to positive first. Whether this makes semantic sense depends on your use case.
Cause 2 — Financial functions that can't converge
IRR and RATE use iterative calculation. Given weird input data, they may not find a solution. Provide a Guess argument closer to the expected answer:
The 0.1 (10%) is a starting guess. If IRR still returns #NUM!, try different guesses — 0.05, 0.20, -0.05. The right guess is usually close to the true answer.
Cause 3 — Number exceeds Excel's range
Excel handles numbers between about 1.79E308 and 2.23E-308. Numbers outside this range return #NUM!. Rare in typical business use.
Prevention
Validate inputs before feeding to math functions. Test edge cases during development: what happens with a zero input? A negative? Empty? See the full #NUM! guide.
#SPILL! — blocked dynamic array (Excel 365 only)
A dynamic array formula can't fill the cells it needs to.
Most common in: FILTER, UNIQUE, SORT, XLOOKUP returning multi-column · Data in the spill zone · Merged cells
Cause 1 — Data blocking the spill zone
You wrote =UNIQUE(A2:A100) in cell C2. If C3, C4, C5... have data in them, UNIQUE can't spill downward and returns #SPILL!.
Fix: clear the cells in the spill zone. Excel shows a dashed outline around where the formula NEEDS to spill — clear everything inside it.
Cause 2 — Merged cell in the spill zone
Merged cells are the enemy of dynamic arrays. Even one merged cell in the spill zone breaks the entire spill.
Fix: unmerge cells in the spill area. Right-click → Format Cells → Alignment tab → uncheck "Merge cells".
Cause 3 — Formula near a Table's edge
If your dynamic array formula lives inside or right next to an Excel Table, the table's structure may block the spill.
Fix: move the formula to an area outside any Tables. Dynamic arrays and Excel Tables don't play well together yet.
Workaround — force a single value
The @ prefix forces implicit intersection — you get only the first matching value, no spilling. Useful when the whole array isn't needed. See the full #SPILL! guide.
Prevention
Keep spill zones empty. Don't merge cells anywhere near where dynamic arrays might land. Test dynamic array formulas in an empty area of the sheet first, then move them to their final location.
#CALC! — dynamic array calculation problem (Excel 365 only)
A dynamic array function returned an unusable result — often an empty array.
Most common in: FILTER with no matches and no 3rd argument · Nested arrays Excel can't handle · Faulty recursive LAMBDA
Cause 1 — FILTER returned nothing
=FILTER(A:A, B:B="Nonexistent") returns no rows because nothing matches. Without a 3rd argument (if_empty), FILTER returns #CALC!.
The 3rd argument replaces the empty result with your message.
Cause 2 — Array of arrays
Some functions can't handle arrays that contain other arrays as elements. Simplify by restructuring the formula or using a helper column.
Cause 3 — Recursive LAMBDA problem
A LAMBDA that recursively calls itself may hit a case where the base condition isn't met properly, returning something unusable.
Fix: verify your LAMBDA's base case fires correctly. Test with the simplest possible input first. See the full #CALC! guide.
Non-error problems that feel like errors
Not everything that looks wrong is one of the # error codes. Two common issues that feel like errors but aren't technically classified as such:
Circular references
A cell that references itself, either directly (A1 = A1+1) or indirectly through a chain of formulas (A1 references B1 which references A1). Excel shows a status bar warning and puts 0 in the cell. Not technically an error code, but breaks your workbook.
Fix: Formulas → Error Checking → Circular References shows a list. Trace and rewrite to eliminate the loop. For legitimate iterative calculations (rare), enable them: File → Options → Formulas → Enable iterative calculation. See the full circular reference guide.
Formula displayed as text instead of calculating
You typed =SUM(A1:A10) in a cell and instead of the total, the cell shows the literal text "=SUM(A1:A10)". Frustrating but easy to fix — three possible causes:
- Cell is text-formatted. Select the cell → Home → Number Format → change from "Text" to "General". Then re-enter the formula (F2, Enter).
- Show Formulas mode is on. Press
Ctrl + `(the backtick key, usually left of "1") to toggle off. Or Formulas → Show Formulas. - Leading apostrophe. A leading
'forces text mode. Edit the cell (F2) and delete the apostrophe.
IFERROR vs IFNA vs ISERROR — which to use
Three wrappers exist for handling errors gracefully. They look similar but behave very differently:
| Aspect | IFERROR | IFNA | IF + ISERROR |
|---|---|---|---|
| Catches which errors? | ALL 9 error types | ONLY #N/A | ALL 9 (via IF wrap) |
| Excel version | 2007+ | 2013+ | Every version |
| Syntax | =IFERROR(formula, alt) |
=IFNA(formula, alt) |
=IF(ISERROR(x), alt, x) |
| Best for | When you truly want any error hidden | Lookups — catches misses, exposes real bugs | Custom branching on error type |
| Downside | Hides bugs — a #REF! looks like a lookup miss | Doesn't catch #DIV/0!, #REF!, etc. | Verbose, formula runs twice |
For lookups, use IFNA. For truly any error, use IFERROR only when you know exactly what errors could occur and you want them all handled the same way. For custom logic per error type, use ISERROR with IF. See the individual guides: IFERROR and IF.
The 5-step debugging workflow
When you inherit a broken spreadsheet or your own workbook suddenly shows errors, resist the urge to wrap everything in IFERROR. Instead, follow this systematic workflow:
Identify the error type
Which of the 9 error codes are you seeing? Different errors need different diagnostic approaches. A #REF! means look for deleted references. A #NAME? means look for typos or missing named ranges. Match the code to the section above for cause-specific guidance.
Trace the source
Errors propagate — a #REF! in one cell causes #REF! in every dependent cell. Find the ORIGINAL source. Use Formulas → Trace Precedents to see which cells feed into the error, and Trace Dependents to see what the error affects. Arrows appear on screen showing the data flow.
Evaluate the formula step-by-step
Select the erroring cell. Go to Formulas → Evaluate Formula. Click "Evaluate" repeatedly to see each intermediate result. The step where the value becomes an error is where the problem lives. This is the single most powerful Excel debugging tool most users have never opened.
Fix the root cause, not the symptom
If a VLOOKUP returns #N/A because of trailing spaces in the lookup value, add TRIM to fix it. Don't just wrap in IFNA to hide the miss — that leaves the underlying data problem in place, which will bite you again later.
Test with edge cases
After fixing, test with problematic inputs. What happens with empty data? Zero? Negative? A lookup value that doesn't exist? A workbook that handles the happy path but breaks on edge cases will break in production.
Prevention strategies
The best error handling is preventing errors from happening in the first place. Six specific practices:
1. Use Excel Tables (Insert → Table)
Table references adjust automatically when rows are inserted or deleted. Formulas like =Table1[@Salary] * 1.1 never break from row operations. Tables also make it obvious which cells are data and which are formulas.
2. Prefer XLOOKUP or INDEX/MATCH over VLOOKUP
Both handle column inserts without breaking. VLOOKUP with a hardcoded column index (=VLOOKUP(A1, B:D, 3, FALSE)) silently returns wrong values if someone inserts a column in the middle. XLOOKUP and INDEX/MATCH reference the return column by range, so column inserts are safe.
3. Add data validation to input cells
Data → Data Validation lets you restrict what users can enter. Reject empty values in denominators. Enforce whole numbers only. Constrain to a dropdown list. Prevention at input time beats error handling later.
4. Always specify FALSE explicitly for exact-match lookups
The default is TRUE (approximate) which silently returns wrong values on unsorted data. Writing FALSE explicitly is 5 seconds of typing that prevents entire categories of bugs.
5. Use structured references, not hardcoded coordinates
Instead of =A1, use =Employees[[#This Row], [Salary]] (Excel Table syntax) or =Salary (named range). Structured references are self-documenting and survive row/column changes.
6. Design defensively — check before computing
Before dividing, check the denominator. Before looking up, check the value exists. This costs one extra formula per operation but eliminates a class of errors:
When errors are helpful (don't hide them)
The instinct to hide every error with IFERROR is understandable but often wrong. Some errors are diagnostic signals you WANT to see:
During development. Errors show you exactly what's broken. Wrapping in IFERROR during development means every bug is invisible until it causes real damage. Add error handling only after the workbook works correctly on happy-path inputs.
In financial models. An unexpected #DIV/0! or #REF! in a financial model is a warning that something structural changed. Hiding it under "N/A" means a shareholder or client sees clean numbers that are actually wrong. Let the error show and investigate.
In data quality reports. If your goal is to identify records with missing data, an #N/A in a lookup IS the answer — it flags the record needing attention. Hiding it defeats the purpose.
In shared workbooks. Other people need to see when something is broken. A silently wrong value in a shared workbook can propagate through decisions for months before anyone notices. Visible errors are impossible to ignore.
Only hide errors when you know EXACTLY what error will occur and EXACTLY what alternative to show. "I'll wrap in IFERROR just in case" is a code smell — it means you don't yet understand what could go wrong, and you're building a workbook that will fail silently.
Related functions and error pages
📥 Download the practice workbook
All 9 errors with causes + fixes · Live IFERROR/IFNA wrapped formulas · IFERROR vs IFNA vs ISERROR head-to-head
Frequently asked questions
How many types of errors does Excel have?
Excel has 9 formula errors: #REF!, #NAME?, #VALUE!, #DIV/0!, #N/A, #NULL!, #NUM!, #SPILL!, and #CALC!. The first seven work in every Excel version; #SPILL! and #CALC! only appear in Excel 365 and 2021+ where dynamic arrays exist. There are also non-error problems like circular references and formulas appearing as text — related but not technically errors.
What is the difference between IFERROR and IFNA?
IFERROR catches ALL nine error types under one alternative value. IFNA catches ONLY the #N/A error and lets other errors pass through. Use IFNA for lookups where a missing value is expected but a #DIV/0! or #VALUE! would indicate a genuine bug worth seeing. Use IFERROR only when you know no other errors could occur — otherwise you're hiding bugs.
How do I find the source of an Excel error?
Use Excel's built-in tools: (1) Formulas → Error Checking to walk through all errors in the workbook, (2) Formulas → Trace Precedents to see which cells feed into the error, (3) F9 in the formula bar to evaluate parts of a formula individually, (4) the Evaluate Formula dialog for step-by-step formula evaluation. The Evaluate Formula tool is the single most powerful debugging feature most users have never opened.
Should I always wrap formulas in IFERROR?
No. IFERROR hides all errors under a single alternative value, which means genuine bugs become invisible. Only wrap when you have a specific expected error (like a lookup miss) and know exactly what fallback should apply. In development and troubleshooting, let errors show — they're valuable diagnostic signals. Production financial models especially should NOT hide unexpected errors.
Why does my formula show as text instead of calculating?
Three common causes: (1) the cell format is set to Text — change it to General or Number, then re-enter the formula, (2) 'Show Formulas' mode is on — press Ctrl+` or go to Formulas → Show Formulas to toggle, or (3) the formula has a leading apostrophe forcing text mode — edit the cell and remove the apostrophe.
What causes #SPILL! errors and how do I fix them?
The #SPILL! error happens when a dynamic array formula (FILTER, UNIQUE, SORT, XLOOKUP returning multi-column) can't fill its target cells because something is blocking the spill zone. Fixes: clear cells below and to the right of the formula, unmerge any merged cells in the spill area, or prefix with @ to return only the first value: =@FILTER(...).
Can I completely prevent Excel errors from happening?
No — errors are Excel's way of signaling that something is wrong. Total prevention would hide problems. What you CAN do: use data validation to reject invalid inputs, structure formulas defensively (check divisors before dividing), use Excel Tables which auto-adjust references, and test edge cases (empty data, zero, missing lookups) during development. Aim for graceful handling, not total elimination.
What's the difference between #REF! and #NAME?
#REF! means a cell reference is now invalid (the referenced cell was deleted, or a lookup index exceeded its range). #NAME? means Excel doesn't recognize a function or name (typo in function, missing quotes around text, or reference to a deleted named range). #REF! is about cells; #NAME? is about identifiers Excel can't resolve.
Do errors affect formulas that reference the error cell?
Yes. Errors propagate: if A1 is #REF! and B1 contains =A1 + 5, then B1 is also #REF!. This is called error propagation and it's usually helpful — you can trace an error back to its source by following the chain. To stop propagation, wrap the source formula in IFERROR or use ISERROR in an IF statement to branch on the error.
Bookmark this guide — errors happen at the worst possible moments. Having the reference open in another tab means fixes take seconds instead of hours.