3.17 Error Types and How to Fix Them
| 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 |
Steps in Excel – tracing errors
- Click the error cell › the ⚠ smart tag › Show Calculation Steps or Trace Error.
- Formulas › Formula Auditing › Trace Precedents / Trace Dependents draws arrows; Remove Arrows clears them.
- Formulas › Formula Auditing › Error Checking walks through all errors in the sheet.
- 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.
Ravindra Bagale's Tip – मराठी
Error दिसला की बरेच students घाबरून पूर्ण formula पुन्हा लिहितात. त्यापेक्षा error चं नाव वाचा – प्रत्येक error काहीतरी सांगत असतो: #N/A म्हणजे "सापडलं नाही", #REF! म्हणजे "reference गेला". Trace Precedents ने कोणत्या cell मुळे error आला ते शोधा, आणि तिथेच दुरुस्ती करा.
Ravindra Bagale's Tip – हिंदी
Error दिखते ही बहुत से students घबराकर पूरा formula दोबारा लिखने लगते हैं. उसकी जगह error का नाम पढ़ो – हर error कुछ न कुछ बताता है: #N/A मतलब "नहीं मिला", #REF! मतलब "reference चला गया". Trace Precedents से पता करो कि किस cell की वजह से error आया, और वहीं ठीक करो.
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.
Thodkyaat sangaycha tar (quick recap)
SUMIFS/COUNTIFS/AVERAGEIFS ne conditional totals, text functions ne data todne-jodne (TEXT ne format), dates ha number aahe he lakshat theva, IF/IFS/SWITCH ne logic, IFNA ne lookup errors, SUMPRODUCT ne advanced conditions – aani error aala tar tyacha arth vachun trace kara. Aata pudhe jaauya – lookups!