#DIV/0!Excel error guide No upload

#DIV/0! in Excel — Division by zero

#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

The short answer

#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.

What it looks like

A ratio, average or percentage formula shows #DIV/0!
The error appears only on rows where the denominator is blank
A dashboard KPI turns into an error when there is no data

Why #DIV/0! happens

The denominator cell is zero.
The denominator cell is empty — Excel evaluates it as zero.
A lookup returned #N/A or blank, which then became the denominator.
A formula refers to a row that has been filtered out or not filled in yet.
A calculated total is zero because the source range is empty.

How to fix #DIV/0!

1
Guard the denominator
Test the denominator before dividing. This keeps a real zero visible as blank instead of turning the whole report into an error.
```
=IF(B2=0,"",A2/B2)
```
2
Use IFERROR for reports
IFERROR is shorter and also catches other errors, which is useful on dashboards. Do not use it to hide a mistake in the source data.
```
=IFERROR(A2/B2,"")
```
3
Filter out blank rows
If blank rows are normal, calculate only rows with a denominator: =IF(B2="","",A2/B2). For a whole column, use FILTER or a pivot table.
4
Check the lookup feeding the denominator
A missing lookup may return 0 or blank. Fix the lookup first, then add the divide-by-zero guard.
```
=IFERROR(A2/XLOOKUP(D2,$A$2:$A$500,$B$2:$B$500),"")
```

A safer formula pattern

=IF(B2=0,"",A2/B2)

Shows a blank instead of #DIV/0! when the denominator is zero or empty.

Check it in your own workbook

Paste the formula into the free browser tools to see the exact fix without uploading your file. The Error Fixer explains the code, the Formula Checker finds syntax problems, and the Formula Explainer breaks down nested logic.

Open the interactive Error Fixer Check a formula

Related functions

IF IFERROR AVERAGEIF SUMIF

Frequently asked questions

What does #DIV/0! mean in Excel?

#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.

How do I fix #DIV/0! in Excel?

Guard the denominator: Test the denominator before dividing. This keeps a real zero visible as blank instead of turning the whole report into an error. Use IFERROR for reports: IFERROR is shorter and also catches other errors, which is useful on dashboards. Do not use it to hide a mistake in the source data.

Why does #DIV/0! appear only in some rows?

Usually because only some rows contain the trigger: The denominator cell is zero. The denominator cell is empty — Excel evaluates it as zero. A lookup returned #N/A or blank, which then became the denominator.

How can I stop #DIV/0! from appearing again?

Shows a blank instead of #DIV/0! when the denominator is zero or empty. Use this pattern as a guard: =IF(B2=0,"",A2/B2)

Related Excel error guides

All error codes →

#VALUE!Wrong value type#VALUE! means Excel expected a number, date or logical value but found text, an empty cell or a value from another data type. The fix is to convert the input, remove hidden characters, or guard the calculation with IFERROR.#REF!Broken cell reference#REF! means a formula points to a cell, row, column or sheet that no longer exists. It usually appears after deleting rows or columns, moving a sheet, or pasting a formula into a workbook that does not contain the referenced sheet.#N/ALookup value not found#N/A means a lookup or match function could not find the value. The value may be missing, spelled differently, stored as text instead of a number, or surrounded by spaces. Use exact-match mode and return a clear fallback.#NAME?Unknown function or name#NAME? means Excel does not recognise a function, named range or reference in the formula. The usual causes are a misspelled function, missing quotation marks around text, or a function that is not available in your Excel version.

Still stuck?

Send the workbook and get the formula fixed

A human Excel expert can repair the formula, explain the cause and return a tested workbook — usually within 24 hours. Fixed quote before work starts, from $15/hr.

Get a fixed quote Ask on WhatsApp

#DIV/0! in Excel — Division by zero

#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

How to fix #DIV/0!

Guard the denominator

Test the denominator before dividing. This keeps a real zero visible as blank instead of turning the whole report into an error.

=IF(B2=0,"",A2/B2)

Use IFERROR for reports

IFERROR is shorter and also catches other errors, which is useful on dashboards. Do not use it to hide a mistake in the source data.

=IFERROR(A2/B2,"")

Filter out blank rows

If blank rows are normal, calculate only rows with a denominator: =IF(B2="","",A2/B2). For a whole column, use FILTER or a pivot table.

Check the lookup feeding the denominator

A missing lookup may return 0 or blank. Fix the lookup first, then add the divide-by-zero guard.

=IFERROR(A2/XLOOKUP(D2,$A$2:$A$500,$B$2:$B$500),"")

Frequently asked questions

What does #DIV/0! mean in Excel?

#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.

How do I fix #DIV/0! in Excel?

Why does #DIV/0! appear only in some rows?

How can I stop #DIV/0! from appearing again?

Shows a blank instead of #DIV/0! when the denominator is zero or empty. Use this pattern as a guard: =IF(B2=0,"",A2/B2)