Error Handling & Formula Auditing

Every working spreadsheet meets errors sooner or later: a product code that is not in the price list, a quantity typed as a word, a total that points at a deleted row. Beginners often wrap every formula in IFERROR so the errors disappear. That makes the sheet look clean, but it can also hide real mistakes in a report that your manager trusts.

In this lesson you will learn to choose between IFNA and IFERROR, prevent errors before they happen, add up a column that contains errors, and use Excel's auditing tools to find where a wrong number comes from. The error values themselves are introduced in Formulas & Operators.

The sample sheet

A kirana wholesaler in Jaipur keeps a small price list in A1:B5 and today's order lines in D1:F5:

Two order lines have problems. P109 is not in the price list, and someone typed the quantity in E4 as the word "two". The first problem is expected (new codes appear all the time). The second is a data-entry mistake that someone must fix.

IFERROR hides everything

In F2, filled down to F5, a typical first attempt is:

=IFERROR(VLOOKUP(D2, $A$2:$B$5, 2, FALSE) * E2, 0)

F2: 495
F3: 0
F4: 0
F5: 649

IFERROR(value, value_if_error) returns its second argument when the first one produces any error. Row 3 shows 0, which is reasonable for an unknown code. But row 4 also shows 0. The order for two steel tiffin boxes worth ₹998 has quietly vanished from the total, and nobody will notice.

IFNA catches only "not found"

IFNA(value, value_if_na) works the same way but reacts only to #N/A, the error a lookup gives when it cannot find a value. Put it around the lookup alone, not around the whole calculation:

=IFNA(VLOOKUP(D2, $A$2:$B$5, 2, FALSE), 0) * E2

F2: 495
F3: 0
F4: #VALUE!
F5: 649

Now the missing code becomes 0, but the bad quantity still shows #VALUE!. That visible error is a good thing: it tells the person checking the sheet exactly which row to fix. Type 2 in E4 and F4 shows 998.

The rule of thumb:

XLOOKUP has the if_not_found argument built in, as you saw in VLOOKUP & XLOOKUP, so it rarely needs IFNA. You will meet IFNA and IFERROR around VLOOKUP and MATCH in almost every older office file.

Prevent errors instead of hiding them

Often a simple test is clearer than catching an error afterwards. To show a growth percentage only when last month's sales exist:

=IF(B2=0, "", (C2-B2)/B2)

The IS functions test a value and return TRUE or FALSE, so they fit inside IF:

A common office pattern combines ISNUMBER with SEARCH, which returns a position when it finds text and #VALUE! when it does not. To mark bank narrations that mention UPI:

=IF(ISNUMBER(SEARCH("UPI", B2)), "UPI", "Other")

For "UPI/Meera Traders/Ref 7781" the result is UPI; for "NEFT-Rohan Agencies" it is Other.

Adding up a column that has errors

One error inside a range breaks SUM. Before E4 is fixed, the Amount column in the IFNA version holds 495, 0, #VALUE! and 649, and =SUM(F2:F5) returns #VALUE!. While you investigate, AGGREGATE can total the column and skip the errors:

=AGGREGATE(9, 6, F2:F5)

1144

The first argument picks the calculation (9 is SUM, 1 is AVERAGE, 4 is MAX) and the second sets what to ignore (6 means error values). Use it for a provisional total, and still fix the errors: the ₹998 order is missing from 1144.

Tracing where a number comes from

When a total looks wrong, the Formulas > Formula Auditing group shows how cells are connected:

• Trace Precedents draws blue arrows from the cells a formula uses. Select F4 and click it: arrows come from D4, E4 and the price list.
• Trace Dependents does the reverse, showing which formulas use the selected cell. Before deleting a "rough work" column, trace its dependents to make sure no report needs it.
• Remove Arrows clears the arrows. They are never printed.
• Error Checking jumps from one error to the next and offers help for each.
• Watch Window keeps chosen cells in a small floating window. Add the grand total from your summary sheet, then edit the data on another sheet and watch the total change without switching sheets.

Two keyboard shortcuts do the same jumps: Ctrl+[ selects the cells the active formula uses, even on another sheet, and Ctrl+] selects the cells that use it.

Common mistakes

• Wrapping every formula in IFERROR. Typing errors, deleted ranges and wrong data types all disappear. Use IFNA for lookups and fix the rest.
• Putting IFERROR around the whole calculation. Wrap only the part that may fail, as in IFNA(lookup, 0) * qty.
• Returning "" where a number is expected. Later formulas such as =F3*2 then give #VALUE!. Return 0 if other formulas use the cell.
• Trusting a total from AGGREGATE as final. It skips error rows, so their amounts are missing.
• Using ISBLANK on cells with formulas. A formula that returns "" is not blank; test B2="" instead.

Key points

• IFERROR hides every error; IFNA hides only #N/A, so other mistakes stay visible.
• Wrap only the lookup in IFNA, not the whole calculation.
• Prevent errors with IF tests and the IS functions such as ISNUMBER and ISBLANK.
• AGGREGATE(9, 6, range) sums a range while ignoring errors, useful while you investigate.
• Trace Precedents, Trace Dependents, Error Checking and the Watch Window show where a number comes from.

=IFNA(VLOOKUP(A2, Students!$A:$B, 2, FALSE), "Check roll no")
=AGGREGATE(1, 6, B2:B6)