While excel is very efficient and handy software to data analysts, finance professionals, accountants, marketers, and students and professionals from various backgrounds, it can be very strenuous at times as well.
There are numerous memes on the internet about Excel’s crash reports, large spreadsheets, loading problems, errors, and many more.
However, people still use Excel because it offers a lot more than its provision of problems.
Errors might occur in excel for human errors but there are always ways to fix them. One such error is divided by 0 error (#DIV/0!).
So, today let’s see how to remove div 0 (#DIV/0!) in excel.
What is divided by 0 error (#DIV/0!)?
Due to its long name, people usually call it a div 0 error in excel. However, the true name of the error is divided by 0 error. Whenever this error occurs, excel will show you #DIV/0!.
This is a very common error in excel which occurs when excel doesn’t find values to divide with or when the divisor is 0.
In the picture you can see that, 2 has been divided by 0 in excel. So, let’s see what happens if we press enter.
As you can see the result is showing an #DIV/0! An error that has occurred due to 2 has been divided by 0.
There are ways to get rid of a div 0 error or divided by 0 error in excel.
So, let’s see how to remove div 0 (#DIV/0!) in excel.
How to Remove Div 0 (#DIV/0!) in Excel?
First, let’s see an example where div 0 error might occur in excel.
In the picture, you can see that to calculate the percentage commissions, total sales have been divided by commission received. So, if somebody receives 0 commission then total sales would be divided by 0 and excel will show #DIV/0! which is divided by 0 error.
In the third row, Alex received 0 commission hence excel is showing the div 0 error.
To remove the div 0 error without any use of formulas or functions simply put values other than 0 in the divisor cells. You can also change the cell reference in the formula which has a value other than 0.
Basically, if you tell excel to divide, just see that the cells contain values other o.
But in many cases, we need to put zeroes or bank cells intentionally in that case there are ways in excel to get rid of the div 0 error.
To get rid of the div o error in excel, there are two ways. Let’s look at them one by one.
Method#1 Using the ISERROR function to remove the div 0 (#DIV/0!) in Excel
In a previous blog, we showed how to use the IF formula in excel.
In that blog, we mentioned that any other function that returns a binary result (true or false) like ISEVEN, ISERROR, ISBLANK, ISODD, etc. can be used with excel. This is called nesting.
So, ISERROR nested with IF function can get rid of the div 0 error (#DIV/0!) in Excel.
Type the following formula to get rid of the div 0 error (#DIV/0!) in Excel in this case:
=IF(ISERROR(B2/C2),”0″,B2/C2)
Pull the fill handle down to copy the formula for the rest of the cells. And you will see that the div 0 error has been removed.
The ISERROR function in excel tells you whether a formula will return the div 0 error or not.
As you can see in the picture the ISERROR formula has been typed to determine whether the division returns an error or not.
As it should be, the third row for Carrey shows TRUE because the divisor is 0 and it would show a div 0 error.
Now, ISERROR nested with the IF formula does its magic. The first argument for if does the logical test which in this case was determining whether there is a div 0 error or not.
The second argument evaluates whether the logical argument is true or not, if it is true then we have commanded excel to give an output of 0 and for the next argument if the logical test is false then we commanded excel to give an output by dividing the cells.
And excel has done just that, for the third row, it found a divided by 0 error, so the result is 0 but for other salesmen, everybody has a divisor so, the results are the quotients.
Method#2 Using the IFERROR function to remove the div 0 (#DIV/0!) in Excel
There is another easy and faster way to get rid of the div 0 (#DIV/0!) in Excel.
Type the following formula to get rid of the div 0 error (#DIV/0!) in Excel in this case:
=IFERROR(B2/C2,0)
Pull the fill handle down to copy the formula for the rest of the cells. And you will see that the div 0 error has been removed.
This is a simpler method to remove the div 0 error.
For the first method, we need to nest the IF and ISERROR functions, however, Excel’s already got a built-in nested version of the functions which is IFERROR.
The syntax for the IFERROR function is:
=IFERROR (value, value_if_error)
So, for the first argument, the value is input which in our case was the division (total sales/commission). The formula tells excel to evaluate whether the value will return a div 0 error or not.
For the second argument, if excel finds an error we can command it to display results as per our convenience.
In this case, we told excel to display 0 and for the third row, it showed a 0 because it found a div 0 error.
So, there you go in these ways you can remove div 0 (#DIV/0!) in excel.
Conclusion
There are several errors other than the div 0 error which might occur in excel and can hamper your flow of activities.
However, there are ways to solve each one of them.
You just need to be patient and learn from the mistakes so that they won’t happen again.
As now you know how to remove div 0 (#DIV/0!) in excel, you have one less thing to worry about and be more efficient.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.