Error messages are an unavoidable part of working with complex Microsoft Excel spreadsheets. If a VLOOKUP can’t find a match, it throws an ugly #N/A error. If a formula accidentally tries to divide a number by zero, it throws a #DIV/0! error.
While these errors are helpful for debugging, having a final report littered with “#N/A” looks incredibly unprofessional, and those errors can actually break other formulas that try to summarize the data.
To safely handle these situations, you should use the ISERROR function to test your formulas before they break your spreadsheet.
Understanding the Syntax
The ISERROR function is a simple logical test. It looks at a cell (or a formula) and returns the word TRUE if it detects an error, or the word FALSE if the cell contains normal data.
Syntax: =ISERROR(value)
- value: The cell reference (e.g., A2) or the mathematical formula you want to test.
Basic Example: Testing a Cell
Imagine you have a column of data (Column A) that was imported from a messy database. Some cells have numbers, but others have `#VALUE!` errors. You want a quick way to flag the broken rows.
- Click an empty cell in Column B (e.g., B2).
- Type the formula:
=ISERROR(A2) - Press Enter.
If cell A2 contains the number 50, cell B2 will display FALSE. If cell A2 contains a `#DIV/0!` error, cell B2 will display TRUE. You can then use filtering to quickly isolate all the TRUE results and fix the broken data.
Advanced Example: Combining ISERROR with IF
The true power of ISERROR is unleashed when you nest it inside an IF statement. This allows you to tell Excel: “Run this calculation. If it results in an error, display a blank space instead of an ugly error code. If it works perfectly, display the actual answer.”
Imagine you are calculating a profit margin by dividing Profit (cell A2) by Revenue (cell B2). If Revenue is zero, the simple formula =A2/B2 will crash and display #DIV/0!.
Here is how to write a bulletproof version of that formula:
=IF(ISERROR(A2/B2), "No Revenue Data", A2/B2)
How this works:
- Excel first runs the test:
ISERROR(A2/B2). - If the math crashes and causes an error (TRUE), the IF statement kicks in and displays your custom, polite text: “No Revenue Data” (or you could use
""to leave the cell completely blank). - If the math works perfectly without errors (FALSE), the IF statement runs the calculation normally (
A2/B2) and displays the correct profit margin.
By wrapping your delicate formulas in an IF/ISERROR combination, your final spreadsheets will look infinitely cleaner and be protected against sudden data entry mistakes.