exceltestpracticeStart free
Practice testsLearnFunctionsShortcutsPricingStart free

Excel errors

#DIV/0! error in Excel

A formula divided by zero, or by an empty cell, which Excel treats as zero.

What #DIV/0! means

#DIV/0! is the most literal error in Excel: something was divided by zero. Anywhere a formula has a denominator, whether it is a slash, a percent change, or an average, a zero or empty value in that position produces it.

Blank cells are the sneaky part. In arithmetic, Excel treats an empty cell as 0, so =A2/B2 with B2 blank fails exactly like a typed zero. That is why the error often appears when you copy a formula down past the end of your data and leave the extra rows empty.

AVERAGE is the other frequent source. It does not divide by a zero you can see, but by the number of numeric cells it found, and when that count is zero the same error comes back from inside the function.

Causes of #DIV/0! and how to fix each

1. The divisor is zero

Excel cannot divide by zero, so any formula where the denominator evaluates to 0 returns #DIV/0!. This is common in ratio columns where one row has a zero total, such as conversion rate with no visitors.

A rate with zero visitors. The formula =C3/B3 in cell D3 returns #DIV/0!.
ABCD
1PageVisitorsSales
2Home20010
3Promo00#DIV/0!

Fix. Decide what a zero denominator should show. An empty string or "n/a" is more honest than 0%, because there is no rate to report. Guard the division with IF so it only runs when the denominator is not zero.

Guarding the division with IF. The formula =IF(B3=0,"",C3/B3) in cell D3 returns .
ABCD
1PageVisitorsSales
2Home20010
3Promo00

When B3 is 0 the formula returns an empty string instead of attempting the division. Row 2 would still calculate 10 divided by 200.

2. The divisor cell is empty

A blank cell counts as zero in arithmetic, so dividing by one gives the same error. This usually happens when a formula is filled down past the end of the data, or when an input has not been entered yet.

Dividing by a cell nobody has filled in. The formula =A2/B2 in cell C2 returns #DIV/0!.
ABC
1TotalPeople
2120#DIV/0!

Fix. Fill in the missing input, or limit the formula to rows that have data. A guard that tests for a blank, such as IF(B2="","",A2/B2), keeps unfinished rows tidy.

3. AVERAGE of a range with no numbers

AVERAGE adds the numbers it finds and divides by how many it found. If the range holds only text or blanks, that count is zero and the division fails inside the function.

Averaging a column of text. The formula =AVERAGE(A1:A2) in cell B1 returns #DIV/0!.
AB
1n/a#DIV/0!
2n/a

Fix. Check that the range really holds numbers (they should be right-aligned, not text), or return a message when there are none: IF(COUNT(A1:A2)=0,"No data",AVERAGE(A1:A2)). The same applies to AVERAGEIF and AVERAGEIFS when no row meets the criteria.

Trapping #DIV/0! in a formula

Prefer an IF test over IFERROR when the only expected failure is a zero denominator. IF states the rule you mean, and it leaves a typo or a bad reference visible instead of covering it.

IFERROR is fine for quick work where you control the data, and it is shorter. Be consistent within a column, and do not return 0 for a missing rate, because a 0% rate and an unknown rate are different statements and a later average will treat them the same.

IFERROR with a text fallback. The formula =IFERROR(C3/B3,"n/a") in cell D3 returns n/a.
ABCD
1PageVisitorsSales
2Home20010
3Promo00n/a

The shorter form gives the same visible result here. It would also hide a mistake, such as pointing at the wrong column.

#DIV/0! on Excel job tests

For a ratio column (conversion rate, margin percent, price per unit), scan the denominator column for zeros and blanks before you fill down. If the instructions do not say what to show for those rows, return a blank or "n/a" and say so in a note cell.

Averages of filtered or conditional data can fail the same way. If AVERAGEIF returns #DIV/0!, check that your criteria match at least one row, including capitalization-insensitive text and trailing spaces.

  • IFReturns one value if a condition is true and another if it is false.
  • IFERRORReturns a value you choose when a formula results in an error.
  • AVERAGEReturns the arithmetic mean of numbers.
  • AVERAGEIFAverages the values in a range that meet one condition.
  • COUNTCounts the cells that contain numbers.
  • #N/AThe lookup came back empty-handed: Excel searched for a value and it is not there.
  • #VALUE!A formula got a kind of value it cannot use, most often text where it needed a number.

#DIV/0! questions

What does #DIV/0! mean?
The formula tried to divide by zero or by an empty cell. Excel has no defined answer for that, so it shows the error.
How do I hide #DIV/0! in Excel?
Wrap the division in IF so it only runs when the denominator is not zero, for example IF(B2=0,"",A2/B2). IFERROR also works if you only expect this one failure.
Why does AVERAGE give #DIV/0!?
There are no numbers in the range. AVERAGE ignores text and blanks, so a range full of text has a count of zero.
Does a blank cell count as zero?
In arithmetic, yes. =A1/B1 with B1 empty divides by zero. In COUNT and AVERAGE, empty cells are skipped rather than counted as zero.

Practice working with errors

The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.

Try a 5-item Excel test sample