IFERROR in Excel: How to Catch and Handle Formula Errors

To use IFERROR in Excel, wrap a calculation or formula in IFERROR and choose what to show if it returns an error. For example, with a number in A2 and a divisor in B2, this formula shows 0 instead of an error when the division cannot be completed: =IFERROR(A2/B2,0). IFERROR is useful for making reports easier to read, but choose a fallback that makes the problem clear rather than hiding an error you need to investigate.

This guide uses Microsoft Excel for Microsoft 365 on Windows. The IFERROR formula works in Excel for Mac and Excel for the web as well; the main difference is that some regional settings use semicolons instead of commas between arguments.

What You’ll Need

An Excel workbook with a formula or calculation that may return an error.
A clear idea of what should appear if that calculation fails, such as 0, a short message, or a blank-looking cell.
For the examples below, a few sample values in the cells described before each formula. You can use your own worksheet instead.

Step-by-Step

Identify the error-prone calculation. Find a formula that sometimes produces an Excel error. Common examples include dividing by a cell that may be zero or blank, looking up a value that may not exist, and referring to a cell or range that may have been deleted. If the formula is already working, note what result it should return in an ordinary case before changing it.
Decide what the fallback should communicate. Choose the second IFERROR argument based on the task. A numeric fallback such as 0 can suit a calculation where zero is a meaningful result. A message such as Not found can help explain a missing lookup. An empty text value can make a presentation cleaner, but it can also conceal missing or invalid data. Do not use a fallback simply because it makes every cell look tidy; consider whether a colleague will need to know an error occurred.
Wrap the existing formula with IFERROR. Keep the original expression as the first argument, then add a comma and the fallback as the second argument. With a quantity in A2 and a divisor in B2, use this formula to return 0 if division produces an error: =IFERROR(A2/B2,0). The first argument can be a cell reference, a calculation, or a longer formula. The second argument is returned only when the first argument results in an Excel error.
Enter the formula in the appropriate cell. Click the result cell, type the formula beginning with an equals sign, and press Enter. Check that the cell shows the expected result for a normal input. If B2 contains 12 and A2 contains 60, the example formula returns 5, because 60 divided by 12 is 5. If B2 contains 0, it returns 0 instead of displaying #DIV/0!. The fallback does not change the calculation when the first argument works.
Use IFERROR with a lookup when an item may be missing. Suppose IDs are in A2:A100, matching product names are in B2:B100, and the ID to search for is in E2. This formula displays a message if the lookup returns an error: =IFERROR(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found"). VLOOKUP searches the first column of the range for an exact match and returns the corresponding value from its second column. If E2 contains an ID that exists in column A, the formula returns its product name; otherwise, it displays Not found. The dollar signs keep the lookup range fixed if you copy the formula.
Copy the formula down if needed. If each row needs the same calculation, select the formula cell and drag its fill handle down, or copy and paste it into the other result cells. Check the first few copied results. Relative references such as A2 and B2 normally adjust to A3 and B3 in the next row, while the dollar signs in the lookup example keep the specified range unchanged. If the references move in a way you did not intend, edit the formula before filling the rest of the column.
Test both success and error cases. Try a row where the formula should calculate normally and, if practical, a row that should trigger an error. For a division formula, a zero divisor is a useful test. For a lookup, use an ID that is not in the lookup range. Confirm that the fallback is appropriate and that it does not make a real data issue easy to overlook. Restore the intended data after testing.
Review the formula after editing the source data. IFERROR does not repair a broken reference or correct a bad input; it only changes what is displayed when an error occurs. If the fallback appears unexpectedly, inspect the original formula and its referenced cells. Keep the underlying data and formula logic correct even when the worksheet has a cleaner appearance.

Common Mistakes

Using the wrong fallback. Returning 0 can imply that a real measurement or total is zero when the calculation actually failed. In a sales report, that could be misleading. Use a clear label when readers need to distinguish a missing result from a genuine zero, or leave the error visible during checking.

Hiding every kind of error. IFERROR catches any error produced by its first argument, not just the one you expected. That includes errors such as #DIV/0!, #N/A, #VALUE!, #REF!, and #NAME?. A fallback intended for a missing lookup could therefore hide a misspelled function or a deleted reference. If you only want to handle a missing lookup value, consider whether IFNA is more suitable; it handles #N/A rather than every error.

Putting the fallback in the wrong place. IFERROR needs the expression to check first and the response to use if it errors second. A misplaced comma or parenthesis can change the formula or make Excel reject it. Check that both arguments are inside the same pair of parentheses.

Assuming a blank-looking cell is truly empty. A fallback of an empty text value displays nothing, but the cell still contains a formula. It is not the same as an unused cell, which can matter for counts, charts, and later formulas. Some functions treat formula-generated empty text differently from cells that are genuinely empty.

Wrapping a formula before checking its cause. If a formula starts returning the fallback, find out why. The error could reveal a data-entry issue, a wrong range, or a calculation that needs a different design. Suppressing it without investigating can allow inaccurate results to pass unnoticed.

Tips

Use a fallback that matches the purpose of the worksheet. A short message is often clearer than 0 when the answer is unavailable; a numeric fallback is appropriate only when zero is a defensible substitute.
Keep the original calculation easy to recognize. IFERROR should surround the calculation, not replace the logic that makes it work. For a long formula, check the inner expression separately if you need to diagnose a problem.
Use IFNA when the only expected problem is a lookup that cannot find a match. This leaves other errors visible, which can make accidental formula problems easier to catch.
During workbook development, consider leaving errors visible until you have verified the formulas and data. Add a fallback for the final worksheet only when it improves usability and does not misrepresent the result.
In a formula copied across rows, check both relative references and fixed ranges. Use dollar signs on references that should stay in place, and leave them off references that should move with each row.
Excel for Mac and Excel for the web support IFERROR too. If a formula copied from another region is rejected, check whether your Excel settings expect semicolons rather than commas as argument separators. The function name and the purpose of its arguments remain the same.

Troubleshooting

The formula is rejected when you press Enter. Check for a missing closing parenthesis, an extra comma, or quotation marks that are not straight double quotes. If your regional settings use semicolons as list separators, replace the commas between arguments with semicolons. Also confirm the formula begins with an equals sign.

The cell still shows an error. Confirm that IFERROR surrounds the part of the formula producing the error, and that the fallback is supplied as the second argument. If the displayed error is coming from a different cell or a separate formula, wrapping this formula will not catch it. Select the cell and inspect its formula in the formula bar.

The fallback appears when you expected a valid answer. The first argument is producing some kind of error. Check the referenced cells, the lookup value, the lookup range, and the formula itself. For VLOOKUP, the value being searched for must be in the first column of the specified range, and FALSE requests an exact match. Verify that values that look alike are stored in compatible formats; for example, a number stored as text may not match a numeric ID as expected.

The formula returns 0, but you cannot tell whether the calculation worked. A zero might be a genuine result or the chosen fallback. Temporarily remove IFERROR or replace the fallback with a clear message while investigating. Once you understand the cause, decide whether a zero is still the right final response.

The result changes after copying the formula. Compare the cell references in the original and copied formulas. Excel normally adjusts relative references as formulas move. Add dollar signs to references that must remain fixed, then recopy the corrected formula and test several rows.

The cell looks blank but affects another calculation. If the fallback is an empty text value, remember that the cell contains a formula returning text rather than being truly empty. Review how the downstream formula handles text and empty cells, and use a more explicit fallback if the distinction matters.

Frequently Asked Questions

What does IFERROR do in Excel?
It evaluates a formula or value and returns the result if there is no error. If the first argument returns an Excel error, it returns the fallback specified in the second argument.

Can IFERROR hide #N/A and #DIV/0!?
Yes. It catches those errors and other Excel errors, including #VALUE! and #REF!. If you only need to handle #N/A, IFNA can be a narrower choice.

How do I make IFERROR show a blank?
Use an empty text value as the fallback. The result looks blank, but the cell still contains a formula, so it is not identical to a truly empty cell.

Does IFERROR work in Excel for Mac and the web?
Yes. The function is supported in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. Depending on regional settings, you may need semicolons instead of commas between arguments.

Can I use IFERROR with VLOOKUP?
Yes. Put the VLOOKUP formula first and a useful fallback, such as Not found, second. Remember that IFERROR can also hide errors other than a missing match, so check unexpected fallback results.

Does IFERROR fix an error in my formula?
No. It changes the result shown when an error occurs; it does not correct the formula or the source data. Investigate recurring fallbacks so a real problem is not concealed.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.