#DIV/0! Error in Excel and How to Avoid It

The #DIV/0! error in Excel appears when a formula divides by zero, or by a cell that is empty. It's the most predictable error in Excel: it shows up in averages per order, conversion rates and percent change whenever the bottom number is missing. This guide shows the causes, the two standard fixes, and how to choose what the cell should show instead.

What does #DIV/0! mean in Excel?#

#DIV/0! means "divided by zero". Dividing by zero has no answer in math, so Excel shows an error instead of a number.

Excel treats an empty cell as 0 in a division, so these all return #DIV/0!:

If B2 holds text such as "n/a" instead, the result is #VALUE!, a different error covered in our #VALUE! guide.

Fix 1: check the divisor with IF#

The most precise fix tests the bottom number before dividing. Use IF:

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

• B2=0 is the test. It's TRUE when B2 is 0 or empty.
• "" is what to show when the test is TRUE: empty text, so the cell looks blank.
• A2/B2 is the normal calculation, used only when B2 isn't 0.

Fill it down and every row with a missing divisor shows a blank, while every other row shows its normal result. You can show anything in the second argument: 0, "n/a" or "No sales".

Fix 2: catch the error with IFERROR#

IFERROR is shorter: it returns the normal result unless the formula produces an error.

=IFERROR(A2/B2,"")

This works well, and it's what many people use. The catch is that IFERROR catches every error. If A2 contains a typo that causes #VALUE!, or the formula has a misspelled name that causes #NAME?, IFERROR hides that too, and you see a tidy blank instead of a warning.

Use IF when you want other mistakes to stay visible. Our guide on using IFERROR the right way covers more patterns.

Percent change when the old value is 0#

Percent change is (new - old) / old, so it fails when the old value is 0. That's not just a technical error: growth from nothing has no meaningful percentage.

Label those rows instead of forcing a number:

=IF(B2=0,"New",(C2-B2)/B2)

Row 2 shows New and row 3 shows 10% (0.1 formatted as a percentage). Sales and operations reports use this pattern all the time; see Excel skills for sales and operations roles and our percent change formula guide for the full method.

AVERAGE, AVERAGEIF and #DIV/0!#

AVERAGE divides a total by a count. When there are no numbers to count, it divides by zero.

• =AVERAGE(D2:D10) returns #DIV/0! if D2:D10 is empty or contains only text.
• =AVERAGEIF(B2:B20,"West",C2:C20) returns #DIV/0! if no row says West.
• =AVERAGEIFS(…) does the same when no row meets every condition.

Fix them the same way. Either count first:

=IF(COUNTIF(B2:B20,"West")=0,"No orders",AVERAGEIF(B2:B20,"West",C2:C20))

or wrap the average in IFERROR: =IFERROR(AVERAGEIF(B2:B20,"West",C2:C20),"No orders").

Should you show 0, a blank or a label?#

Choose based on what happens to the cell next. The replacement isn't just cosmetic: other formulas and charts read it.

• A blank ("") is the safest default. AVERAGE, SUM and MAX ignore text, so blanks don't distort them. But a formula that does math directly on the cell, like =D2*2, returns #VALUE! on "".
• 0 looks like a real result. If the average price of a product with no sales shows 0, an average of that column is pulled down.
• A label like "New" or "No data" is clearest for people reading the report. Like a blank, it's text, so math on it fails.

When in doubt, use a blank in calculation columns and a label in summary tables people read.

Find and fix every #DIV/0! in a report#

In a long report, fix the errors in one pass instead of cell by cell.

1. Find them. Press Ctrl+F (⌘F on a Mac), type #DIV/0!, click Options, set Look in to Values and click Find All. The list shows every cell that currently displays the error.
2. Decide if it's expected. A row for a product with no sales yet is expected. A divisor that should never be 0, like the number of working days in a month, points to missing or wrong data, so fix the data instead.
3. Fix the first formula, then fill. Edit the top cell of the column, for example to =IF(B2=0,"",A2/B2). Then select it and the cells below and press Ctrl+D (⌘D on a Mac) so every row uses the same rule.

A common example is a conversion rate in a weekly report: orders divided by visits. With visits in column B and orders in column C, weeks with no visits yet show #DIV/0! until the data arrives. =IF(B2=0,"",C2/B2) keeps those rows clean without hiding real problems in the others.

Other errors that look similar#

#DIV/0! is about the divisor. If the math itself is impossible for another reason, such as the square root of a negative number, you get the #NUM! error instead. If Excel doesn't recognise a function name in your formula, you get the #NAME? error.

Practise it#

Fix the growth formula in the exercise on this page. Then practise IFERROR in a live spreadsheet, where two products sold nothing, and work through the percentages and % change lesson to build growth formulas step by step.