10 Excel Errors and How to Fix Them in 2026 — Complete Error Reference
Home› 10 Excel Errors and How to Fix Them 2026
⚠ Top List · Excel Errors

10 Excel errors and how to fix them in 2026

Every Excel user has stared at a red-boxed cell full of #NAME? and wondered what went wrong. Excel has 9 different error values plus circular references — each with a specific meaning, cause, and fix. This is the complete reference: what each error means, why it happened, how to fix it right now, and how to prevent it forever.

⚠ How this reference is organized

Each error entry follows the same 3-part structure: Diagnosis (what triggered it), Fix (exact steps to resolve it now), and Prevention (how to stop it from happening again). Bookmark this page — you'll come back to it every time an error appears.

The 30-second Excel error diagnostic

Before you dive into fixing any specific error, apply this quick mental checklist:

  1. Read the error name. Excel actually tells you what's wrong — #REF! means broken reference, #NAME? means unrecognized name, and so on. Half the diagnosis is just reading the tag.
  2. Click the cell. Look at the formula bar. What does the formula say? Is anything obviously wrong (misspelled function, missing quotes, broken cell reference)?
  3. Look for the yellow warning triangle. Excel shows a small dropdown next to error cells. Click it — Excel often suggests the exact fix.
  4. Trace precedents. Formulas → Trace Precedents (or Ctrl+[) shows which cells feed into this formula. Follow the arrows to find the source of the error.
  5. Check the source data. Did someone delete a row? Change data types? Add a blank cell? Most errors originate in the data, not the formula.

Now let's walk through each error individually.


1
Very Common Lookup
#N/A Value Not Found

Lookup couldn't find what it was looking for

#N/A stands for "Not Available". It appears when a lookup function like VLOOKUP, XLOOKUP, or MATCH can't find the value you asked it to look up. Unlike other errors, #N/A often indicates a data problem, not a formula problem.

Diagnosis — What triggered it Lookup value doesn't exist in the lookup range. Common causes: typos, trailing spaces in the source data (visually identical but different), or data type mismatch (text "123" vs number 123).
Fix — Right now Wrap the lookup in IFNA to display a friendly message: =IFNA(VLOOKUP(A2, Table, 2, FALSE), "Not found"). For XLOOKUP, use the built-in 4th argument: =XLOOKUP(A2, IDs, Names, "Not found"). If you need the actual value, use TRIM to strip trailing spaces from both the search value and the lookup column.
Prevention — Forever Always wrap lookups in IFNA (not IFERROR — see IFERROR vs IFNA). Standardize your data with TRIM at import time. Use structured references with Excel Tables to catch type mismatches early.

Full details: #N/A error page.

2
Very Common Reference
#REF! Broken Reference

Formula points at a cell that no longer exists

#REF! is Excel's way of saying "the cell you referenced isn't there anymore." It usually appears after you delete rows, columns, or sheets that a formula was referencing.

Diagnosis — What triggered it You (or a colleague) deleted a row, column, worksheet, or workbook that a formula was pointing at. VLOOKUP's column index is also a common source — if the target column moves outside the range, you get #REF!.
Fix — Right now If it just happened, Ctrl+Z to undo the deletion. If not, open the formula and identify where #REF! appears — that's where the broken reference is. Replace with the correct cell reference. If a table was deleted, you may need to recreate it or point the formula at a different source.
Prevention — Forever Use structured references (Excel Tables) instead of hardcoded ranges like A1:A100. Tables adapt to added/removed rows automatically. Use named ranges instead of absolute references for anything that might move. Enable Sheet Protection on lookup tables that shouldn't be modified.

Full details: #REF! error page.

3
Very Common Syntax
#NAME? Unrecognized Function or Name

Excel doesn't recognize something in your formula

#NAME? means Excel doesn't understand a name in your formula — usually a misspelled function name (VLOKUP instead of VLOOKUP), text without quotes, or a function that requires a newer Excel version than you have.

Diagnosis — What triggered it Most common: misspelled function name. Second most common: forgetting quotes around text (=IF(A1>10,High,Low) instead of =IF(A1>10,"High","Low")). Third: using a function like XLOOKUP, FILTER, or LAMBDA in an Excel version that doesn't support it.
Fix — Right now Click the cell and check spelling of every function name. Add quotes around any text literals. If the function is spelled correctly but still fails, check Excel version compatibility — XLOOKUP, FILTER, UNIQUE, SORT, LAMBDA require Excel 365 or 2021; IFS, TEXTJOIN require Excel 2019+.
Prevention — Forever Type function names in lowercase — Excel auto-capitalizes them if they're valid. If your typed name stays lowercase, it's misspelled or unavailable. Use IntelliSense (the dropdown as you type) to pick functions instead of typing them from memory.

Full details: #NAME? error page.

4
Very Common Data Type
#VALUE! Wrong Data Type

Formula got the wrong type of input

#VALUE! means Excel got a data type it wasn't expecting — trying to do math on text, adding a date to a text label, or passing a range where a single value was expected. This is one of Excel's most common errors, especially with imported data.

Diagnosis — What triggered it Common causes: trying to sum a column containing text values, doing math on cells with non-breaking spaces (CHAR(160)), formulas expecting a number receiving text like "1,000" with a comma. Also appears when array formulas are used incorrectly.
Fix — Right now Identify the offending cell using Evaluate Formula (Formulas → Evaluate Formula) or Trace Precedents. Convert text-looking-like-numbers using VALUE() or a paste-special multiply-by-1 trick. Wrap ambiguous inputs in IFERROR to handle gracefully: =IFERROR(A1*B1, 0).
Prevention — Forever Clean imported data with TRIM + SUBSTITUTE(A1, CHAR(160), " ") to remove hidden characters. Use Text-to-Columns to force number conversion. Apply data validation to input cells to enforce numeric input.

Full details: #VALUE! error page.

5
Very Common Math
#DIV/0! Division by Zero

Formula tried to divide by zero or blank

#DIV/0! is exactly what it looks like — a division problem. Either you're dividing by 0 explicitly, or (more commonly) dividing by a cell that's blank or contains 0. Extremely common in financial models where per-unit calculations happen before units are entered.

Diagnosis — What triggered it Any formula with / where the divisor is 0 or an empty cell. Common in metrics like conversion rate (conversions/visits), average order value (revenue/orders), or margin (profit/revenue) where the denominator can legitimately be 0.
Fix — Right now Wrap the division in IFERROR to return 0 or a friendly message: =IFERROR(A1/B1, 0). For more surgical handling, use IF: =IF(B1=0, 0, A1/B1). This preserves error visibility for genuinely unexpected divide-by-zero cases.
Prevention — Forever Always defensive-wrap divisions in metrics dashboards. Consider whether "0 divided by 0" should return 0 or a blank — different reports need different behavior. For financial models, some analysts prefer "N/A" text for cases where the ratio is meaningless.

Full details: #DIV/0! error page.

6
Occasional Math
#NUM! Invalid Numeric Operation

Excel got a number it can't handle

#NUM! appears when a formula produces an invalid number — square root of a negative, LOG of a negative, a value too large for Excel to represent, or a date beyond Excel's range. Less common than #VALUE! but usually harder to diagnose.

Diagnosis — What triggered it Common causes: SQRT() or LN() on a negative number, IRR() unable to converge on a solution, a date calculation producing a date before 1900 or after 9999, or a number exceeding Excel's ~1.8E+308 upper limit.
Fix — Right now Check inputs for values that shouldn't be there — negative amounts in a SQRT, dates outside valid range. For IRR that can't converge, provide a "guess" as the second argument: =IRR(cashflows, 0.1). Wrap in IFERROR if the invalid input is legitimate business logic.
Prevention — Forever Add data validation to input cells that must be positive. For IRR-style iterative calculations, always provide a reasonable guess parameter. Handle edge cases explicitly with IF before passing to math functions.

Full details: #NUM! error page.

7
Rare Syntax
#NULL! Empty Intersection

Two ranges don't intersect

#NULL! is the rarest Excel error. It appears when you use the space operator (Excel's intersection operator) between two ranges that don't actually intersect, or when you accidentally use a space instead of a comma or colon between arguments.

Diagnosis — What triggered it Almost always: a typo where you used a space instead of a comma. =SUM(A1:A10 B1:B10) tries to sum the intersection of two non-overlapping ranges. Should be =SUM(A1:A10, B1:B10) with a comma.
Fix — Right now Open the formula. Look for spaces between range arguments where commas should be. Replace them with commas (for multiple ranges) or colons (for continuous ranges). That's usually the entire fix.
Prevention — Forever Type comma between range arguments deliberately — don't rely on autocomplete positioning. Review your formulas visually before pressing Enter. This error rarely appears in mature workbooks because it's typically a fresh typo.

Full details: #NULL! error page.

8
Occasional Dynamic Array
#SPILL! Dynamic Array Blocked

Formula wants to spill but something is in the way

#SPILL! is a modern Excel error, introduced with dynamic arrays in Excel 365. It appears when a formula like FILTER, UNIQUE, or SORT tries to spill its results into surrounding cells, but those cells are already occupied.

Diagnosis — What triggered it The formula wants to spill (say) 20 rows of results but there's a value in one of those cells blocking it. Common when a UNIQUE or FILTER formula sits next to existing data. Also appears if the spill range hits merged cells.
Fix — Right now Click the cell with #SPILL!. Excel highlights the blocked spill range with a dashed border — that's the area the formula wanted to fill. Delete or move the blocking content. Unmerge any merged cells in the spill range.
Prevention — Forever Place dynamic-array formulas in areas with clear empty space below and to the right. Never merge cells in areas where dynamic arrays might spill. Use dedicated "output zones" for FILTER/UNIQUE results, kept clear of other content.

Full details: #SPILL! error page.

9
Rare Dynamic Array
#CALC! Calculation Problem

Dynamic array can't compute a result

#CALC! is the newest Excel error, also from the dynamic array era. It appears when Excel understands your formula but can't compute a meaningful result — most often when FILTER returns no matching rows and no fallback is specified.

Diagnosis — What triggered it Most common: =FILTER(range, condition) where the condition matches zero rows. Also appears with certain LAMBDA calculations that produce impossible results, or when nested dynamic arrays create circular calculation states.
Fix — Right now For FILTER, always supply the optional 3rd argument as a fallback: =FILTER(range, condition, "No matches"). This is FILTER's built-in "empty result" handler. For LAMBDA-based #CALC! errors, trace the LAMBDA logic step-by-step to find the invalid state.
Prevention — Forever Every FILTER formula in production should have a fallback value. Test with edge case inputs that should legitimately produce no matches. See FILTER vs AutoFilter for more filter patterns.

Full details: #CALC! error page.

10
Occasional Logic
Circular Ref Formula Refers to Itself

A formula references its own cell — directly or indirectly

Circular references aren't a standard error value like the others — Excel shows a warning dialog and displays 0 in the cell instead. They happen when a formula depends on its own result, creating an infinite loop that Excel refuses to calculate.

Diagnosis — What triggered it Direct: =A1+1 typed into cell A1. Indirect: A1 references B1, B1 references C1, C1 references A1. Excel shows a status bar warning: "Circular References" with a cell address. Formulas → Error Checking → Circular References lists all of them.
Fix — Right now Go to Formulas → Error Checking → Circular References. Click each listed cell to see it. Rewrite the formula to remove the loop. Sometimes the fix is trivial (typo pointing at wrong cell). Sometimes it requires restructuring the calculation into a linear flow.
Prevention — Forever Design calculations to flow in one direction — inputs feed intermediate values, which feed outputs. Avoid formulas that reference cells they logically depend on. For legitimate iterative calculations (like solver problems), Excel offers Enable Iterative Calculation in Options — but use it sparingly and document heavily.

Full details: Circular reference page.


Error frequency in real-world workbooks

Not all errors happen equally often. In analysis of thousands of production workbooks, this is the rough distribution of error frequencies:

ErrorFrequencyRoot cause type
#N/A~35% of all errorsData problem (missing values)
#REF!~20%Structural problem (deleted references)
#VALUE!~15%Data type problem
#DIV/0!~12%Math problem (zero denominators)
#NAME?~8%Formula syntax problem
#SPILL!~5%Layout problem (modern Excel)
Circular~3%Logic problem
#NUM!~1%Numeric overflow / invalid math
#CALC!~0.5%Dynamic array state problem
#NULL!~0.5%Syntax typo (space vs comma)

The top 4 errors (#N/A, #REF!, #VALUE!, #DIV/0!) cause over 80% of real-world Excel error debugging time. Master those four and you'll handle the vast majority of problems that arise.

The error-handling philosophy

Beyond individual errors, there's a broader question: how should you handle errors when they appear in production formulas? Two schools of thought:

Hide all errors with IFERROR. Wrap every risky formula in IFERROR with a friendly fallback. Users never see error codes. Dashboards always look "clean". The problem: this hides genuine bugs. A deleted lookup table shows up as blank cells across your dashboard, and nobody notices until reports are wrong.

Handle errors surgically with IFNA and specific IF checks. Only catch the errors you specifically expect. Let unexpected errors surface as red flags. The advantage: real bugs get caught immediately when they appear. The tradeoff: dashboards occasionally show error codes to end users when data is broken.

The professional pattern

Use IFNA for lookups where "not found" is legitimate business logic. Use IFERROR only for calculations where any error genuinely should be replaced with a default (like division-by-zero in KPIs). Never blanket-wrap every formula in IFERROR — that trades short-term dashboard cleanliness for long-term data reliability. See IFERROR vs IFNA for the full decision framework.

The complete error-handling toolkit

Once you've identified an error, these are the functions and features that help you handle them:

ToolWhat it doesWhen to use
IFNACatches only #N/ALookups where missing values are expected
IFERRORCatches ALL errorsCalculations with any expected failure
ISERROR()Returns TRUE if any errorTesting for errors in conditional logic
ISNA()Returns TRUE only for #N/ALegacy pattern before IFNA (Excel 2010 and older)
Formulas → Evaluate FormulaStep through formula calculationComplex formulas where the error source isn't obvious
Formulas → Trace PrecedentsVisual arrows to source cellsTracing where broken references originate
Formulas → Error CheckingLists all errors in workbookAuditing a workbook before delivery
Data → Data ValidationPrevents bad input at sourceStopping errors before they happen

Errors in dynamic arrays (Excel 365)

Modern Excel introduced two new errors specifically for dynamic-array functions: #SPILL! and #CALC!. These weren't in older Excel because dynamic arrays didn't exist. If you're using FILTER, UNIQUE, SORT, or XLOOKUP heavily, you'll encounter these more often.

Both are usually easy fixes:

#SPILL! almost always means "clear the blocking cells". Excel even shows you exactly which cells are blocking with a dashed border.

#CALC! almost always means "add a fallback to your FILTER formula". Use the 3rd argument: =FILTER(range, condition, "No matches").

Excel version matters for error diagnosis

If you see #NAME? on a formula that works fine for your colleague, check Excel versions. XLOOKUP, FILTER, UNIQUE, LAMBDA all require Excel 365 or 2021. Opening a workbook with these functions in Excel 2019 produces #NAME? where the modern functions should be — because those functions don't exist in older versions.

Prevention beats fixes — 5 habits

Every error is fixable, but preventing them saves more time than fixing them. Five habits that dramatically reduce error frequency:

1. Use Excel Tables (Ctrl+T) instead of raw ranges. Tables adapt to added/removed rows automatically, eliminating most #REF! errors caused by structural changes.

2. Wrap risky formulas surgically. IFNA for lookups. IFERROR for divisions. Nothing for calculations that shouldn't fail — let real errors surface.

3. Clean data at import. TRIM + SUBSTITUTE for text, VALUE() or Text-to-Columns for numbers. Cleaning at the source prevents #VALUE! and #N/A errors downstream.

4. Use data validation for input cells. Restrict cells to specific data types or value ranges. Prevents users from typing text where numbers are expected.

5. Document version requirements. If your workbook uses modern functions like XLOOKUP or FILTER, note this in a "README" cell. Prevents colleagues on older Excel versions from being surprised by #NAME? errors.

Related resources

Frequently asked questions

How many types of errors are there in Excel?

Excel has 9 formula error values plus circular reference warnings. The 9 error values are #N/A, #REF!, #NAME?, #VALUE!, #DIV/0!, #NUM!, #NULL!, #SPILL!, and #CALC!. Each signals a different problem — from a missing lookup value to a broken cell reference to a formula that cannot calculate. Understanding what each error means is the first step to fixing them.

What causes #N/A error in Excel?

#N/A means "not available" and typically appears when a lookup function like VLOOKUP or XLOOKUP cannot find a matching value. Common causes include: the search value does not exist in the lookup table, there are trailing spaces creating mismatches, or the data types differ (like text vs numbers that look identical). Wrap the lookup in IFNA to display a friendly message instead.

How do I fix #REF! error in Excel?

#REF! means the formula references a cell that no longer exists — usually because rows, columns, or sheets were deleted. Fix by using Ctrl+Z to undo the deletion if it just happened, or by rewriting the formula with correct references. To prevent it, use structured references or named ranges instead of hardcoded cell addresses which are more resilient to structural changes.

What does #NAME? error mean in Excel?

#NAME? means Excel does not recognize something in your formula — usually a misspelled function name, an unquoted text string, or a function that requires a newer Excel version. For example, using XLOOKUP in Excel 2019 returns #NAME? because that function was added in Excel 365. Check spelling first, then check version compatibility.

Why do I get #DIV/0 error?

#DIV/0! appears when a formula tries to divide by zero or by a blank cell. Common causes: dividing revenue by units when units is 0, or by a cell that has not been filled in yet. Wrap the division in IFERROR to display 0 or a friendly message instead: =IFERROR(A1/B1, 0). This is one of the most common errors in financial models.

What is the difference between #N/A and #NAME? errors?

#N/A means a lookup found no matching value — the formula is correct but the data does not have what you searched for. #NAME? means Excel does not recognize your formula syntax — usually a misspelled function or a function unavailable in your Excel version. #N/A is a data problem; #NAME? is a formula problem. They have completely different causes and fixes.

How do I prevent Excel errors from breaking my dashboard?

Use surgical error handling instead of hiding all errors. Wrap lookups in IFNA to catch only expected missing values. Use IFERROR only for calculations where any error should be replaced with a default. Never blanket-wrap every formula in IFERROR — that hides real bugs. Also use data validation to prevent bad input at the source, structured references to survive column changes, and named ranges for resilience.

What are #SPILL! and #CALC! errors?

Both are modern Excel errors introduced with dynamic arrays in Excel 365. #SPILL! appears when a dynamic-array formula like FILTER or UNIQUE can't spill its results because other content is blocking the target cells. #CALC! appears when a dynamic-array formula can't compute a meaningful result, most often when FILTER returns no matches. Fix #SPILL! by clearing the blocking cells. Fix #CALC! by providing a fallback with FILTER's 3rd argument.

Can Excel automatically detect and fix errors?

Partially. Excel shows a yellow warning triangle next to error cells with a dropdown suggesting fixes for common cases. Formulas → Error Checking scans the entire workbook and lists all errors with suggested corrections. However, Excel cannot fix data-source problems automatically — if a lookup table is missing a value, Excel can only flag the #N/A, not restore the missing data.

· · ·

Bottom line: Every Excel error has a specific meaning and a specific fix. Bookmark this page. When an error appears, jump to its section, follow the 3-part Diagnosis/Fix/Prevention pattern, and move on. Master these 10 and you'll never lose an hour to Excel debugging again.