IFERROR lets you wrap an Excel formula so that, if it produces an error, the cell shows a value you choose instead, such as a short message, a zero or a blank. This guide covers the syntax, a worked example, and the main way IFERROR can work against you.
The details below come from Microsoft's IFERROR help page. This article covers IFERROR only. It does not explain how IFNA differs, because that page was not part of the research for this piece.
How the IFERROR formula is built
According to Microsoft, the syntax is:
IFERROR(value, value_if_error)
- value is the formula you want to check.
- value_if_error is what Excel returns if that formula evaluates to an error.
If there is no error, IFERROR returns the formula's normal result. Microsoft's page also says that if either argument points to an empty cell, IFERROR treats it as an empty string ("").
Wrap an existing formula in IFERROR
- Click the cell that contains the formula showing an error.
- In the formula bar, put
IFERROR(after the equals sign, before the existing formula. - At the end of the formula, type a comma, then the value you want shown instead of the error, then a closing bracket.
- Press Enter.
- Copy the cell down or across to apply the same formula to the rest of the range.
Microsoft's example divides one cell by another and supplies a text message as the fallback:
=IFERROR(A2/B2, "Error in calculation")
With 210 in A2 and 35 in B2, the page shows the formula returning 6. If the division produced an error, the cell would show "Error in calculation" instead.
To show a blank cell rather than a message, use an empty string as the second argument:
=IFERROR(A2/B2, "")
The same wrapping works around lookup formulas. If you are building one from scratch, see How to Use VLOOKUP in Excel or How to Use XLOOKUP in Excel, then wrap the finished formula in IFERROR the same way.
IFERROR with array formulas
If the first argument is an array formula, Microsoft says IFERROR returns an array of results, one for each cell in the range. You do not need to wrap each cell separately.
The common mistake: hiding errors you needed to see
IFERROR returns your fallback for any error the formula produces. That is convenient, but it also means a typo in a cell reference or bad source data gets the same friendly message as an expected miss. You lose the clue that something is broken.
Two habits help:
- Get the formula working first. Add IFERROR only once the unwrapped formula gives correct results on known data.
- Pick a fallback you will notice. A message like "Check" is easier to spot than a blank cell when you are scanning a sheet for problems.
If your errors come from numbers stored as text, fix the data rather than masking it. How to Convert Text to Numbers in Excel walks through that.
Which Excel versions support IFERROR
Microsoft's page lists Excel for Microsoft 365 (Windows and Mac), Excel 2024, 2021, 2019, 2016 and Excel Web App as applicable versions.
If you would rather hide errors without changing formulas, Microsoft has a separate help page on hiding error values and error indicators in cells.
