This guide shows you how to hide error values like #N/A, #DIV/0!, and #VALUE! in Microsoft Excel using the IFERROR function. You’ll learn the exact formula structure, how to apply it to existing formulas, and what to do when the error is in a cell you don’t control. No prior Excel formula experience is needed, but you should know how to select cells and type basic formulas.
What You’ll Need
- Microsoft Excel 2010 or newer (the steps are the same for Excel 365, Excel 2021, and Google Sheets).
- A worksheet that currently shows error values (e.g., #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!).
- Basic understanding of how to edit a formula (click the cell, look at the formula bar).
Step-by-Step
- Identify the cell with the error. Click on the cell that shows the error. Look at the formula bar at the top of Excel – you’ll see the current formula, like
=VLOOKUP(A2, B:C, 2, FALSE)or=A2/B2. - Wrap the existing formula with IFERROR. The IFERROR function takes two arguments: the value to check (your original formula) and the value to return if it’s an error. The general structure is:
=IFERROR(your_formula, value_if_error) - Type the new formula. In the formula bar, replace the existing formula with
=IFERROR(, then paste or retype the original formula, then add a comma and the value to show instead of the error. For example, if your original formula was=VLOOKUP(A2, B:C, 2, FALSE), type:=IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not Found")
This will show the text “Not Found” instead of #N/A when the lookup fails. - Choose what to display instead of the error. Common options:
– A blank cell: use two double quotes with nothing between them:""
– A zero: type0
– A custom message: type text in quotes, e.g.,"Data missing"
– Another calculation: e.g.,0or100– anything that makes sense for your data. - Press Enter. The error should disappear, replaced by your chosen value. If you still see an error, check the syntax – you likely missed a closing parenthesis.
- Copy the formula to other cells. If you need the same fix for a whole column, use the fill handle (the small square at the bottom-right of the selected cell) and drag down. The IFERROR will apply relative to each row.
- For formulas that return arrays (Excel 365), use IFERROR the same way. For example,
=IFERROR(FILTER(A2:A10, B2:B10>5), "No results")works in Excel 365 and Excel 2021.
Common Mistakes
- Forgetting a closing parenthesis. IFERROR needs two arguments and two closing parentheses – one for the inner formula, one for IFERROR itself. Excel will often auto-correct, but double-check.
- Using IFERROR on a formula that already contains IFERROR. This is legal but can make formulas hard to read. Avoid nesting more than one or two levels.
- Hiding errors that are actually important. IFERROR hides all errors, including ones that indicate a real problem (e.g., #REF! from a deleted column). Only use it when you expect occasional errors like #N/A from lookups.
- Putting the comma in the wrong place. The comma separates the formula from the replacement value.
=IFERROR(A2/B2, "")is correct;=IFERROR(A2/B2 "")is not.
Tips
- Use a blank string for a clean look.
""makes the cell appear empty, which is often better than showing “0” for missing data. - Combine with ISERROR for more control. If you need to check for errors but do something different for each type, use
=IF(ISERROR(A2/B2), "Error", A2/B2)– but IFERROR is simpler for most cases. - Apply to VLOOKUP and INDEX/MATCH. These are the most common sources of #N/A errors. Wrapping them with IFERROR is a standard practice.
- For Excel 2007 and earlier, use IF(ISERROR(…)) because IFERROR was introduced in Excel 2007. For older versions, the syntax is
=IF(ISERROR(your_formula), value_if_error, your_formula)– note you repeat the formula twice. - Use IFNA for VLOOKUP specifically. If you only want to hide #N/A (not other errors), use
=IFNA(VLOOKUP(...), "Not Found"). This leaves other errors visible.
Troubleshooting
The error is still showing. Check that you typed the formula correctly. Click the cell and look at the formula bar – it should start with =IFERROR(. If it doesn’t, you may have overwritten the formula. Also, make sure the cell is not formatted as text (right-click > Format Cells > General).
The cell shows the formula as text. This usually means you missed the equals sign at the beginning. Type = before IFERROR.
IFERROR is not available. If you’re using Excel 2003 or earlier, IFERROR doesn’t exist. Use the older =IF(ISERROR(...)) method. In Google Sheets, IFERROR works exactly the same.
The formula returns a #NAME? error. This means Excel doesn’t recognize IFERROR – you might have a typo (e.g., IFFERROR) or you’re in an older version. Double-check the spelling.
Errors appear in conditional formatting or data validation. IFERROR only works in formulas that return values to cells. For conditional formatting rules, you’ll need to use the ISERROR function inside the rule formula.
Frequently Asked Questions
Does IFERROR hide all types of errors?
Yes, IFERROR catches every error type: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. If you need to hide only specific errors, use IFNA for #N/A or IF(ISERROR(…)) with a condition.
Can I use IFERROR to show a blank cell instead of an error?
Yes, use "" (two double quotes) as the second argument. For example: =IFERROR(A2/B2, "") will show an empty cell.
What’s the difference between IFERROR and IF(ISERROR(…))?
IFERROR is shorter and evaluates the formula only once. IF(ISERROR(…)) requires you to repeat the formula twice, which is longer and can slow down large spreadsheets. Use IFERROR whenever possible.
Will IFERROR slow down my workbook?
Minimally. IFERROR adds a tiny overhead, but it’s negligible for typical use. If you have thousands of cells with complex IFERROR formulas, you might notice a slight slowdown, but it’s usually not significant.
Can I use IFERROR in charts or pivot tables?
For charts, you can’t directly wrap a chart’s data source with IFERROR – you need to fix the underlying cells. For pivot tables, you can use IFERROR in a calculated field, but it’s often easier to adjust the pivot table options to hide errors.
Does IFERROR work in Google Sheets?
Yes, Google Sheets has the same IFERROR function with identical syntax. The steps in this guide apply to both Excel and Google Sheets.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Google Photos Backup Stuck? Fixes That Work
- Record Your Own Alarm Sound on Android
- Transfer Music from ZTE to iPhone: Full Guide
- Angel Investor vs Venture Capitalist: Key Differences
- Sync Apps Between Android Devices Using Google Play
- Find What Drains Your iPhone Battery Fast