Cellwise

Excel guide · 2 min read

#DIV/0! error in Excel

#DIV/0! appears when a formula divides by zero or by an empty cell. Fix it by checking the divisor first, =IF(B2=0,"",C2/B2), or by wrapping the formula in IFERROR, =IFERROR(C2/B2,0). AVERAGE and AVERAGEIF also show #DIV/0! when there are no numbers to average.

Sample data

Every formula on this page was calculated live against this sheet.

ABCD
1RepCallsDealsTeam
2Ava12018North
3Ben00South
4Cara2North
5Dev9511South

Why it happens

Ben made no calls, so his close rate is 0 divided by 0. Cara's calls cell is blank, and Excel treats a blank as 0 in math. Both give #DIV/0!.

fx=C3/B3

Result: #DIV/0!

B3 is 0, so the division returns #DIV/0!.

Fix 1: test the divisor with IF

Check for zero first and only divide when it is safe. This is the most precise fix, because it only handles the zero case and lets any other error show.

B4=0 is also TRUE for a blank cell, so one test covers both. Return "" for a blank-looking result or 0 if you will total the column.

fx=IF(B4=0,"",C4/B4)

Result:

Cara's calls cell is blank, which counts as 0, so the formula returns an empty result.

Fix 2: IFERROR for a quick catch-all

IFERROR is shorter and handles the error whatever caused it. The trade-off: it also hides other problems, such as text in the calls column.

fx=IFERROR(C3/B3,0)

Result: 0

Ben's division fails, so IFERROR returns 0.

AVERAGEIF with no matching rows

Averages divide too. If no row matches the criteria, AVERAGEIF has nothing to divide by and returns #DIV/0!. Here there is no "West" team.

fx=AVERAGEIF(D2:D5,"West",C2:C5)

Result: #DIV/0!

No rows match West, so there is nothing to average and the result is #DIV/0!.

Fix: give the empty case a label

Wrap the average in IFERROR with a message that makes sense to the reader. "No data" is clearer than a 0, which looks like a real average of zero.

fx=IFERROR(AVERAGEIF(D2:D5,"West",C2:C5),"No data")

Result: No data

Still no West rows, but now the cell reads No data.

Which fix to choose

Use IF when you know the divisor can legitimately be zero or blank, like a rep who made no calls yet. It handles exactly that case and nothing else. Use IFERROR in quick summaries where any error should show the same fallback.

Also think about what the reader needs to see. In a dashboard, a dash or "n/a" tells people the rate cannot be calculated. In a column you will later average, return "" rather than 0, because AVERAGE ignores text and empty strings but counts zeros, which would drag the average down.

Common mistakes

Returning 0 when the true answer is "unknown"

A 0% close rate and "no calls made" mean different things. Use "" or "n/a" in reports so nobody misreads it.

Checking the wrong cell

Test the divisor (the number after the /), not the numerator. =IF(C2=0, ...) does not prevent dividing by B2.

Fixing the formula but not the data

If calls should never be blank, the real fix is filling them in. Use Data > Data Validation to stop blanks being entered.

FAQ

Why does Excel say #DIV/0! when the cell is empty?

Excel treats an empty cell as 0 in arithmetic, so dividing by it is dividing by zero. A test like IF(B2=0, ...) catches both zeros and blanks.

How do I average a range and skip the #DIV/0! errors?

Fix the source cells with IFERROR, or use =AGGREGATE(1,6,range), which averages while ignoring error values.

Should I use IF or IFERROR for #DIV/0!?

Use IF when you know the divisor might be zero, because it handles exactly that case. Use IFERROR when you just need a clean output and accept that it hides other errors too.

Functions used

Keep learning

Updated 2026-10-31