What does #N/A mean in Excel?

The #N/A error means 'Not Available'—Excel searched for a value but couldn't find it. This commonly occurs in VLOOKUP, XLOOKUP, and MATCH functions when the lookup value doesn't exist in the data, or when there are extra spaces or data type mismatches.

How do I hide errors in Excel?

Use IFERROR to replace errors with a custom value: =IFERROR(your_formula, "Not Found"). For #N/A specifically, use IFNA. You can also use conditional formatting to change the font color to white, or use the ISERROR function in an IF statement.

Why does Excel show #REF! error?

The #REF! error appears when a formula refers to a cell that no longer exists—usually because rows or columns were deleted. It also occurs when VLOOKUP's column index exceeds the table range. Undo the deletion or rebuild the formula with correct references.

What causes #VALUE! error in Excel?

The #VALUE! error occurs when Excel receives the wrong data type—like text where it expects a number. Common causes include spaces in cells that look empty, text formatted as numbers, or incompatible array sizes. Use TRIM and VALUE functions to clean data.

How do I fix #NAME? error in Excel?

The #NAME? error means Excel doesn't recognize something in your formula. Check for misspelled function names, missing quotation marks around text, undefined named ranges, or functions not available in your Excel version. Double-check spelling and syntax.

What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Tutorials - 2026-01-21

Common Excel Errors Explained (And How to Fix Them)

Stop panicking when you see Excel errors. This comprehensive guide explains every error type, why it happens, and exactly how to fix it.

Why Excel Shows Errors

Excel errors aren't failures—they're messages. Each error code tells you exactly what went wrong. Once you understand them, fixing formulas becomes straightforward. This guide covers every common Excel error with real-world examples and solutions. For quick reference, see our Error Reference Guide.

The 7 Excel Error Types

Error Meaning Most Common Cause #N/A Not Available Lookup value not found #VALUE! Wrong Value Type Text where number expected #REF! Invalid Reference Deleted cells #NAME? Unrecognized Name Misspelled function #DIV/0! Division by Zero Dividing by empty cell #NUM! Invalid Number Number too large/small #NULL! Null Intersection Wrong range operator

#N/A — Value Not Found

The most common error in lookup functions. What it means: Excel searched for something and couldn't find it. Common causes: Lookup value doesn't exist in the data Extra spaces in cells Different data types (text "123" vs number 123) Case sensitivity issues Solutions: \\\ ' Wrap in IFERROR =IFERROR(VLOOKUP(A1, Data, 2, FALSE), "Not Found") ' Use XLOOKUP with built-in handling =XLOOKUP(A1, LookupRange, ReturnRange, "Not Found") ' Clean data with TRIM =VLOOKUP(TRIM(A1), Data, 2, FALSE) \\\ Full #N/A guide → Error Reference →

#VALUE! — Wrong Data Type

Excel expected one type of data but got another. What it means: You're mixing incompatible data types. Common causes: Text in a math formula Spaces that look empty but aren't Array formula issues Date/time format problems Solutions: \\\ ' Convert text to number =A1 * 1 =VALUE(A1) ' Remove hidden characters =TRIM(CLEAN(A1)) ' Check for spaces =LEN(A1) ' Compare to expected length \\\ Full #VALUE! guide → Error Reference →

#REF! — Invalid Reference

A cell reference is no longer valid. What it means: Excel can't find cells your formula references. Common causes: Deleted rows or columns Cut and paste breaking references Invalid external workbook links Circular references Solutions: \\\ ' Prevention: Use Tables (references adjust automatically) =SUM(Table1[Sales]) ' Fix: Undo the deletion (Ctrl+Z) ' Or rebuild the formula with correct references \\\ Full #REF! guide → Error Reference →

#NAME? — Unrecognized Name

Excel doesn't recognize something in your formula. What it means: There's a typo or missing definition. Common causes: Misspelled function name Missing quotes around text Undefined named range Regional settings (semicolons vs commas) Solutions: \\\ ' Check spelling =SUMM(A1:A10) ' Wrong: SUMM =SUM(A1:A10) ' Correct: SUM ' Add quotes around text =IF(A1=Hello, "Yes", "No") ' Wrong =IF(A1="Hello", "Yes", "No") ' Correct ' Define named ranges ' Formulas → Name Manager → New \\\ Error Reference →

#DIV/0! — Division by Zero

You're dividing by zero or an empty cell. What it means: Math doesn't allow division by zero. Common causes: Divisor cell is empty Divisor formula returns 0 Average of empty range Solutions: \\\ ' Check before dividing =IF(B1=0, 0, A1/B1) ' Use IFERROR =IFERROR(A1/B1, 0) ' For averages, use AVERAGEIF to exclude zeros =AVERAGEIF(A:A, "<>0") \\\ Error Reference →

#NUM! — Invalid Number

The result is too large, too small, or impossible. What it means: Excel can't calculate a valid number. Common causes: Number exceeds Excel's limits Impossible calculation (square root of negative) IRR/RATE can't find solution Iteration limits exceeded Solutions: \\\ ' Check for negative numbers in SQRT =IF(A1<0, "Invalid", SQRT(A1)) ' For IRR, provide a guess =IRR(CashFlows, 0.1) ' Break large calculations into steps \\\ Error Reference →

#NULL! — Null Intersection

Two ranges don't intersect. What it means: You used a space (intersection operator) between ranges that don't overlap. Common causes: Accidental space in formula Intentional intersection that doesn't exist Solutions: \\\ ' Wrong: space between ranges =SUM(A1:A10 B1:B10) ' Correct: comma to include both =SUM(A1:A10, B1:B10) ' Correct: colon for continuous range =SUM(A1:B10) \\\ Error Reference →

Error Handling Best Practices

Use IFERROR Strategically \\\ =IFERROR(YourFormula, "Error message") =IFERROR(VLOOKUP(...), "Not found") \\\ Warning: IFERROR hides ALL errors. Use IFNA for lookups to catch only #N/A. Use IFNA for Lookups \\\ =IFNA(XLOOKUP(...), "Not found") \\\ Only catches #N/A, letting other errors show (which you want for debugging). Validate Data Before Formulas \\\ =IF(ISBLANK(A1), "Missing data", YourFormula) =IF(ISNUMBER(A1), A1*2, "Not a number") \\\ Use Data Validation Prevent errors by restricting input: Data → Data Validation Set allowed values, ranges, or lists

Debugging Formulas

Step 1: Evaluate Formula Select cell → Formulas → Evaluate Formula Watch Excel calculate step by step. Step 2: Check Precedents Formulas → Trace Precedents See which cells feed into your formula. Step 3: Check Dependents Formulas → Trace Dependents See which cells use your formula. Step 4: Use F9 to Evaluate Parts Select part of formula in formula bar → Press F9 See the value of just that part.

Quick Reference

Error Quick Fix #N/A Check lookup value exists, use TRIM #VALUE! Check data types, remove spaces #REF! Undo deletion, rebuild formula #NAME? Check spelling, add quotes #DIV/0! Check divisor isn't zero/empty #NUM! Check number limits and validity #NULL! Replace space with comma

Why Excel Shows #VALUE! Error How to Fix #REF! Error in Excel #N/A Error: Causes and Solutions Excel Formula Errors Explained Simply Error Reference Guide Excel Functions: Complete Guide

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons

Frequently asked questions

What does #N/A mean in Excel? The #N/A error means 'Not Available'—Excel searched for a value but couldn't find it. This commonly occurs in VLOOKUP, XLOOKUP, and MATCH functions when the lookup value doesn't exist in the data, or when there are extra spaces or data type mismatches.
How do I hide errors in Excel? Use IFERROR to replace errors with a custom value: =IFERROR(your_formula, "Not Found"). For #N/A specifically, use IFNA. You can also use conditional formatting to change the font color to white, or use the ISERROR function in an IF statement.
Why does Excel show #REF! error? The #REF! error appears when a formula refers to a cell that no longer exists—usually because rows or columns were deleted. It also occurs when VLOOKUP's column index exceeds the table range. Undo the deletion or rebuild the formula with correct references.
What causes #VALUE! error in Excel? The #VALUE! error occurs when Excel receives the wrong data type—like text where it expects a number. Common causes include spaces in cells that look empty, text formatted as numbers, or incompatible array sizes. Use TRIM and VALUE functions to clean data.
How do I fix #NAME? error in Excel? The #NAME? error means Excel doesn't recognize something in your formula. Check for misspelled function names, missing quotation marks around text, undefined named ranges, or functions not available in your Excel version. Double-check spelling and syntax.
What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.