How to Fix DIV/0 Error in Excel

This guide shows you how to correct the #DIV/0! error in Excel, which appears when a formula tries to divide by zero or an empty cell. You’ll learn several methods, from simple IF checks to modern functions like IFERROR, so you can keep your spreadsheets clean and readable.

What You’ll Need

Microsoft Excel (2010 or later for IFERROR; 2007 or later for IF)
A spreadsheet containing the #DIV/0! error
Basic understanding of Excel formulas

Step-by-Step

Identify the error cell. Look for cells displaying #DIV/0!. Click on one to see its formula in the formula bar. Typically it looks like =A1/B1.
Check the divisor. The error occurs because the cell referenced as the divisor (the second cell in the division) is either 0, empty, or contains text. Confirm this by clicking on that cell.
Use the IF function (basic fix). Replace the formula with: =IF(B1=0, "", A1/B1). This checks if B1 is zero; if true, it returns an empty string (or a message like “N/A”), otherwise it performs the division.
Use IFERROR (simplest fix). Wrap your existing formula: =IFERROR(A1/B1, ""). This catches any error, including #DIV/0!, and replaces it with your chosen text or value.
Use IFNA (for VLOOKUP-related errors). If the error comes from a lookup, use =IFNA(VLOOKUP(...), "Not Found") to handle only #N/A errors, leaving other errors visible.
Use conditional formatting (visual fix). Select the range with errors, go to Home > Conditional Formatting > New Rule > Use a formula. Enter =ISERROR(A1) and set a font color matching the cell background to hide the error text.
Prevent the error at the source. Ensure your data entry doesn’t leave blank or zero cells where division occurs. Use data validation to restrict inputs, or pre-fill cells with a default non-zero value if appropriate.

Common Mistakes

Using IFERROR too broadly: It hides all errors, including #VALUE! or #REF!, which might indicate real problems. Use IFERROR only when you expect the error.
Forgetting to update cell references: When copying formulas, relative references change. Double-check that the divisor cell in the IF condition matches the one in the division.
Returning a text string when a number is expected: If downstream formulas sum or average the results, returning “” can cause issues. Consider returning 0 instead, but be aware that 0 can skew averages.

Tips

Use =IF(B1=0, 0, A1/B1) if you need a numeric result for further calculations.
For cleaner reports, return a dash or “N/A” instead of an empty string: =IFERROR(A1/B1, "N/A").
If you’re using Excel tables, structured references work the same way: =IFERROR([@Sales]/[@Units], "").
To quickly find all errors, press Ctrl+G > Special > Formulas > Errors, then apply fixes.

Troubleshooting

Error persists after using IF: Check that the divisor cell is truly zero, not text like “0” (which is text). Use the ISNUMBER function to verify: =ISNUMBER(B1) returns FALSE if it’s text.

IFERROR returns blank but you see a zero: This happens if the cell format is set to display zeros. Change the number format to General or custom like 0;-0;;@ to hide zeros.

Error appears in a pivot table: Pivot tables can show #DIV/0! when a calculated field divides by zero. Right-click the pivot table, select Options, and check “For error values show” to enter a custom message.

Formula works in one cell but not when copied: The divisor reference may be relative and changes when copied. Use absolute references (e.g., $B$1) if you want to keep the same divisor.

Frequently Asked Questions

Why does #DIV/0! appear even when the divisor is not zero?

The divisor might be a formula that returns an empty string or text. For example, if B1 contains =IF(C1="","",C1), it returns “” which Excel treats as zero in division.

Can I hide all errors at once?

Yes, use conditional formatting to make error text invisible, or go to File > Options > Advanced and uncheck “Show a zero in cells that have zero value” – but this only hides zeros, not errors. For errors, conditional formatting is the way.

What’s the difference between IFERROR and IFNA?

IFERROR catches all errors (#DIV/0!, #VALUE!, #N/A, etc.). IFNA only catches #N/A errors, which is useful when you want to hide #N/A from VLOOKUP but still see other errors.

How do I replace the error with a blank cell that doesn’t affect charts?

Use =IFERROR(A1/B1, NA()). This returns #N/A, which Excel charts ignore when plotting, leaving a gap instead of a zero.

Is there a way to prevent the error without changing every formula?

Yes, use the IF function in the divisor cell itself. For example, if B1 is a calculated value, wrap it: =IF(original_formula=0, 1, original_formula). This ensures B1 is never zero, but be careful as it changes the result.

Does the error affect other calculations?

Yes, any formula that references a cell with #DIV/0! will also show the error. It’s best to fix the source rather than hiding it.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →