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

Excel Formula Errors Explained Simply

New to Excel? Here's a beginner-friendly guide to understanding and fixing formula errors.

Excel Errors: A Beginner's Guide

Don't panic when you see an error! Excel is just telling you something needs fixing. Think of errors as helpful messages, not scary warnings. Here's what each error means in plain English—and exactly how to fix them. For detailed solutions, see Common Excel Errors Explained.

Understanding Excel Errors

Before we dive into specific errors, here's what you need to know: Errors are NOT your fault. They happen to everyone, even Excel experts. The key is knowing what they mean and how to fix them. Errors are specific. Each error code tells you exactly what went wrong. Once you learn the codes, you'll fix problems in seconds. Errors can be hidden. If you don't want users to see errors, you can display friendly messages instead (we'll show you how).

The Complete Error Cheat Sheet

Error Plain English Most Common Fix #N/A "Can't find it" Check spelling, remove spaces #VALUE! "Wrong type of data" Convert text to numbers #REF! "Cell was deleted" Undo or rebuild formula #NAME? "Don't recognize that" Check spelling, add quotes #DIV/0! "Can't divide by zero" Check divisor isn't zero #NUM! "Number doesn't work" Check inputs are valid #NULL! "Ranges don't overlap" Use comma, not space ##### "Column too narrow" Widen the column

#N/A — "I Can't Find It"

What Excel is saying: "You asked me to look something up, but I couldn't find it anywhere." When you'll see it: Mostly with lookup functions like VLOOKUP, XLOOKUP, MATCH, and INDEX. Real Example You have a product list and want to find the price for "Apple": \\\ ' Your data: ' A1: Orange B1: $2.00 ' A2: Banana B2: $1.50 ' A3: Grape B3: $3.00 ' Your formula: =VLOOKUP("Apple", A1:B3, 2, FALSE) ' Result: #N/A (because "Apple" isn't in the list!) \\\ Common Causes The value doesn't exist — You're looking for something that's not there Spelling differences — "Apple" vs "apple" vs "APPLE" Extra spaces — "Apple " (with trailing space) ≠ "Apple" Different data types — Looking for number 123 but cell contains text "123" How to Fix It Fix 1: Check if the value exists \\\ ' Use COUNTIF to verify =COUNTIF(A:A, "Apple") ' If result is 0, the value doesn't exist \\\ Fix 2: Remove extra spaces \\\ =VLOOKUP(TRIM(A1), Data, 2, FALSE) ' TRIM removes leading/trailing spaces \\\ Fix 3: Handle the error gracefully \\\ =IFERROR(VLOOKUP("Apple", A1:B3, 2, FALSE), "Not found") ' Shows "Not found" instead of #N/A \\\ Fix 4: Use XLOOKUP (has built-in error handling) \\\ =XLOOKUP("Apple", A1:A3, B1:B3, "Not found") ' The fourth argument is what to show if not found \\\ Learn more: #N/A Error: Causes and Solutions

#VALUE! — "That's the Wrong Type"

What Excel is saying: "You gave me text when I needed a number, or vice versa." When you'll see it: Math operations, date calculations, or when mixing data types. Real Example \\\ ' Cell A1 contains the text "Ten" (not the number 10) =A1 * 2 ' Result: #VALUE! (can't multiply text!) ' Cell B1 looks like a number but is actually text =B1 + 100 ' Result: #VALUE! \\\ Common Causes Text in math formulas — Trying to add/multiply text Hidden spaces — Cell looks empty but contains a space Text-formatted numbers — Numbers stored as text (often from imports) Non-printable characters — Invisible characters from copy/paste How to Fix It Fix 1: Convert text to number \\\ =VALUE(A1) ' Converts text "100" to number 100 ' Or multiply by 1: =A1 * 1 ' Forces conversion to number \\\ Fix 2: Clean the data \\\ =VALUE(TRIM(CLEAN(A1))) ' CLEAN removes non-printable characters ' TRIM removes extra spaces ' VALUE converts to number \\\ Fix 3: Check if it's a number first \\\ =IF(ISNUMBER(A1), A1 * 2, "Not a number") \\\ Fix 4: Handle the error \\\ =IFERROR(A1 * 2, 0) ' Returns 0 if there's an error \\\ Quick Test: Is It Text or Number? \\\ =ISNUMBER(A1) ' TRUE if number, FALSE if text =ISTEXT(A1) ' TRUE if text, FALSE if number =LEN(A1) ' Shows character count (helps find hidden spaces) \\\ Learn more: Why Excel Shows #VALUE! Error

#REF! — "That Cell is Gone"

What Excel is saying: "You deleted a cell I was using in my formula!" When you'll see it: After deleting rows, columns, or cells that formulas reference. Real Example \\\ ' Original formula in D1: =A1 + B1 + C1 ' You delete column B ' Formula becomes: =A1 + #REF! + B1 ' The whole cell shows #REF! \\\ Common Causes Deleted rows or columns — Most common cause Cut and paste — Moving cells that formulas reference Invalid VLOOKUP column — Asking for column 5 in a 3-column range Broken links — Reference to a deleted or moved file How to Fix It Fix 1: Undo immediately \\\ Ctrl+Z (Windows) or Cmd+Z (Mac) ' Works if you catch it quickly \\\ Fix 2: Find all #REF! errors \\\ Ctrl+F → Type: #REF! → Find All ' Shows every cell with this error \\\ Fix 3: Rebuild the formula \\\ ' Look at the formula bar ' Replace #REF! with the correct cell reference \\\ Prevention Tips Use Excel Tables (Ctrl+T) — references adjust automatically Use named ranges — more resilient than cell references Be careful with Cut (Ctrl+X) — use Copy (Ctrl+C) instead Learn more: How to Fix #REF! Error in Excel

#NAME? — "I Don't Recognize That"

What Excel is saying: "You typed something I don't understand—maybe a misspelled function or missing quotes." When you'll see it: Typos in function names, missing quotes around text, or undefined names. Real Examples \\\ ' Misspelled function: =SUMM(A1:A10) ' Result: #NAME? (should be SUM, not SUMM) ' Missing quotes around text: =IF(A1=Hello, "Yes", "No") ' Result: #NAME? (Hello needs quotes) ' Correct version: =IF(A1="Hello", "Yes", "No") \\\ Common Causes Misspelled function name — SUMM instead of SUM Missing quotes — Text without quotation marks Undefined named range — Using a name that doesn't exist Regional settings — Using comma instead of semicolon (or vice versa) How to Fix It Fix 1: Check function spelling \\\ ' Start typing and use AutoComplete ' Excel suggests functions as you type =SU... → Excel shows SUM, SUMIF, SUMIFS, etc. \\\ Fix 2: Add quotes around text \\\ ' Wrong: =IF(A1=Apple, "Yes", "No") ' Right: =IF(A1="Apple", "Yes", "No") \\\ Fix 3: Check named ranges \\\ ' Go to Formulas → Name Manager ' See all defined names ' Delete or fix undefined ones \\\ Fix 4: Check regional settings \\\ ' Some countries use semicolon instead of comma: =IF(A1>10, "Big", "Small") ' US/UK =IF(A1>10; "Big"; "Small") ' Some European countries \\\

#DIV/0! — "I Can't Divide by Zero"

What Excel is saying: "You're asking me to divide by zero or an empty cell—that's mathematically impossible!" When you'll see it: Division formulas where the divisor is zero or blank. Real Example \\\ ' A1 = 100, B1 = 0 =A1/B1 ' Result: #DIV/0! (can't divide by zero) ' A1 = 100, B1 = (empty) =A1/B1 ' Result: #DIV/0! (empty cell = zero) \\\ How to Fix It Fix 1: Check before dividing \\\ =IF(B1=0, 0, A1/B1) ' If B1 is zero, return 0; otherwise divide =IF(B1=0, "N/A", A1/B1) ' If B1 is zero, show "N/A" \\\ Fix 2: Use IFERROR \\\ =IFERROR(A1/B1, 0) ' Returns 0 if there's any error =IFERROR(A1/B1, "Cannot calculate") ' Shows message if error \\\ Fix 3: Check for empty cells too \\\ =IF(OR(B1=0, B1=""), "No data", A1/B1) \\\ Common Scenarios Scenario Formula Result 100 ÷ 0 =100/0 #DIV/0! 100 ÷ (empty) =100/A1 #DIV/0! 100 ÷ 5 =100/5 20 AVERAGE of empty range =AVERAGE(A1:A10) #DIV/0!

#NUM! — "That Number Doesn't Work"

What Excel is saying: "The number you're using is invalid—too big, too small, negative when it shouldn't be, or just impossible." When you'll see it: Mathematical impossibilities or numbers outside Excel's limits. Real Examples \\\ ' Square root of negative number: =SQRT(-1) ' Result: #NUM! (can't have negative square root) ' Number too large: =1000^1000 ' Result: #NUM! (exceeds Excel's limit) ' Invalid IRR calculation: =IRR({-100, -50, -25}) ' Result: #NUM! (all negative = no valid return rate) \\\ How to Fix It Fix 1: Check for negative numbers \\\ =IF(A1<0, "Invalid", SQRT(A1)) ' Only calculate if positive \\\ Fix 2: Use ABS for absolute value \\\ =SQRT(ABS(A1)) ' Converts negative to positive first \\\ Fix 3: Validate inputs \\\ =IF(AND(A1>=0, A1<=1000000), YourFormula, "Out of range") \\\

#NULL! — "Those Ranges Don't Overlap"

What Excel is saying: "You used a space between two ranges, which means 'intersection,' but they don't intersect." When you'll see it: Accidentally using space instead of comma between ranges. Real Example \\\ ' Wrong (space between ranges): =SUM(A1:A10 B1:B10) ' Result: #NULL! (space means intersection) ' Right (comma between ranges): =SUM(A1:A10, B1:B10) ' Result: Sum of both ranges \\\ How to Fix It Fix: Use the correct separator \\\ ' To add multiple ranges, use comma: =SUM(A1:A10, B1:B10, C1:C10) ' To find intersection (rarely needed), use space: =SUM(A1:C10 B5:B15) ' Only sums cells that are in BOTH ranges \\\

##### — "Column Too Narrow"

What Excel is saying: "The result is too wide to display in this column." Note: This isn't technically an error—the calculation worked, you just can't see it. How to Fix It Fix 1: Widen the column Double-click the column border to auto-fit Or drag the border to make it wider Fix 2: Change number format \\\ ' Instead of showing 1,234,567,890 ' Use thousands: 1,234,568K ' Or millions: 1,235M \\\ Fix 3: Wrap text Home → Wrap Text (for text that's too long)

The Universal Fix: IFERROR and IFNA

When you want to hide errors and show something user-friendly: IFERROR — Catches ALL Errors \\\ =IFERROR(YourFormula, "Something went wrong") =IFERROR(A1/B1, 0) =IFERROR(VLOOKUP(A1, Data, 2, FALSE), "Not found") \\\ IFNA — Catches Only #N/A \\\ =IFNA(VLOOKUP(A1, Data, 2, FALSE), "Not in list") ' Only handles #N/A, other errors still show \\\ When to Use Each Situation Use Hide all errors IFERROR Only hide "not found" IFNA Need to see other errors IFNA Quick fix IFERROR

How to Debug Any Error

Step 1: Read the Error Code The error tells you what's wrong! Use the cheat sheet above. Step 2: Click the Cell Look at the formula bar. Where exactly is the problem? Step 3: Check Each Part Break down complex formulas: \\\ ' Instead of debugging this all at once: =IF(VLOOKUP(A1,Data,2,FALSE)>100,SUM(B:B),AVERAGE(C:C)) ' Test each part separately: ' Cell E1: =VLOOKUP(A1,Data,2,FALSE) ' Cell E2: =E1>100 ' Cell E3: =SUM(B:B) ' Cell E4: =AVERAGE(C:C) \\\ Step 4: Use Evaluate Formula \\\ Formulas → Evaluate Formula ' Watch Excel calculate step by step \\\ Step 5: Check Data Types \\\ =ISNUMBER(A1) ' Is it a number? =ISTEXT(A1) ' Is it text? =ISBLANK(A1) ' Is it empty? =LEN(A1) ' How many characters? \\\

Quick Reference: Error Prevention

To Prevent Do This #N/A Use XLOOKUP or wrap in IFERROR #VALUE! Clean data with TRIM and VALUE #REF! Use Tables and named ranges #NAME? Use formula AutoComplete #DIV/0! Check divisor before dividing #NUM! Validate inputs are in range #NULL! Use comma, not space

Related Resources

Common Excel Errors Explained — Detailed guide for each error Why Excel Shows #VALUE! Error — Deep dive into #VALUE! How to Fix #REF! Error — Complete #REF! guide #N/A Error: Causes and Solutions — Fix lookup errors How Excel Functions Work — Understand formula basics Error Reference Guide — Quick error lookup

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 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.