#DIV/0! Error in Excel: Why It Happens and 4 Ways to Fix It

#DIV/0! means a formula is dividing by zero or by an empty cell. Sometimes that's expected (no data yet), sometimes it's a sign the formula is pointing at the wrong cell. Tell them apart first, then fix.

Cause 1: the reference moved (a real mistake)

You divide by a total and fill down:

=B2/B14     row 2: fine
=B3/B15     row 3: B15 is empty → #DIV/0!

The total should be locked:

=B2/$B$14

This is by far the most common cause in real sheets and job tests. See absolute references.

Cause 2: no data yet (expected)

Margin = profit ÷ sales, but some months have no sales yet. The error is "correct" but ugly.

Fix 1: IF the divisor is zero, show blank

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

Clear and explicit. Treats empty cells as 0 too.

Fix 2: IFERROR

=IFERROR((B2-C2)/B2,"")

Shorter, but it hides every error, not just division by zero. See IFERROR.

Fix 3: show a message

=IF(B2=0,"No sales",(B2-C2)/B2)

Better than a blank when someone needs to know data is missing.

Fix 4: AVERAGEIF returning #DIV/0!

AVERAGE and AVERAGEIF return #DIV/0! when there are no numbers to average. Wrap them:

=IFERROR(AVERAGEIF(A2:A100,F2,B2:B100),"No data")

Don't replace it with 0

Showing 0% margin when there were no sales is misleading, and zeros pull averages down. Prefer a blank or a label.

Practise it

Cellio's feedback catches this one specifically: if your formula divides by an empty cell, it tells you so, and the fill-down exercises show exactly which copies broke.

Frequently asked questions

What does #DIV/0! mean?

The formula divides by zero or by a blank cell.

How do I get rid of #DIV/0! in Excel?

Use =IF(divisor=0,"",formula) or =IFERROR(formula,"").

Why do I get #DIV/0! after filling down?

The divisor reference moved to an empty cell. Lock it with $.

Does #DIV/0! appear in Google Sheets?

Yes, with the same meaning and fixes.