Excel formulas often return errors like #N/A or #DIV/0! when they encounter problems. These error messages can make your spreadsheet look unprofessional and disrupt other calculations. The IFERROR function provides a way to catch these errors and replace them with a custom value. This article explains how to use IFERROR to display a blank cell when a formula fails.
Key Takeaways: Using IFERROR to Clean Up Your Spreadsheet
- IFERROR(value, value_if_error): This function checks a formula for an error and returns a specified result if one is found.
- Double quotes (“”): Using empty double quotes as the second argument tells Excel to display a truly blank cell when an error occurs.
- Formula Auditing: Temporarily remove IFERROR to see the original error, which helps you find and fix the underlying problem in your data.
What the IFERROR Function Does
The IFERROR function is a logical function in Excel. It requires two arguments. The first argument is the value or formula you want to test. The second argument is the value you want to return if the first argument results in any error. Excel recognizes several standard error types, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.
Using IFERROR with an empty string (“”) as the second argument is a common practice for cleaning up reports. This makes dashboards and summary tables look cleaner by hiding calculation errors. However, it is important to understand that a cell showing “” is not the same as a truly empty cell. Other formulas that check for blank cells, like using =A1=””, will treat it as blank, but functions like ISBLANK will return FALSE because the cell contains a formula.
Steps to Apply IFERROR to Your Formulas
You can wrap an existing formula with IFERROR or write a new formula from scratch. The process is the same for both.
- Identify the formula causing the error
Click on the cell that shows an error like #DIV/0!. Look at the formula bar to see the original formula, such as =A2/B2. - Type the IFERROR function
In the formula bar, place your cursor at the beginning of the formula. Type =IFERROR( . You will now have =IFERROR(A2/B2. - Add the value_if_error argument
After your original formula, type a comma. Then, type two double quotation marks with nothing between them: “”. This represents a blank. Your formula should now look like =IFERROR(A2/B2, “”. - Close the function and apply
Type a closing parenthesis ) to complete the function. The final formula is =IFERROR(A2/B2, “”). Press Enter to apply the change. The error will be replaced by a blank cell. - Copy the formula down a column
If you need to apply this to many cells, use the fill handle. Click the cell with your new IFERROR formula. Drag the small square at the bottom-right corner of the cell down the column to copy it to other cells.
Using IFERROR in a New Formula
You can also build an IFERROR formula from the start. For a VLOOKUP that might return #N/A, you would write it as =IFERROR(VLOOKUP(A2, DataRange, 2, FALSE), “”). This tries the VLOOKUP first. If it finds the value, it displays the result. If the VLOOKUP fails with #N/A, the IFERROR function catches it and shows a blank instead.
Common Mistakes and Limitations to Avoid
IFERROR Hides All Errors, Including Helpful Ones
The main risk of IFERROR is that it masks every type of error. A #REF! error means a cell reference is broken, which is a serious problem in your spreadsheet structure. Using IFERROR to hide it can lead to incorrect data going unnoticed. Before applying IFERROR broadly, check what error the original formula produces. Fix structural errors like #REF! or #NAME? first.
Blank Cell (“”) vs. Zero (0)
A common decision is whether to show a blank or a zero. For a division formula, =IFERROR(A2/B2, 0) might be more accurate than a blank if a zero result is valid. Showing a blank can be misleading in a numeric column, as it may look like missing data. Choose the second argument based on what makes sense for your report.
Performance in Large Arrays
Wrapping every cell in a large data table with IFERROR can slow down calculation speed slightly. For extremely large datasets, consider if you can fix the source data to prevent the error instead. Using IFERROR is efficient for most everyday spreadsheets, but it is an extra calculation step.
IFERROR vs. IFNA: Choosing the Right Function
| Item | IFERROR | IFNA |
|---|---|---|
| Errors it catches | All error types (#N/A, #DIV/0!, #VALUE!, etc.) | Only the #N/A error |
| Best use case | General error cleanup in final reports where any error should be hidden | Specifically for VLOOKUP, XLOOKUP, or MATCH where #N/A is an expected, informative result |
| Safety level | Less safe, can hide critical formula mistakes | More safe, allows other error types to show so you can fix them |
| Syntax example | =IFERROR(A1/B1, “”) | =IFNA(VLOOKUP(A1, B:C, 2, FALSE), “Not Found”) |
You can now use IFERROR to replace error messages with blank cells in your Excel sheets. This makes your financial models and data summaries look cleaner and more presentable. For more control, try the IFNA function when you only want to hide #N/A errors from lookup formulas. Remember to use the Formula Auditing tools to check for errors before you hide them, ensuring your underlying calculations are correct.