If the value is in A2 and the divisor is in B2, use =IFERROR(A2/B2,"Cannot divide") to show a helpful message instead of an error; with A2 equal to 10 and B2 equal to 0, the result is “Cannot divide.” IFERROR works by evaluating a calculation and returning your chosen fallback if that calculation produces an Excel error.
What IFERROR does
IFERROR is a worksheet function that catches an error produced by an expression and substitutes a value you specify. The expression might be a division, a lookup, a reference, or a longer calculation. If it succeeds, Excel displays its normal result; if it fails with an error, Excel displays the fallback instead.
It can catch errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. The fallback can be text, a number, a blank-looking result, or another calculation. IFERROR does not repair the cause of an error or verify that the result is sensible. It only changes what appears when an error occurs, so the fallback should make the problem understandable rather than conceal a mistake.
How to write an IFERROR formula
IFERROR has two arguments, separated by a comma in standard English-language Excel formulas. The first is the calculation to evaluate; the second is what Excel should return if that calculation produces an error. Put the whole calculation inside the first argument, and enclose text fallbacks in straight double quotation marks. A numeric fallback does not need quotation marks.
For example, if A2 contains a quantity and B2 contains a unit price, the formula can handle a failed multiplication or reference in the same way as other calculations. In everyday use, however, multiplication of ordinary numbers rarely produces an error; IFERROR is most useful when a calculation can plausibly fail, such as division by zero or a lookup that finds no match.
An empty string is a common fallback when you want a cell to look blank. It is not the same as an actually empty cell: the formula remains in the cell, and other formulas or checks may treat the result differently from a truly blank cell.
Examples for division and percentages
-
Prevent a divide-by-zero message. With a sales amount in A2 and units in B2, enter
=IFERROR(A2/B2,"N/A"). If A2 is 240 and B2 is 0, the result is N/A; if B2 is 12, it is 20. This keeps a report readable when a row has no units, but it does not explain why the divisor is zero. -
Calculate a percentage change. With the new value in B2 and the old value in A2, enter
=IFERROR((B2-A2)/A2,"No prior value"). If A2 is 80 and B2 is 100, the result is 0.25, or 25% when the cell is formatted as a percentage. If A2 is zero, Excel returns the text fallback rather than a division error. -
Show a blank instead of an error. With the amount in A2 and the quantity in B2, enter
=IFERROR(A2/B2,""). If B2 is zero, the cell appears blank; if A2 is 36 and B2 is 6, it shows 6. Use this only when a blank will not be mistaken for a valid missing result.
Formatting the result matters. A percentage formula returns a decimal value, so format its cell as Percentage rather than multiplying by 100 and adding a percent sign as text. A text fallback such as “No prior value” will remain text even if the cell is formatted as a percentage.
Examples for lookups and missing matches
A lookup commonly returns #N/A when the requested item is absent. IFERROR can replace that error with a message. With product codes in A2:A6, product names in B2:B6, and the code to find in D2, enter =IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Not found"). If D2 contains a code that is not in A2:A6, the result is Not found; if the matching row contains “Mug,” the result is Mug.
VLOOKUP searches the first column of the specified table and, with FALSE as its fourth argument, requests an exact match. IFERROR handles any error from the lookup, not just a missing match. If a reference in the formula is broken or another issue causes an error, the same “Not found” message may appear. That can make diagnosis difficult, so use a more specific error-handling choice when you need to distinguish missing items from other problems.
For a modern alternative, XLOOKUP has a built-in argument for a value to return when no match is found, avoiding the need to wrap that case in IFERROR. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If a workbook must support older Excel versions, VLOOKUP with IFERROR is often more compatible.
Choosing a useful fallback
The right fallback depends on how people will use the result. A message is usually clearer in a report, a blank may suit a presentation, and a number may be necessary for further calculations. A fallback of 0 can be useful when zero is the intended business meaning, but it can also make missing or invalid data look like a genuine zero.
Consider what the value means before hiding an error. If a missing lookup means “not yet entered,” say so. If a division by zero means “no units recorded,” a message can point to the source problem. If downstream formulas require a number, consider leaving the error visible during data checks, or use a separate status column to identify missing inputs.
For a division where only a zero divisor needs special treatment, an IF test can be more informative than catching every error. With the numerator in A2 and the divisor in B2, enter =IF(B2=0,"No units",A2/B2). If B2 is zero, the result is No units; if A2 is 30 and B2 is 5, the result is 6. Errors unrelated to a zero divisor are not hidden by this formula.
Common mistakes and limitations
-
Hiding errors too broadly: Wrapping a large formula or an entire calculation chain in IFERROR can mask broken references, misspelled function names, and unexpected input. Apply error handling around the part that can legitimately fail, and keep a way to inspect source data.
-
Assuming every unusual result is an error: A blank cell, a zero, or an inaccurate value is not necessarily an Excel error. IFERROR will not flag a calculation that returns a valid but incorrect result.
-
Using a text fallback in later arithmetic: If a formula returns “N/A” for some rows and a number for others, a later calculation expecting numbers may produce another error. Decide whether the next step should skip those rows, display a status, or use a meaningful numeric value.
-
Thinking an empty string deletes the formula: The formula remains in place when its fallback is “”. The result merely looks blank, and functions that count or test cells may not treat it exactly like an unused cell.
-
Using the wrong list separator: The examples use commas, as requested for standard English-language Excel syntax. Some regional settings use semicolons between arguments. If Excel rejects a pasted formula, check the list separator setting used by your installation.
IFERROR compared with IFNA
IFERROR catches all Excel error types, while IFNA catches only #N/A. That distinction matters for lookups: a missing item can reasonably produce “Not found,” but a #REF! caused by a damaged reference may deserve attention rather than the same message.
With product codes in A2:A6, names in B2:B6, and the search code in D2, use =IFNA(VLOOKUP(D2,A2:B6,2,FALSE),"Not found") when you want to handle a missing-match #N/A without replacing other errors. If D2 is absent from the code list, the result is Not found; if the formula encounters a different error, Excel displays that error. IFNA is available in Excel 2013 and later. For older Excel versions, IFERROR is available from Excel 2007 onward.
Compatibility and practical use
IFERROR is supported in Microsoft Excel 2007 and later, including Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The examples use ordinary worksheet formulas and do not require a special ribbon command. On Windows, press Enter after typing a formula. On Mac, the same formula syntax and function behavior apply; keyboard shortcuts for editing or entering formulas can differ, but no platform-specific step is required here.
When reviewing a workbook, consider whether the fallback is being used as a temporary presentation choice or as part of the data logic. During development, it can help to remove IFERROR briefly and inspect the original error. After fixing the underlying issue, add a suitable fallback only if the spreadsheet’s audience benefits from it. In shared workbooks, clear fallback wording also helps a colleague understand whether a result is missing, invalid, or intentionally suppressed.
Frequently Asked Questions
What does IFERROR return when there is no error?
It returns the result of the calculation or value in its first argument. The fallback is used only when that first argument evaluates to an Excel error.
Can IFERROR return a blank cell?
It can return an empty string, which appears blank. With a value in A2 that may produce an error, enter =IFERROR(A2/0,""); the cell displays nothing, but it still contains a formula and is not literally empty.
Does IFERROR fix the error in my formula?
No. It replaces the displayed error result with a fallback. Check the original calculation and its inputs if you need to correct the cause or confirm that the fallback is appropriate.
Should I use IFERROR or IFNA for a lookup?
Use IFNA when you only want to replace a missing-match #N/A and want other errors to remain visible. Use IFERROR when any error from the lookup should produce the same fallback.
Why does Excel show a formula error after I paste an example?
Check that quotation marks are straight double quotes, parentheses are balanced, and your Excel regional settings accept commas as argument separators. Some installations require semicolons instead of commas.
Related
- Excel Flip Data Upside Down: Reverse Rows in Excel
- Excel Find Table Names: Where to Look and How
- Excel Find Shortcut Key: Search Cells Fast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- INDEX and MATCH in Excel: How to Find Values FlexiblyYes. In Excel, INDEX and MATCH work together to find a value by its position: MATCH locates the…
- Excel Formula for Every Friday Date in a Date Range ListWith a start date in A2 and an end date in B2, this Microsoft 365 formula returns every…
- Excel Length of Service in Years and Months: FormulasTo calculate completed length of service in years and remaining months, enter the start date in A2 and…
- How to Find a Weighted Average in Excel with SUMPRODUCTTo find a weighted average in Excel, multiply each value by its weight, add those products, then divide…
- Can You Filter Out Duplicates in Excel? Methods That WorkYes. In Excel for Microsoft 365 on Windows, you can identify duplicates with conditional formatting, filter them with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.