#DIV/0! in Excel: why it appears and how to fix it

#DIV/0! means a formula tried to divide by zero. An empty cell counts as zero, so dividing by an empty cell gives the same error.

No number divided by zero has an answer, in Excel or on paper. So Excel does not guess. It shows the error and waits for you to decide what the row should say.

One sheet, three rows with the error

This sheet works out a conversion rate. In D4 the formula is =C4/B4, sales divided by leads. It is filled down to D8.

The 3 errors look the same. They mean 3 different things.

• Social: no leads and no sales. The zero is real, and a rate of 0 is fair.
• Print: no leads but 2 sales. That cannot be true. Somebody forgot to type the leads.
• Radio: nothing typed yet. The formula was filled down past the data.

A good fix treats these 3 rows differently. That is why you look at each error before you hide it.

Test the divisor with IF

The clearest fix checks the number you divide by before Excel divides. This is the shape:

An empty cell passes the same test. =B8=0 is TRUE when B8 is empty. So one IF covers both a zero and a missing number.

A zero that should not be there

Fill that IF down to the Print row and it shows 0 there too. Print had 2 sales, so 0 is wrong. The sheet now looks fine, and the missing leads are hidden.

Give that case its own answer. Test the sales as well, inside the first IF:

Rows with no data yet

Some sheets have the formula filled down to row 100 before any data is typed. Then every empty row shows #DIV/0!.

Test for an empty cell and show nothing: =IF(B8="","",C8/B8). The Radio row now looks empty. When you type its leads and sales, the rate appears.

Two quotes with nothing between them are an empty text. SUM and AVERAGE skip text, so the totals still work.

Why not just use IFERROR?

=IFERROR(C7/B7,0) also removes the error. On the Print row it shows 0, with no warning. That is the same hidden problem as before.

IFERROR has a second weakness. It catches every error, not only #DIV/0!. Say somebody types text in the Leads column by mistake. =C4/A4 divides by the text Email and gives #VALUE!. IFERROR turns that into a quiet 0 too.

An IF that tests the divisor only catches the case you named. Every other error still shows. To fix that one, read what #VALUE! means.

One error spreads to the total

Put =SUM(D4:D8) under the rates and it shows #DIV/0! too. SUM cannot add an error. So one bad row breaks every total that reads it.

That is the real reason to guard the division. You fix the error in the row where it starts. Then the rest of the sheet can work.

A total rate is a different question anyway. Add up all sales, then divide by all leads: =SUM(C4:C8)/SUM(B4:B8) gives 0.08. That total leads number is not zero, so there is no error.

Other formulas that give #DIV/0!

• AVERAGE of no numbers. If every cell in the range is empty or text, AVERAGE has nothing to divide by.
• AVERAGEIF with no match. =AVERAGEIF(A4:A8,"TV",C4:C8) gives #DIV/0! because no row says TV.
• MOD with a zero. =MOD(10,0) gives #DIV/0!.
• Percent change from zero. =(G1-F1)/F1 works out growth. From 12 to 15 it gives 0.25. From 0 to 5 it gives #DIV/0!.

The last one needs a decision, not a formula. Growth from zero has no percent. Show a word like New instead of a number.

Questions people ask

Should the fallback be 0 or empty?

Use 0 when zero is the true answer, like the Social row. Use empty when there is no data yet. Use words when the data is wrong. Never use 0 just to make the error go away.

Why do I get #DIV/0! when the cell shows a number?

Check which cell the formula divides by. A formula copied down or across moves its references. It may now point at an empty cell next to the number.

Can I find every #DIV/0! in a sheet?

Yes. Press F5 and click Special. Choose Formulas, and leave only Errors ticked. Excel selects every formula that shows an error.