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
Error disla ki khup students ghabrun poora formula punha lihitat. Tyapeksha error cha naav vacha – pratyek error kahi tari sangat asto: #N/A mhanje "sapadla nahi", #REF! mhanje "reference gela". Trace Precedents ne kontya cell mule error aala te shodha, aani tithech durusti kara.
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!