#DIV/0! error in Excel: what it means and how to fix it
#DIV/0! means a formula divided by zero or by an empty cell. Why it happens, averages of nothing included, and how to show a clean result, with a practice sheet.
IFERROR function
IFERROR returns a formula's result, or a value you choose when the formula returns an error: =IFERROR(B2/C2, 0) shows 0 instead of #DIV/0!.
Available in Excel 2007 and later, Excel for the web and Google SheetsOn this page
IFERROR syntax
=IFERROR(value, value_if_error)| Argument | Required | What it means |
|---|---|---|
value | Yes | The formula to check. |
value_if_error | Yes | What to show instead if it returns any error. |
Examples
Avoid #DIV/0! in a ratio
Conversion rate is orders ÷ visits. A day with no visits would divide by zero; IFERROR shows 0 instead.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Day | Visits | Orders | Conversion |
| 2 | Mon | 0 | 0 | 0.0% |
| 3 | Tue | 400 | 12 |
=IFERROR(C2/B2,0) → 0.0%A friendly message for a lookup
The code isn't in the list, so VLOOKUP alone would show #N/A.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Code | Price | Find | Price | |
| 2 | A-101 | 49 | A-999 | Check the code | |
| 3 | A-102 | 19 |
=IFERROR(VLOOKUP(D2,A2:B3,2,FALSE),"Check the code") → Check the codeCommon mistakes
- Hiding errors you need to see. IFERROR catches every error, including a typo that returns #NAME? or a broken reference. For lookups, IFNA only catches "not found" and lets real mistakes show.
- Returning "" where a number is expected. An empty string is text, so later maths on it can return #VALUE!. Return 0 if the result feeds a calculation.
FAQ
What's the difference between IFERROR and IFNA?
IFERROR replaces every error. IFNA only replaces #N/A, the "not found" error from lookups, so a typo or broken reference still shows up.
Does IFERROR slow down a workbook?
Not noticeably for normal sheets. The bigger cost is hidden errors: a wrong formula wrapped in IFERROR looks fine.
Guides that use IFERROR
Worked problems with practice sheets.
#VALUE! error in Excel: what it means and how to fix it
#VALUE! means a formula got the wrong kind of value, most often text where it needs a number. The usual causes and fixes, with a practice sheet.
#N/A error in Excel: what it means and how to fix it
#N/A means a lookup didn't find what it was looking for. Here are the six usual causes, how to spot each one on the sheet, and the fix, with a practice sheet.
Know IFERROR? Prove it on a real sheet.
Practise IFERROR and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install