In this short tutorial, I will show you how to quickly remove the division error in Excel (using some easy formulas). I will also show you how you can find all the cells in a worksheet or workbook that contain the DIV errors.
What is a #DIV/0! Error in Excel?
In Excel, you will get a DIV error when you have a formula where there is a division and the divisor is 0. For example, if you enter =12/0 in a cell in Excel, it will give you the division error (#DIV/0!) Of course, you wouldn’t use a formula like this in real life, but in most cases, the division error is a result of larger formulas where you use cell references in the formula, and some of the references could have the value 0 or could be blank. And sometimes, you may get this error as part of the data you download from a database or from the web.
Remove #DIV/0! Error Using IFERROR
If you’re working with formulas and want to get rid of the #DIV/0! error (which often occurs as the result of the formula), you can use the IFERROR technique. Below is the syntax of the IFERROR formula: here:
value – this is the formula that can give you the division errorvalue_if_error – this is the value specify you should get when you have any error
Let me explain this using an example. Suppose you have a dataset as shown below where I have the division formula in column C. As you can see, I get div errors in all the cases where the divisor is 0 or blank in column B. To remove the div error, I can use the below formula: The above formula returns the resulting value after the division if it’s not an error, and in case it gives an error, it replaces the div error with a blank. Similarly, if you want to replace the div error with something more meaningful than a blank, you can specify that as the second argument. Below is the formula that will give you the text “Not Available” instead of the division error: A few important things to know about the IFERROR formula:
IFERROR formula was introduced in Excel 2007 onwards. So if you’re using Excel 2003 or prior versions, you won’t get to use this formula (use the method I cover next in that case)IFERROR works for all the error values (including N/A, #DIV/0!, #VALUE!, #REF!, #NAME, #NUM, etc.). So in case your formula returns any other error, it would also be treated the same way
Remove #DIV/0! Error Using ISERROR and IF
While using the IFERROR function is the preferred way to handle the #DIV/0! error in Excel, in case you’re using an old version of Excel that does not have the IFERROR function (or you need backward compatibility in case you have to share the file with someone who is still using an older version of Excel), you can use the IF + ISERROR method. This one works just like the IFERROR function but has a few more arguments. Again, suppose I have the below dataset and I want to remove the #DIV/0! error. I can use the below formula to do this: The above formula first checks whether our formula gives an error or not (using the ISERROR formula), and in case it does, it gives the second argument as the result, else it gives the third argument as the result.
Find all Cells with #DIV/0! Error in Excel
In this section, I will show you how to quickly find and select all the cells that contain the #DIV/0! error. This could be useful when you get a file from a colleague or download it from the web and it has the div error that you want to remove. While this method won’t remove the DIV error, it can be useful in finding and highlighting these errors so you can decide how to handle these (such as delete it or check the data and rectify it). Below are the steps to find and remove all DIV errors in the worksheet/workbook: Once selected, you can remove the error from all these cells by hitting the delete key, or highlight these by giving the cells a background color. So these are some of the ways you can use to remove the DIV errors (division error) in Excel. If it’s a result of your own formulas, you can use the IFERROR function, and if you have these in a workbook that you have inherited or downloaded, you can use the Find and Replace dialog box technique I covered. I hope you found this tutorial helpful! Other Excel tutorials you may also like:
NAME Error in Excel (#NAME?)- What Causes it and How to Fix it!#REF! Error in Excel – How to Fix the Reference Error!Use IFERROR with VLOOKUP to Get Rid of #N/A ErrorsIdentify Errors Using Excel Formula DebuggingHow to Fix #VALUE Error in Excel?
