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

 

The results would be as bizarre as follows:

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

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

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)

Here are the results:

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:

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

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

2. Drag and drop the same to the list below.

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.

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

= IFERROR (B2/C2,”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.

4. Hit Enter.

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 🩹

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”

4. Press the “Find All” button.

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

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 👇

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.

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,

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

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

= MOD (10,0)

We have specified the divisor as 0.

3. Press Enter to see the results.

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.