That red triangle in the corner of your cell. The green arrow. The cryptic text starting with #. Excel formula errors look intimidating, but they're actually diagnostic tools telling you exactly what went wrong.Every error code in Excel tells you something specific. Once you know what it means, the fix is usually straightforward. This guide walks through each error type so you never stare at a red cell wondering what to do.
Quick Reference Table
Error
Meaning
Most Common Cause
#DIV/0!
Dividing by zero
Blank cell in divisor
#N/A
Value not found
Lookup mismatch
#VALUE!
Wrong data type
Text where number expected
#REF!
Invalid reference
Deleted cell, row, or column
#NAME?
Name not recognized
Typo in function name
#NULL!
Incorrect range intersection
Typo in range operator
#SPILL!
Array can't expand
Obstruction in spill range
Each section below shows you what causes each error and how to fix it, with a real example to work from.
#DIV/0! — Division by Zero
What it means: A formula is trying to divide by zero or an empty cell. Excel can't perform division when the divisor is zero — mathematically undefined.Most common scenario: You have a sales table with a "Margin %" column. Some rows have a blank or zero in the cost column.Fix 1 — Check the divisor cell:
=IF(B2=0, "", A2/B2)
This checks if B2 is zero first. If it is, the cell stays blank. Otherwise, it performs the division.Fix 2 — Use IFERROR for cleanup after the fact:
=IFERROR(A2/B2, "")
This returns a blank for any error, not just #DIV/0!. Use it when you know all errors in this context should be hidden.Workaround — Zero-based divisor with a minimum:
=A2/MAX(B2, 0.001)
This replaces zero with 0.001, avoiding the division error. Use with caution — it introduces a small distortion in the result. Only appropriate when approximate margins are acceptable.
#N/A — Value Not Found
What it means: A lookup function (VLOOKUP, XLOOKUP, INDEX-MATCH, HLOOKUP) cannot find the lookup value in the source data.Most common scenario: You're looking up an employee ID in a table, but the ID doesn't exist — or it has extra spaces.Fix 1 — Use IFNA for clean fallback:
=IFNA(XLOOKUP(D2, A:A, B:B), "Not found")
XLOOKUP's if_not_found parameter handles this more elegantly:
=XLOOKUP(D2, A:A, B:B, "Not found")
See the XLOOKUP Complete Guide for more on the if_not_found parameter and exact match behavior.Fix 2 — Check for exact match mode:VLOOKUP defaults to approximate match (TRUE). A common source of unexpected #N/A:
=VLOOKUP(D2, A:B, 2, FALSE)
The FALSE forces an exact match. Without it, VLOOKUP returns #N/A if the data isn't sorted.Fix 3 — Clean the data first:Trailing spaces cause false mismatches. Clean lookup values:
=XLOOKUP(TRIM(D2), TRIM(A:A), B:B, "Not found")
Workaround — Use IF and ISNA for older Excel versions:
What it means: A formula expects a number but receives text — or vice versa. Excel can't perform arithmetic on text.Most common scenario: SUM of cells formatted as text, date math with text-formatted dates, or a formula referencing a cell with unexpected characters.Fix 1 — Check cell formatting:Cells with green triangles in the corner have numbers stored as text. Select the cells and click the warning icon → Convert to Number.Fix 2 — Use VALUE() to convert text numbers:
=SUM(VALUE(A2:A10))
Enter as an array formula in older Excel versions. In Excel 365, VALUE converts each cell automatically inside array formulas.Fix 3 — Remove hidden characters:Imported data often carries invisible characters. Use Data > Text to Columns (delimited, finish) to clean imported data.Workaround — IFERROR plus conversion:
=IFERROR(SUM(VALUE(A2:A10)), "Check for text in range")
#REF! — Invalid Cell Reference
What it means: A formula references a cell, row, or column that has been deleted.Most common scenario: You delete a column that a VLOOKUP or SUM formula references. The formula breaks instantly.Fix 1 — Undo immediately:If you just deleted the cell, press Ctrl + Z (see the Excel Keyboard Shortcuts Guide for more undo and navigation shortcuts).Fix 2 — Replace the #REF! reference:In the formula bar, replace each #REF! with the correct cell reference:
=SUM(A2:A10) ' If B2:B10 was accidentally deleted, UPDATE reference
Fix 3 — Use INDIRECT for stable references (advanced):
=SUM(INDIRECT("A2:A10"))
INDIRECT stores the range as text, so it survives row and column deletions. Use sparingly — INDIRECT is volatile and recalculates on every change.Prevention — Use structured references with Excel Tables:Convert your range to a Table (Ctrl + T). Table references like Table1[Amount] survive row additions and deletions because they reference the column by name, not by position.
#NAME? — Typo or Undefined Name
What it means: Excel doesn't recognize a function name or named range. Usually a typo.Most common scenarios:
Typo: VLOKUP(A2, B:C, 2, FALSE) instead of VLOOKUP
Omitted quotes: =IF(A2>100, Large, Small) instead of =IF(A2>100, "Large", "Small")
Missing named range: referencing Region but the name doesn't exist in Name Manager
Fix 1 — Check spelling:Verify the function name against Excel's formula autocomplete.Fix 2 — Check quotes:All text values in formulas must be in double quotes:
=IF(A2>100, "Large", "Small") ' ✅ Correct=IF(A2>100, Large, Small) ' ❌ Excel reads Large as a named range
Fix 3 — Check Name Manager:Go to Formulas > Name Manager (Ctrl + F3) to verify all named ranges exist. The Data Validation Dropdown Guide covers setting up named ranges for dropdown lists, which is a common source of #NAME? errors.
#NULL! — Range Intersection Error
What it means: A formula uses the space operator (range intersection) incorrectly. In Excel, a space between two ranges means "return the intersection" — cells that belong to both ranges.Most common scenario: You meant to use a comma (union) but used a space instead.
=SUM(A2:A10 B2:B10) ' ❌ Space = intersection — almost never what you want=SUM(A2:A10, B2:B10) ' ✅ Comma = union — sums both ranges
Fix — Check range operators:
Operator
What It Does
Example
, (comma)
Union — combines ranges
SUM(A2:A10, C2:C10)
: (colon)
Range — continuous cells
A2:A10
(space)
Intersection — shared cells
A2:B10 A5:C15 (returns A5:B10)
Unless you specifically need intersection, use commas between range arguments.
#SPILL! — Dynamic Array Obstruction
What it means: A dynamic array formula (FILTER, SORT, UNIQUE) wants to spill results into adjacent cells, but those cells already contain data.Most common scenario: You write =FILTER(A2:A100, B2:B100>1000) in cell D2, but D3, D4, or any cell in the spill range has existing content.Fix 1 — Clear the spill range:Select the cell with the formula. Excel highlights the spill range with a blue border. Clear everything inside that border.Fix 2 — Move existing data:If the spill range overlaps with intended data, move your formula to a location with empty space below and to the right.Fix 3 — Use implicit intersection (@):
=@FILTER(A2:A100, B2:B100>1000)
The @ forces a single-cell result (the first matching row). This defeats the purpose of dynamic arrays but avoids the #SPILL! error when spill space is limited.Context — June 2026 PivotTable Update:Microsoft's June 2026 update improved how PivotTables interact with dynamic array formulas. Spill-aware PivotTables now handle #SPILL! references more gracefully. See the Dynamic Array Functions Guide for complete coverage of spill behavior and troubleshooting.
Graceful Error Handling
You don't have to stare at error cells. Excel gives you functions to catch them and decide what to show instead.
IFERROR — Catch Everything
=IFERROR(your_formula, "Fallback value")
Returns the fallback for any error. Clean and aggressive — but it hides all errors, even ones you might want to investigate.
IFNA — Catch Only #N/A
=IFNA(your_formula, "Not found")
Returns the fallback only for #N/A errors. Other errors (like #REF! or #VALUE!) still show up, alerting you to problems you should fix.
This first checks for zero divisor (returns blank), then performs the lookup with IFNA fallback. Specific, clean, and debuggable.The IF, Nested IF, and IFS Guide covers more conditional logic patterns for error handling.
Preventive Best Practices
The single best thing you can do is convert your ranges to Tables (Ctrl + T). Table references like Table1[Amount] survive structural changes that break regular cell references — and they're self-documenting. The Data Validation Dropdown Guide shows how to build validation rules around Table references for error-resistant workflows.Catch problems at the entry point before your formulas ever see bad data. Data Validation restricts what goes into a cell, so you don't end up with text where a number belongs. That alone eliminates most #VALUE! errors.And test formulas on a small sample before rolling them across 10,000 rows. Spot the error pattern in ten rows, fix it, then expand the range. A quick sanity check saves hours of debugging.When copying formulas, absolute references ($A$1) prevent #REF! errors from shifting references. Named ranges also help — they lock a reference to a specific range regardless of where the formula sits.
AI Prompt to Troubleshoot Formula Errors
When you're stuck on an error and the fix isn't obvious, try pasting this into your AI assistant:
I have an Excel formula returning [ERROR TYPE] in cell [CELL].My formula: [PASTE FORMULA]My data: [DESCRIBE DATA IN THE INPUT CELLS]What I expected: [DESCRIBE EXPECTED RESULT]What is the most likely cause and how do I fix it?
Example:
I have an Excel formula returning #VALUE! in cell D2.My formula: =A2*B2My data: A2 contains "5.5" (green triangle), B2 contains 10What I expected: 55What is the most likely cause and how do I fix it?
The AI should identify that A2 is text-formatted and suggest converting to number. Try the fix on one cell first before expanding to the full column.
Why does VLOOKUP return #N/A when the value clearly exists?Most common causes: (1) The lookup value has trailing spaces — use TRIM(). (2) The lookup value is a number but the source is text (or vice versa). (3) You're using approximate match (TRUE) with unsorted data — use FALSE for exact match.What's the difference between IFERROR and IFNA?IFERROR catches ALL errors (including #REF!, #VALUE!, #DIV/0!, #NAME?). IFNA catches only #N/A errors. Use IFNA when you want to silently handle "not found" lookups but still see other errors.Can I prevent #REF! errors when deleting columns?Use INDIRECT for critical references, or convert your data to Excel Tables. Table references (Table1[Column]) survive column deletions because they reference the column name, not the position.Why does my formula show #NAME? when the function exists?Check the spelling and check for missing quotes. Text values in formulas must be in double quotes — "Large" not Large. Also verify the function exists in your Excel version.How do I fix #SPILL! without deleting data?Move your formula to a location with enough empty space. Or use implicit intersection (@) to force single-cell output. Or reference a smaller range — FILTER(A2:A100, ...) instead of FILTER(A:A, ...).
Topics
Topics in this article
Explore related topics and continue reading similar content.