Excel #DIV/0 Error: What It Means and How to Fix it

The Excel #DIV/0 error occurs when a value is divided by 0. Because dividing a number by zero returns an infinity value, Excel shows you an error. Excel provides you with a number of helpful ways to resolve this error. In this tutorial, you’ll learn how to resolve the Excel #DIV/0 error. Similarly, trying to use a calculator to divide a value by zero.

By the end of this tutorial, you’ll have learned:

• What the #DIV/0 error means and why it happens
• How to resolve the #DIV/0 error in Excel using the IFERROR() and ISERROR() functions
• How to find and highlight all cells containing the #DIV/0! error in Excel

Understanding the Excel #DIV/0 Error

For example, if we tried to write the following formula, =1/0, we would raise a #DIV/0 error. This is true for any value.

It’s unlikely, of course, that you wouldn’t write a formula like this directly. However, you may have set up a formula that you copy down an entire column. If that column containing your denominator has a zero, then Excel will raise that error.

Let’s take a look at an example. Imagine that we have a column that contains the area of a rectangle and another that contains the length of the rectangle. We can calculate the height of the rectangle by diving the area by the length.

If we have a user entering the lengths of different rectangles, then it’s likely that we may encounter user data entry. Let’s see what this might look like:

If we write a formula that calculates the height of the rectangle, we can copy that all the way down the column. This allows us to calculate the height of each rectangle. You can do this by dragging the fill handle down to the last record.

When we let go of the mouse, Excel calculates the heights of our rectangles. Because one of the lengths was entered as 0, Excel raises the #DIV/0 error.

In the following sections, you’ll learn how to use Excel to resolve the error.

Handling the Excel #DIV/0 Error with IFERROR()

Let’s take a look at understanding what the IFERROR() function looks like:

=IFERROR(value, value_if_error)

The IFERROR() function is used to trap and handle errors in a formula. This means that if an error is identified, then the function returns a fallback value. What that fallback value is, is up to you! The function takes two parameters:

1. value=, the formula that we are evaluating
2. value_if_error=, the value to return if the value returns an error

Let’s see how we can replicate our earlier example and wrap our function in the IFERROR() function.

Rather than writing =A2/B2 into cell C2, we can wrap the formula in the IFERROR() function, as shown below:

=IFERROR(A2/B2, "Dividing by zero.")

When we use this formula and copy it all the way down, the cell that previously raised an error now returns the string "Dividing by zero."

Similarly, we can also use the ISERROR() function, in combination with the IF() function to accomplish a similar result.

Handling the Excel #DIV/0 Error with ISERROR()

The Excel ISERROR() function turns a boolean (True or False) value depending on whether the function passed into it returns an error or not. We can place this function into the IF() function to check whether or not the function returns an error. If it does, we can return a message indicating this.

At this point, I should note that this process may seem like a bit more work than simply using the IFERROR() function. You’re right in thinking that. So, why are we looking at this combination of functions?

The Excel IFERROR() function was only introduced in Excel 2007. If you’re working with older versions of Excel, you’ll need to use this method.

You can use the IF() and ISERROR() functions to trap and handle the #DIV/0! error in Excel by modifying your formula like this:

=IF(ISERROR(A2/B2), "Dividing by zero.", A2/B2)

We can then copy the formula down the entire column of values and notice that the errors are able to be resolved.

Finding All Cells That Contain the Excel #DIV/0! Error

In this section, you’ll learn how to find all of the cells in a workbook that contain the Excel #DIV/0! error. This can be helpful in highlighting those cells in order to trace back where the errors are coming from.

In order to do this, you can:

1. Open the Find and Replace window, using the CTRL + F keyboard shortcut (CMD + F, if you’re using macOS)

2. Enter #DIV/0! into the Find what: menu.

3. Change the Within: option to include whether you want to search within the sheet or the whole workbook.

4. Modify the Look in: option to search for Values.

5. Select Find All to find all of the values. From there, you can select all of the errors found by clicking CTRL + A (or CMD + A on macOS) on the section below. This will select all the errors.

6. You can highlight all of the errors by giving them a background color.

Doing this allows you to easily identify where the errors are occurring. From here, you can easily trace back the issues that are causing the errors.

Conclusion

In this tutorial, you learned about the Excel #DIV/0! error. The error occurs when you a formula attempts to divide a value by zero. Because dividing a value by zero returns infinity, Excel raises an error.

You first learned how to trap and handle the #DIV/0! error using the IFERROR() function, which was introduced with Excel 2007. Then, you learned how to use the IF() function with the ISERROR() function to trap and handle the error. Finally, you learned how to highlight all cells containing the error using the Find and Replace menu in Excel.

Additional Resources

To learn more about related topics, check out the tutorials below:

• Excel #REF! Error: What It Means and How to Fix It
• Excel #NUM! Error: What It Means and How to Fix it
• Excel #DIV/0 Error: What It Means and How to Fix it
• How to Ignore #N/A Error in Excel SUMIF() (#DIV/0 etc.)
• Excel IFERROR(): Official Documentation