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:
- Your formulas do not refer to any cells containing the #DIV/0 error.
- You don’t perform the division operation using any empty cells.
- 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.
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 🚕
Can we readily find the maintenance cost per kilometer for each car?
For sure, that’s simple.
- Write a simple division operation as below:
= B2 / C2
- 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.
- 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.
The IFERROR function works the same for all errors in Excel. Be it #VALUE, #REF, #NAME, #CALC, or whichever error 💪
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 ✨