How to Fix the #DIV/0 Error in Excel (Step-by-Step)

We all have used Excel to perform divisions, haven’t we? If you have done the same, I bet you have come across the #DIV/0 error in Excel.

Just like it appears to be, the #DIV/0 error is Excel’s way of saying there’s some problem with your division formula 🚫

What is that problem? And how can you fix it? Get the answer to all these and many more questions about the #DIV/0 error in the guide below.

To tag along with the guide, download our sample workbook here.

What causes the #DIV/0 error

There are two main reasons why the #DIV/0 error might occur in Excel.

  1. When a number in Excel is divided by zero (or any value equal to zero).
  2. When a formula has a reference to a cell containing the #DIV/0 error.

Let’s look into each of these causes below.

Division by zero

Same rule from the math classes of our early childhood ➗

“A number is not divisible by zero”.

That’s because a number divided by zero is undefined. You might have forgotten this, but Excel has got a sharp memory.

So, if you write the following formula in Excel:

= 9 / 0


Dividing a number by zero in Microsoft Excel

The results would be as bizarre as follows:

The #DIV/0 error occurs

Similarly, if you a divide number or a cell by a blank cell:

Dividing a number by an empty cell

You’d still end up with a #DIV/0 error as follows:

The #DIV error message

A referred cell contains the #DIV/0 error

Another reason why you might face the #DIV/0 error in Excel is referring to a cell that contains the #DIV/0 error.

For example, write a simple SUM Function in Excel as follows:

= SUM (A2:A4)

A cell value is #DIV

Note that one of the cells of the referred range (A2 to A4) contains the #DIV/0 error 🤷‍♀️

Kasper Langmann, Microsoft Office Specialist

Here are the results:

DIV error due to cell reference

The SUM function returns the #DIV/0 error.

That’s because in the formula = SUM (A2:A4) we have referred to a cell (A3) that contains a DIV/0 error.

So, even if your formula is all correct. But creating a reference to another cell containing the #DIV/0 error can still cause the #DIV/0 error to occur 🤨

How to fix the #DIV/0 error

The #DIV/0 error is primarily caused by empty cells or cells with a zero. And so, you can avoid facing this error by ensuring the following:

  1. Your formulas do not refer to any cells containing the #DIV/0 error.
  2. You don’t perform the division operation using any empty cells.
  3. The cells in your dataset do not contain zeros.

All of these can help you prevent the #DIV/0 error, but how can you fix it once the #DIV/0 error appears on your sheet?

Here are a few ways how you can avoid the #DIV error in Excel 🧹

Using the IFERROR function

Take the following data set as an example:

Details for different cars

We have a list of cars with their monthly maintenance cost and the number of kilometers run by each car per month.

Some cars haven’t run any kilometers during the month so their corresponding kilometers are zero/blank. But there’s still some maintenance cost for that car 🚕

Kasper Langmann, Microsoft Office Specialist

Can we readily find the maintenance cost per kilometer for each car?

For sure, that’s simple.

  1. Write a simple division operation as below:

= B2 / C2

Maintenance cost divided by the kilometers
  1. Drag and drop the same to the list below.
#DIV error returned by Excel for zero/blank value

And we know the maintenance cost per kilometer for each car, except for a few.

For Car B and Car D, as there were zero/no kilometers, the answer we have is a #DIV/0 error 🚩

In this case, you can avoid getting the #DIV/0 error by using the IFERROR function.

  1. Wrap the above formula into the IFERROR function as below:

= IFERROR (B2/C2,”Zero kilometers run”)

If statement set to Zero kilometers run

The IFERROR function will run the formula =B2/C2. If the formula results in an error, it will replace the error with the supplied value (“Zero kilometers run” in this case).

If the formula doesn’t return an error, it will simply return the result of the formula = B2/C2.

The IFERROR function works the same for all errors in Excel. Be it #VALUE, #REF, #NAME, #CALC, or whichever error 💪

Kasper Langmann, Microsoft Office Specialist
  1. Hit Enter.
Excel replaces formula errors with the value

The results no more show the #DIV/0 error – we are all good now!

Instead, cells that earlier showed the #DIV/0 error, now show the value supplied in the formula.

The IFERROR is a lifesaver, isn’t it ✨

Pro Tip!

Another way of suppressing the #DIV/0! error is to use the ISERROR function together with the IF function.

The ISERROR returns TRUE where there is an error and FALSE where there isn’t. Nest it into the IF function to replace the error with any desired value.

The FIND and REPLACE dialog box

The IFERROR function can be used to replace errors in Excel when you are applying a formula/function from scratch.

But what if you have widespread data and you can’t go back to check each cell for the #DIV/0 Error? Here’s the fix 🩹

error values in excel

To replace the #DIV errors in the image above:

  1. Press the Control Key + H to launch the Find and Replace dialog box.
  2. In the Find box, type “#DIV/0!”
  3. Against the Look In box, select “Values”
The find and replace dialog box
  1. Press the “Find All” button.

Excel highlights all instances of occurrence of the #DIV/0! Error.

Excel identifies all the zero errors

Once you have identified these cells, you can go to each of them by scrolling through the blue list and clicking on each item 🖱

Make up for the cause of the error, and your sheet is all sorted.

Examples of formulas that return the #DIV/0 error

We have broadly covered the causes of the #DIV/0 error in Excel along with their solutions.

Now let’s look into a few functions that are likely to return the #DIV/0 error if misapplied.

AVERAGE Function

What is the average function about?

= Sum of numbers / Count of those numbers

As the function primarily involves the division, it is likely to return a #DIV/0! Error if the denominator is zero.

For example, if you AVERAGE out a range of text values in Excel 👇

Averaging a range of text values

The answer would be a #DIV/0 error. This is because the range contains no numeric value – Excel cannot count the numbers to be used in the denominator.

So it assumes them to be zero. And the result we have is the #DIV/0! Error.

Even with other average functions like AVERAGEA, AVERAGEIF, and AVERAGEIFS, you will face the same problem.

Kasper Langmann, Microsoft Office Specialist

MOD function

The MOD function of Excel is used to find the remainder after a number is divided by a divisor.

Pro Tip!

What does the MOD function do? Think what is 10 divided by 7 in whole numbers?

7 one times is 7

7 two times is 14

So we can only take one time 7 within 10. And if we divide 10 by 7, we get 1 and a remainder of 3 (10 less 7) 3️⃣

As the MOD function is a division function, it would return a #DIV/0 error if the divisor is 0. Let’s see it here:

  1. Write the MOD function in Excel as follows:

= MOD (10,

Writing the MOD function

Where 10 is the number to be divided by a divisor.

  1. As the second argument, write the divisor by which the number must be divided.

= MOD (10,0)

Writing the divisor for the MOD function

We have specified the divisor as 0.

  1. Press Enter to see the results.
MOD function returns an error value

And just as expected – Excel returns the #DIV/0 error as the divisor was 0 🥴

That’s it – Now what?

So here we are! In the guide above, we have seen all about the #DIV/0 error – the causes, the fixes, some examples, and some pro tips!

With all of this, you now know how to deal with the situation when Excel throws the #DIV/0 error your way. Don’t you?

While it is important to know about Excel errors and their solutions, note that this will only help you when you first know how to run functions in Excel 🚴‍♀️

Excel has a wide variety of functions to offer. Some very important functions of Excel include the VLOOKUP, SUMIF, and IF functions.

To learn them, enroll in my free 30-minute email course that will teach you these (and much more).

Other resources

There are many more errors that you’d encounter in Excel other than the #DIV/0 error.

Like the #REF Error, the #NAME error, and the #VALUE error. How to fix them? Learn it all here.