Excel practice › Excel errors › #DIV/0! error

#DIV/0! error in Excel: what it means and how to fix it

#DIV/0! means a formula divided by zero, or by an empty cell, which Excel counts as zero. The formula isn't broken: the same one works on every row where the divisor holds a number.

If a zero or an empty divisor is normal in your data, like a campaign with no clicks yet, tell the formula what to show for those rows. =IF(B5=0,0,C5/B5) divides only when there is something to divide by. =IFERROR(C5/B5,0) is shorter, and hides every other error too.

Skip to the 3 exercises ↓

The causes, in the order worth checking

  1. The divisor is 0.
  2. The divisor is an empty cell, often a row that isn't filled in yet.
  3. The formula divides by the wrong cell, usually after being copied down.
  4. AVERAGE over cells with no numbers in them.
  5. AVERAGEIF or AVERAGEIFS with no row that meets the condition.

1 and 2. The divisor is 0, or empty

Conversion rate is orders divided by clicks. The podcast spot has had no clicks yet:

Live example#DIV/0!: orders divided by 0 clicks
E5fx
ABCDE
1CampaignClicksOrdersRevenueConv. rate
2Spring email12406240300.05
3Search ads31009365100.03
4Referral4104132800.1
5Podcast spot000#DIV/0!
6Trade show182700

3. The formula divides by the wrong cell

Each region's share is its sales divided by the total in B6. C2 holds =B2/B6 and works. Copied down one row, both references moved, and the divisor is now B7, an empty cell under the total:

Live example#DIV/0!: the total reference slid down
C3fx
ABC
1RegionSalesShare
2East480000.3
3South32000#DIV/0!
4Midwest24000
5West56000
6Total160000

4. AVERAGE over cells with no numbers

AVERAGE adds the numbers in a range and divides by how many there are. It skips empty cells and text, so over a range with no numbers at all it divides by zero. The Design team hasn't been scored yet:

Live example#DIV/0!: an average of nothing
D4fx
ABCD
1TeamQ1 scoreQ2 scoreAverage
2Support4.24.54.35
3Sales3.84.13.95
4Design#DIV/0!
5Ops4.64.44.5

5. AVERAGEIF with no matching row

AVERAGEIF and AVERAGEIFS return #DIV/0! when no row meets the condition. Here, the average revenue of campaigns with more than 5,000 clicks, when no campaign has that many:

Live example#DIV/0!: no campaign over 5,000 clicks
F2fx
ABCDEF
1CampaignClicksOrdersRevenueAvg revenue
2Spring email1240624030#DIV/0!
3Search ads3100936510
4Referral410413280
5Podcast spot000
6Trade show182700

IF or IFERROR

Both remove the error from the cell. They don't do the same thing.

FormulaWhat it does
=IF(B5=0,0,C5/B5)Tests the divisor. Divides only when B5 isn't 0 or empty. Any other error in the formula still shows.
=IFERROR(C5/B5,0)Divides, and replaces any error with 0: #DIV/0!, but also #VALUE!, #REF! and #NAME?.

Use IF when division by zero is the one case you expect. It leaves a mistyped reference or a text cell visible. Use IFERROR for averages, where there is no single cell to test, and when the formula is long. Microsoft writes the IF test in a shorter form, =IF(B5,C5/B5,0): a divisor of 0 or an empty one counts as FALSE.

Then decide what to show. A 0 says "no orders per click", which isn't what an untested campaign means, and it pulls averages down. An empty text ("") or a short note is often the more honest choice.

Live exampleIF tests the divisor first
E5fx
ABCDE
1CampaignClicksOrdersRevenueConv. rate
2Spring email12406240300.05
3Search ads31009365100.03
4Referral4104132800.1
5Podcast spot000No clicks
6Trade show182700

Practice: 3 formulas to fix

Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.

Exercise 1A campaign with no clicks

=C5/B5 shows #DIV/0! for the podcast spot, which has 0 clicks. In E5, write a formula that returns the conversion rate (orders divided by clicks), or the text No clicks when the division isn't possible.

E5fx
ABCDE
1CampaignClicksOrdersRevenueConv. rate
2Spring email12406240300.05
3Search ads31009365100.03
4Referral4104132800.1
5Podcast spot000
6Trade show182700
Show the answer

=IFERROR(C5/B5,"No clicks") gives No clicks.

IFERROR returns the result of the division when there is one, and your text when the division ends in an error. =IF(B5=0,"No clicks",C5/B5) does the same and leaves other errors visible.

Exercise 2A total that slid away

C2 holds =B2/B6. It was copied down, and rows 3 and 4 were then corrected by hand. C5 still holds the copied formula, =B5/B9, and shows #DIV/0!. In C5, write the formula for the West's share of the total, with the total locked so it can be copied.

C5fx
ABC
1RegionSalesShare
2East480000.3
3South320000.2
4Midwest240000.15
5West56000
6Total160000
Show the answer

=B5/$B$6 gives 0.35.

$B$6 stays on the total wherever the formula is copied, and B5 keeps following its own row.

Exercise 3An average with nothing to average

=AVERAGEIF(B2:B6,">"&F2,D2:D6) averages the revenue of campaigns with more clicks than the number in F2. With 5000 in F2 no campaign qualifies, and it shows #DIV/0!. In G2, write a formula that returns the average, or the text None when no campaign qualifies.

G2fx
ABCDEFG
1CampaignClicksOrdersRevenueMin clicksAvg revenue
2Spring email12406240305000
3Search ads3100936510
4Referral410413280
5Podcast spot000
6Trade show182700
Show the answer

=IFERROR(AVERAGEIF(B2:B6,">"&F2,D2:D6),"None") gives None.

AVERAGEIF has no divisor you can test in a cell, so IFERROR is the practical fix: it returns the average when rows match and your text when none do.

Questions people ask

What does #DIV/0! mean in Excel?

The formula divides by zero. An empty cell counts as zero, so dividing by a cell that isn't filled in gives the same error. AVERAGE, AVERAGEIF and AVERAGEIFS show it too when they have no numbers to average.

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

First check that the formula divides by the right cell. If it does, and a zero or an empty divisor is expected, handle it: =IF(B5=0,0,C5/B5) or =IFERROR(C5/B5,0). Replace the 0 with "" for a cell that looks empty.

Why does AVERAGE return #DIV/0!?

There are no numbers in the range. AVERAGE skips empty cells and text, and with nothing left it divides by a count of zero. AVERAGEIF does the same when no row meets its condition. Wrap it in IFERROR to show something in that case.

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

IF when you can test the divisor: it handles the zero and leaves other errors visible. IFERROR when there is nothing simple to test, as with AVERAGEIF. IFERROR hides every error, so make sure the formula works on normal rows before you add it.

Keep going

Ten questions in a real spreadsheet, from SUM to INDEX/MATCH, with a level at the end and the lesson to start from. Free, no sign-up. Lesson 2, also free, is seven drills on IF.

Related

IFERROR and IFNA, with live examplesIF function, with live examplesAVERAGEIF and AVERAGEIFS functions, with live examples#VALUE! error in Excel: meaning and fixes#NAME? error in Excel: meaning and fixesExcel IF function practiceExcel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: How to correct a #DIV/0! error, Microsoft Support: AVERAGEIF function, Microsoft Support: IFERROR function. Last checked November 2026.