ISERROR & ISNA Error Auditing Formulas
Learn how to detect errors and empty cells in Excel using ISBLANK, ISERR, and ISERROR. Distinguish between #N/A and fatal errors to audit data integrity.
Auditing large financial tables often means distinguishing between normal missing data and broken spreadsheet calculations. For instance, a vendor that has not yet been assigned a catalog price might show #N/A, which is expected. In contrast, a division by zero error (#DIV/0!) indicates an invalid financial formula that needs an immediate fix.
Excel provides three specialized formulas to inspect cell errors and empty states: ISBLANK, ISERR, and ISERROR.
Quick Comparison: Error and Blank Check Trio
| Formula | What It Catches | Catches #N/A? | Best Fit in Real Work |
|---|---|---|---|
ISBLANK(cell) |
Truly empty cells with no content or formulas | No | Verifying whether required form fields were left blank by users. |
ISERROR(cell) |
ALL error types (#N/A, #VALUE!, #DIV/0!, #REF!) |
Yes | Broad error suppression and blanket error screening. |
ISERR(cell) |
All error types EXCEPT #N/A | No | Detecting broken calculations without flagging normal lookup misses. |
Deep Technical Mechanics
1. The Difference Between ISERROR and ISERR
The distinction between ISERROR and ISERR is essential for professional modeling:
- If cell A1 contains
#N/A,ISERROR(A1)returnsTRUE, butISERR(A1)returnsFALSE. - If cell A1 contains
#DIV/0!, both formulas returnTRUE.
Use ISERR when you want to catch mathematical bugs while letting lookup misses stay visible for catalog updates.
2. The "Ghost Blank" Trap with ISBLANK
If a cell contains a formula that evaluates to an empty text string (such as =IF(A1>0, "OK", "")), that cell looks completely empty on your screen. However, ISBLANK returns FALSE because the cell contains a formula!
To detect visually empty cells that may contain empty strings, test for length instead: =LEN(A1)=0.
Hands-on Practice Playground
Audit the vendor invoices below to test for empty records and isolate calculation errors. Follow each objective to complete the exercise.
In cell D2, check if invoice amount B2 is completely blank using ISBLANK.
In cell D2, test whether invoice amount B2 is a completely blank, empty cell using the ISBLANK formula.
In cell E2, verify whether cell B2 contains any spreadsheet error value using ISERROR.
In cell E2, verify whether cell B2 contains any spreadsheet error value using the ISERROR formula.
In cell F2, check if cell B2 contains a technical error excluding #N/A using ISERR.
In cell F2, check whether cell B2 contains a technical error while ignoring #N/A conditions using the ISERR formula.
Share This Lesson
Spread the word with your peers and challenge your friends