Your Excel sheet shows #N/A, #DIV/0!, or #VALUE! errors, which can make reports look unprofessional. These errors appear when formulas reference empty cells, perform invalid calculations, or look up missing data. This article explains how to use the ISERROR and IF functions together to detect and hide these errors in older versions of Excel. You will learn a single formula that replaces any error with a blank cell or a custom message.
Key Takeaways: Hide Formula Errors in Excel
- ISERROR function: Checks if a formula result is any error and returns TRUE or FALSE.
- IF function: Returns one value if the ISERROR test is TRUE and another if it is FALSE.
- Formula: =IF(ISERROR(YourFormula), “”, YourFormula): Shows a blank cell when an error occurs, otherwise shows the normal result.
Understanding the ISERROR and IF Functions
In older Excel versions like Excel 2010, 2007, or 2003, the IFERROR function does not exist. To manage errors, you must combine two separate functions. The ISERROR function is the detection tool. You give it any cell reference or formula. It returns TRUE if that cell contains any error, including #N/A, #VIV/0!, #REF!, #NAME?, #NUM!, #NULL!, or #VALUE!. It returns FALSE if the cell contains a normal number, text, or is empty.
The IF function makes a decision based on a logical test. Its syntax is IF(logical_test, value_if_true, value_if_false). By nesting ISERROR inside the IF function, you create a logical test that asks: “Does my original formula produce an error?” If the answer is TRUE, you can tell Excel to display nothing or a dash. If the answer is FALSE, you tell Excel to display the original formula’s result. This combination gives you full control over error display without modern functions.
Steps to Combine ISERROR and IF in a Formula
Follow these steps to build a formula that hides errors. The example uses a VLOOKUP formula that may return #N/A if a lookup value is not found.
- Identify your original formula
First, write down the formula that is currently causing the error. For example: =VLOOKUP(A2, DataRange, 2, FALSE). This formula is in cell B2. - Build the ISERROR test
Wrap your original formula inside the ISERROR function. Type =ISERROR(VLOOKUP(A2, DataRange, 2, FALSE)). This part will become the logical_test for the IF function. - Insert the IF function
Now, place the entire ISERROR function inside an IF function. Start typing =IF(. The structure is =IF(ISERROR(YourFormula), value_if_true, value_if_false). - Set the value if an error is TRUE
After the first comma, specify what to show when an error occurs. To show a blank cell, use two double quotes: “”. You can also use a custom message like “Not Found” or “0”. - Set the value if an error is FALSE
After the second comma, specify what to show when there is no error. You must repeat your original formula here. The complete formula is: =IF(ISERROR(VLOOKUP(A2, DataRange, 2, FALSE)), “”, VLOOKUP(A2, DataRange, 2, FALSE)). - Press Enter and copy the formula
Press Enter to apply the formula. The cell will now be blank if VLOOKUP returns an error, or show the correct lookup result. Copy the formula down the column to apply it to all cells.
Using the Formula with Other Error-Prone Functions
The same pattern works for any formula. For a division formula that might cause #DIV/0!, start with =A2/B2. The error-hiding version is =IF(ISERROR(A2/B2), “”, A2/B2). For a complex formula, simply replace “YourFormula” in the structure with your entire calculation. The key is to write the original formula correctly in both the ISERROR check and the value_if_false argument.
Common Mistakes and Limitations to Avoid
Formula Returns a Blank for All Cells
If your new formula shows a blank for every cell, even correct ones, check the value_if_false argument. You likely made a typo or used different cell references in the second instance of your formula. Both instances of the original formula inside the IF function must be identical.
ISERROR Hides All Errors Including Important #N/A
The ISERROR function catches every error type. Sometimes, #N/A is a valid result you want to see, as it confirms a lookup value is missing from the source list. If you need to hide only specific errors like #DIV/0! but keep #N/A visible, you cannot use ISERROR. In older Excel, you must use the more specific ISNA function or combine multiple IF statements with functions like ISERR.
Performance Issues with Long Formulas
The combined formula calculates your original formula twice: once inside ISERROR and once for the final result. For simple calculations, this is fine. For very long, complex array formulas or calculations on large datasets, this double calculation can slow down Excel. Consider simplifying the original formula or using helper columns to improve performance.
ISERROR vs IFERROR: Key Differences
| Item | ISERROR with IF (Older Method) | IFERROR Function (Newer Method) |
|---|---|---|
| Excel Version Availability | Excel 2003, 2007, 2010 | Excel 2010 and later |
| Formula Length | Longer, repeats the main formula | Shorter, mentions formula once |
| Error Types Caught | Catches all error types (#N/A, #DIV/0!, etc.) | Catches all error types |
| Customization for Specific Errors | Requires different functions like ISNA | Cannot differentiate error types |
| Calculation Overhead | Calculates the main formula twice | Calculates the main formula once |
You can now clean up your spreadsheets by hiding distracting error values. Use the =IF(ISERROR(YourFormula), “”, YourFormula) pattern for any calculation. For more control, try using ISNA instead of ISERROR to only hide #N/A errors from VLOOKUP. Remember that in Excel 2010 or later, you can switch to the simpler IFERROR function for the same result.