When building complex spreadsheets in Microsoft Excel, errors are inevitable. If a VLOOKUP formula cannot find a matching value, it will display a bold #N/A. If an equation attempts to divide a number by zero, it will output #DIV/0!. While these error codes are helpful for debugging, they make final reports look unprofessional and can completely break downstream calculations that rely on those cells.
Instead of manually deleting these errors or writing convoluted nested IF statements, you can use the IFERROR function. This elegant tool intercepts any error generated by a formula and replaces it with clean text, a zero, or a completely blank cell.
How the IFERROR Function Works
The logic of IFERROR is remarkably simple. It evaluates a mathematical formula or function. If the formula works correctly, it outputs the normal result. If the formula results in any standard Excel error (such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!), it outputs a custom value of your choosing instead.
The syntax requires only two arguments:
=IFERROR(value, value_if_error)
- value: The formula, calculation, or cell reference you want to check for errors.
- value_if_error: What Excel should display if an error is detected.
How to Hide Errors with Blank Cells
The most common use case for IFERROR is cleaning up a spreadsheet by making errors completely invisible. This is achieved by setting the value_if_error to a blank string, represented by two double-quotation marks with nothing inside them ("").
Example: Cleaning Up a VLOOKUP
Imagine you have a VLOOKUP formula pulling employee names based on their ID numbers: =VLOOKUP(A2, E:F, 2, FALSE). If ID number A2 does not exist in the database, the cell will scream #N/A.
To fix this, you simply wrap your entire existing formula inside the IFERROR function.
=IFERROR(VLOOKUP(A2, E:F, 2, FALSE), "")
Now, if the VLOOKUP succeeds, it prints the employee’s name. If it fails, the cell remains completely blank, keeping your spreadsheet looking pristine.
How to Replace Errors with Text or Numbers
In financial or mathematical models, replacing an error with a blank cell can sometimes cause further issues. For example, if you are summing a column of numbers, a blank cell is fine, but if you are multiplying, you might prefer the error to default to a zero.
To output a zero, you do not use quotation marks (as quotation marks define text):
=IFERROR(A2/B2, 0)
If you are building a dashboard for other users, you can use IFERROR to provide helpful instructions instead of confusing system codes.
=IFERROR(VLOOKUP(A2, E:F, 2, FALSE), "Employee Not Found")
This immediately tells the user exactly what went wrong in plain English, rather than requiring them to understand what an #N/A code signifies.
A Warning About Masking Errors
While IFERROR is a fantastic formatting tool, you must use it with caution. Because it aggressively suppresses all errors, it can hide legitimate mistakes in your logic.
If you accidentally type the name of a function wrong (which would normally throw a #NAME? error), the IFERROR function will intercept it and output a blank cell. You might assume the formula ran correctly but found no data, when in reality, the formula itself is broken. It is best practice to write and test your complex formulas first, ensuring they work as intended, and only wrap them in IFERROR as the final step before publishing the report.