In Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such as =ISERROR(A2/B2) and Excel returns TRUE if the division produces an error, or FALSE if it does not. ISERROR detects errors; combine it with IF when you want to show a message or take another action instead of displaying the error.
You can use ISERROR in Microsoft Excel for Microsoft 365 on Windows to audit calculations, catch failed lookups, or control what a formula displays. Before starting, decide which cell or calculation you want to test and what you want the workbook to do when an error occurs.
What You’ll Need
- An Excel workbook with a value, formula, or calculation you want to check.
- A blank cell for the ISERROR formula, unless you are adding the check inside an existing formula.
- If you plan to handle errors with IF, a clear alternative result, such as a short message, a blank, or a fallback calculation.
Step-by-Step
- Identify the result to test. ISERROR can test a cell that already contains a value or formula, or it can test a calculation directly. For a quick first test, suppose A2 contains an amount and B2 contains a divisor. A zero in B2 would make the division fail, while a nonzero number would produce a numeric result. You do not need to change the source cells to check their result.
- Choose an empty cell for the check. Select, for example, C2, next to the row you are auditing. Keeping the check in a separate column makes it easier to see which rows need attention. If your source data has headers, put the check formula on the first data row rather than in the header row.
- Enter ISERROR around the value or calculation. With the numerator in A2 and the denominator in B2, enter this in C2:
=ISERROR(A2/B2). Press Enter. If A2 is 120 and B2 is 0, the division produces #DIV/0! internally and C2 displays TRUE. If A2 is 120 and B2 is 6, the calculation is 20 and C2 displays FALSE. The function reports whether an error occurred; it does not display the underlying error itself. - Check a cell that may already contain an error. If A2 contains a formula or imported value that might be erroneous, use a direct cell test. With the value or formula result in A2 and the check in B2, enter
=ISERROR(A2)in B2. B2 displays TRUE if A2 evaluates to an Excel error and FALSE if it evaluates to a regular number, text, or blank. This is useful when you want to flag existing errors without rebuilding the original formula. - Return a useful message instead of TRUE or FALSE. To make the result readable, use IF around the check. With the numerator in A2, denominator in B2, and output in C2, enter
=IF(ISERROR(A2/B2),"Check inputs",A2/B2)in C2. For 120 divided by 0, the cell displays Check inputs; for 120 divided by 6, it displays 20. The first argument to IF is the test, the second is the result when the test is TRUE, and the third is the result when it is FALSE. - Use ISERROR to handle a lookup that may not find a match. Suppose A2:B5 contains a product code in column A and its price in column B, E2 contains the code to search for, and F2 is the output cell. Enter
=IF(ISERROR(VLOOKUP(E2,$A$2:$B$5,2,FALSE)),"Not found",VLOOKUP(E2,$A$2:$B$5,2,FALSE))in F2. If the code exists, F2 returns the matching price; if VLOOKUP returns #N/A because the code is missing, F2 displays Not found. The FALSE argument requests an exact match. Keep the dollar signs in the lookup range if you intend to copy the formula down. - Copy the check to other rows and review the results. If each row has its own numerator and denominator, copy the formula from C2 down the check column using the fill handle. Excel adjusts relative references, so the next row checks A3 divided by B3. Review TRUE results to find the source of the error, or use an IF formula if the finished sheet should show a message instead. Confirm a few rows manually before relying on the check across a large range.
Common Mistakes
Expecting ISERROR to fix the problem: ISERROR only returns TRUE or FALSE. It does not repair a bad reference, change a divisor, or replace an error with a message. Use IF with ISERROR when you need to choose an alternate display, or correct the underlying formula or data when the actual calculation should succeed.
Confusing an error with text that looks like one: A cell containing the text #N/A is not the same as the Excel error value #N/A. ISERROR detects actual Excel error results, not ordinary text that happens to use the same characters. If data was imported as text, check its type or clean the source instead of expecting ISERROR to flag it.
Assuming every unusual result is an error: A blank cell, zero, or text result is not automatically an error. For instance, a formula that returns an empty string is text, so ISERROR returns FALSE. If you need to test for blanks or particular values, use a suitable separate test rather than treating ISERROR as a general data-quality check.
Using an unnecessarily repeated calculation: In the IF example, the division or lookup appears twice. That is clear for a small example but can make a long formula harder to maintain, and a costly calculation may be repeated. For many tasks, IFERROR is a simpler option; see the comparison in Tips.
Leaving out a lookup argument or changing the range: A lookup that unexpectedly returns #N/A may be caused by a missing exact-match setting, extra spaces in codes, or a range that does not include the needed row. Hiding the error can conceal those issues, so verify the lookup inputs as well as the ISERROR result.
Tips
Use ISERROR when you specifically need a TRUE/FALSE test, such as a separate audit column, conditional logic, or a way to distinguish a successful calculation from any error. It recognizes Excel error values such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. It also detects newer Excel error results when they are returned as actual errors.
For a straightforward replacement, IFERROR is often shorter. With the numerator in A2, the denominator in B2, and the desired result in C2, enter =IFERROR(A2/B2,"Check inputs") in C2. It returns the division result when there is no error and Check inputs when there is one. Unlike ISERROR, this does not expose a separate TRUE/FALSE test. IFERROR handles any error in its first argument, so choose the replacement carefully rather than hiding errors that should be investigated.
ISERROR is broader than ISERR: it also returns TRUE for #N/A, whereas ISERR excludes #N/A. This distinction matters in lookup formulas because a missing match commonly produces #N/A. If you want to handle missing matches differently from other errors, test specifically for that case rather than using a broad error check that treats every error the same way.
Keep error messages short and actionable. Check inputs is useful if someone should inspect the row; Not found makes sense for a missing lookup match. A blank replacement can make a report look cleaner, but it can also make failed calculations hard to notice. During review, consider keeping a visible status column until the source data has been checked.
When copying formulas, check whether each reference should move with the row. A2 and B2 are relative references and become A3 and B3 when filled down. In the VLOOKUP example, $A$2:$B$5 is absolute, so the search table stays fixed. On Excel for Mac, the function and formula syntax are the same; ribbon wording or layout can vary slightly by release. Excel for the web also supports ISERROR, though its interface is browser-based.
Troubleshooting
The formula returns FALSE, but the cell appears to show an error. Check whether the apparent error is actually text, perhaps imported from another system. Select the source cell and inspect the formula bar; also try testing the source cell itself. A displayed error-looking string stored as text will not make ISERROR return TRUE.
The formula returns TRUE, but you cannot see an error in the source. If the test wraps a calculation, Excel may be detecting an error generated during that calculation even though the source cells look normal. Check for a zero denominator, an invalid reference, incompatible value types, or a lookup key that is not present. Temporarily put the tested calculation in a separate cell to reveal its specific error.
The formula shows #NAME? instead of TRUE or FALSE. Confirm that the function is spelled ISERROR and that the formula begins with an equals sign. Check that the referenced cells and any named ranges exist. If you typed text labels inside an IF formula, enclose each label in straight double quotation marks, as in the examples, and make sure parentheses are balanced.
The IF formula still displays an error. Make sure the expression that can fail is inside the ISERROR test and that the IF branches are in the correct order. A common slip is placing the intended fallback in the third argument, which is used when the test is FALSE. Also test the underlying operation on its own; the source may contain an error for a reason unrelated to the condition you expected.
Filling down gives unexpected results. Inspect the formula in a later row. Relative references should change to that row, while a fixed lookup range should remain the same if it has dollar signs. If the lookup fails only on some rows, compare the keys for leading or trailing spaces, different data types, and spelling differences.
Frequently Asked Questions
What does ISERROR return in Excel?
It returns TRUE when the value or calculation supplied to it evaluates to an Excel error, and FALSE when it does not. It reports the error status rather than the specific error message.
Does ISERROR catch #N/A?
Yes. #N/A is an Excel error, so ISERROR returns TRUE for it. This makes the function useful for checking lookups that may not find a match.
What is the difference between ISERROR and IFERROR?
ISERROR returns TRUE or FALSE so you can test for an error. IFERROR returns the original result when it succeeds and a replacement value when it fails. Use the one that fits whether you need a status test or a fallback result.
Will ISERROR detect an error stored as text?
No. Text such as #VALUE! is not an actual Excel error value, even if it looks identical on screen. Convert or clean the imported data if you need to work with it as an error or as a valid value.
Can ISERROR check a whole range?
In Microsoft 365, an array formula can evaluate multiple values and may spill multiple TRUE/FALSE results into adjacent cells. Make sure the spill area is empty. For a simple row-by-row audit, putting one ISERROR formula in the first row and filling it down is often easier to review.
Is ISERROR available in Excel for Mac and Excel for the web?
Yes. ISERROR is a standard Excel function and works in Microsoft 365 on Windows, Mac, and the web. Formula syntax is the same, although the surrounding menus and interface can differ.
Related
- Excel Environmental Data Analysis: Methods and Examples
- Excel Solver Answer Report: Create and Read It
- How to Run Solver in Excel: Setup and Optimization
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort in Excel and Move Entire Rows Without Splitting DataTo sort in Excel and move entire rows together, select the full data range, then choose Data >…
- Excel Function to Check If a Cell Contains TextTo check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text…
- UPPER Excel Formula: Capitalize Text in Excel CellsTo make every letter in text uppercase in Excel, use the UPPER function. If the original text is…
- Excel MID Function: Extract Text From a CellIf the text you want to extract is in A2, use =MID(A2,5,5) to return five characters starting at…
- Power Query XLOOKUP From Another Table: How It WorksPower Query does not run the Excel XLOOKUP function inside a query. To return columns from another table,…
- Excel Filter by Cell Colour: Steps, Limits and FixesTo filter by cell colour in Excel for Microsoft 365 on Windows, select a cell in your data,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.