Ravindra BagaleCourses & study guides मराठी Track your progress

3. Formulas and Functions

3.17 Error Types and How to Fix Them

By Last updated:

Error Meaning Typical cause on our data Fix
#N/A Value not available Lookup value not found – extra space, text vs number Store ID TRIM/VALUE the key; check the lookup range; IFNA for real "not found"
#VALUE! Wrong type of argument ="₹64"*2 (text with symbol); ranges of different sizes Clean numbers (Module 5); match range sizes
#REF! Invalid reference A column used in a formula was deleted; VLOOKUP col index > columns Undo; rebuild the formula; use XLOOKUP/structured references
#DIV/0! Division by zero Qty blank or 0; zero orders in a city =IF(F2=0,"",G2/F2) or IFERROR
#NAME? Excel doesn't recognise a name Typo (=SUMIFF), missing quotes around text, new function in old Excel Fix spelling; put text in "quotes"; check version
#SPILL! Dynamic array can't spill Cells in the spill area are not empty; inside an Excel Table Clear the blocking cells; use outside a Table
#NUM! Invalid number =SQRT(-1), impossible date Check inputs
#NULL! Wrong range operator =SUM(A1 A5) (space instead of comma/colon) Use , or :
#CALC! Calculation engine error (365) =FILTER() returns nothing without if_empty Add the if_empty argument
##### Not an error – column too narrow, or negative date/time Long rupee amount; negative time Widen column; fix time maths

About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Aamir Khan) are made up for this lesson on error types and how to fix them.

Steps in Excel – tracing errors

  1. Click the error cell › the ⚠ smart tag › Show Calculation Steps or Trace Error.
  2. Formulas › Formula Auditing › Trace Precedents / Trace Dependents draws arrows; Remove Arrows clears them.
  3. Formulas › Formula Auditing › Error Checking walks through all errors in the sheet.
  4. Formulas › Formula Auditing › Watch Window keeps an eye on key cells from other sheets.

Ravindra Bagale's Tip

When they see an error, many students panic and rewrite the whole formula. Instead, read the error's name – every error is telling you something: #N/A means "not found", #REF! means "the reference is gone". Use Trace Precedents to find which cell caused the error, and fix it right there.

Practice task

Deliberately create each of the six main errors on a practice sheet, then fix each one and write the cause next to it.

Loading practice tasks...

  1. Fix #NAME? (Easy): The broken formula is =SUMM(B2:B4). Write the correct one.
  2. Store: fix #NAME? (Easy): =AVERAG(Stock!B2:B4) gives #NAME?. Write the correct formula.
  3. Fix #DIV/0! (Medium): =B3/C3 gives #DIV/0!. Show "No qty" when C3 is 0, else B3/C3.
  4. Fix #VALUE! (Medium): =D2*2 gives #VALUE!. Remove the ₹ and turn it into a number, then multiply by 2.
  5. Store: fix #DIV/0! (Medium): =Stock!B3/Stock!C3 gives #DIV/0!. Show "Not used" instead.
  6. Store: fix #VALUE! (Medium): =Stock!D2/Stock!C2 gives a wrong answer or error. Remove the comma, make it a number, then divide by Stock!C2.
  7. Fix #N/A from a space (Hard): Look up E2 in A2:C4 and return the Amount (column 2). Use TRIM to fix the space.
  8. Find the hidden space (Hard): Count the characters in E2.
  9. Store: fix #N/A (Hard): Look up Stock!E2 in Stock!A2:C4 and return Stock (column 2). Use TRIM.
  10. Store: see the space (Hard): Count the characters in Stock!E2.

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Error Types And How To Fix Them?
Card 1: What is Error Types And How To Fix Them? · Download PNG
Revision card 2: Tiny example — Error Types And How To Fix Them
Card 2: Tiny example — Error Types And How To Fix Them · Download PNG
Revision card 3: Checklist — Error Types And How To Fix Them
Card 3: Checklist — Error Types And How To Fix Them · Download PNG