How To Solve #Div/0 Errors in Excel (with Examples)

Mathematically, a number cannot be divided by zero. The same holds true in Excel. Excel cannot calculate a value that is divided by zero and denotes it with #Div/0! In addition to zero, division by a blank or empty cell is also displayed as #Div/0!

Simply put, when Excel encounters a value divided by zero, a blank/empty cell, or a value that equals zero, it displays the #Div/0! error. Although we will discuss the #div/0! error with respect to Excel, this article is also applicable to other spreadsheet programs such as Google Sheets, Open Office, etc.

Error Due to Division by Zero.

Enter the following formula in cell B2:

=12/0

This will immediately throw a #Div/0! error, as the denominator in the formula is zero.

Look at the image below, and you will notice a divide-by-zero error in cell E9. Here, the formula is:

=C9/D9

But D9 is 0, so the error.   

Error after division by a blank cell.

Enter 10 in cell B2 and input the following formula in cell D3:

=B2/C2

This will result in a #Div/0! error because cell C2 is blank and treated as zero in Excel calculations.

Error in an Average Formula Due to Non-Numeric Value

Enter the values shown in the image below and input the following formula into cell C15:

=AVERAGE(C3:C14)

However, this will result in a #Div/0! error in cell C15.

Average is

=sum/count

Since the sum and count of numeric values in the given range are both zero (because the values are non-numeric), it results in a 0/0 situation, leading to the div/0! error.

The Averageifs Formula

The statement above is also true for the AVERAGEIF and AVERAGEIFS formulas; however, let us focus on discussing the error at hand with respect to the AVERAGEIFS function. Please refer to the image below:

There are two columns: one for color and the other for quantity. We want to calculate an average of the color values based on the data in cells C3:C9. Enter the formula in cell F3.

=AVERAGEIFS(C5:C11,B5:B11,E3)

Copy it to cells F4 and F5. You will immediately notice a #DIV/0! error in cell F5. But why?

For cell F3, the count of red is 2 (B3 and B5), and the sum is 12 + 8 = 20 (C3 and C5). The average will be 20/2 = 10.

For cell F5, the count of black is 0 (since black is not present in the reference range), and the sum is also zero; therefore, it results in a 0/0 calculation, causing a #DIV/0! error.

Error in Another Formula Where Division Is Not Involved.

Enter the following formula in cell E15:

=SUM(E3:E14)

But why does it show a #div/0! error? This is a simple sum function, and there is no division involved. The #div/0! error in cell E3 is causing the error in cell E15.

MOD is also an Excel formula that can directly cause a #div/0! error.

Reasons for #Div/0! Errors.

So, Excel will display a division by zero error.

1. If there is a division by zero, a blank, or an empty cell is involved.
2. If the information in the formula range is not valid, for example, trying to get the average of a range of non-numeric cells.
3. If the formula range already contains a #div/0! error.

Methods for Handling #Div/0! Errors.

As the #Div/0! error has its roots in mathematics and cannot be avoided, first ensure that the error is not caused by a mistake or human error. To do so:

1. Ensure there is no division by zero, blank, or empty cell in the process.
2. Check if the information in the formula range is valid. For example, ensure that you are not attempting to calculate an average of non-numeric cells.
3. Make sure there is no cell already displaying a #DIV/0! error in the formula range.
4. Use the Excel formulas to mask, trap, or fix #div/0! errors (discussed later). 

If error reporting is enabled, click on the yellow triangle icon and select ‘Show Calculation Steps.’ You can use that to isolate the problem.

How to Find #DIV/0! Error Cells.

One can manually overlook a division by zero error when going through a large data set, and using Excel’s capabilities to find all the cells with #div/0! errors will be of great help. This can also help us decide how to manage these errors.

1. Press Control + F keys to open the Find and Replace box.
2. Expand the options, and in the “Find What” box, enter:

   #DIV/0!

3. Select ‘Sheet‘ or ‘Workbook‘ (in the ‘Within’ dropdown) and set the ‘Look in‘ dropdown to ‘Values‘.
4. Click on ‘Find All,’ and all the cells with the #Div/0! errors will be displayed. You can examine each cell to determine whether the error is a result of a mistake or if another action can be taken, such as deleting the cell if it is not required.

   

Use Excel Formulas to Overcome ‘Divided by Zero’ Errors.

There will always be cases where division by zero is inevitable, but you do not want to show a calculation error on your worksheet to your senior, colleague, client, etc. For this purpose, you can trap or mask the #DIV/0! error by using the IF or IFERROR formulas.

Use the IFERROR formula to trap #DIV/0! errors.

IFERROR is an Excel formula used to mask all errors on an Excel sheet. It also functions to trap or mask #Div/0! errors. The syntax of the IFERROR formula is as follows:

=IFERROR(value, value_if_error)

Here, ‘value’ represents the formula displaying an Excel error (in this case, #DIV/0!), and ‘value_if_error’ is a value that you specify to appear in place of the error.

Let’s clarify with a simple example. Enter 10 in cell B2 and input the following formula in cell D2:

=B2/C2

This will cause a #DIV/0! error in cell D2. Now, enter the following formula in cell D2:

=IFERROR(B2/C2,"")

You will see that cell D2 is now blank because we have asked the IFERROR formula to display a blank space (denoted by “”) instead of the #DIV/0! error.

Add a custom message to the IFERROR formula.

If you do not want to display a blank cell, you can add a custom message to your formula, which can be helpful in the future when you or someone else revisits the formula. To do so, let’s continue with our simple example:

1. Enter the following formula in the D2 cell:

   =IFERROR(B2/C2,"C2 is empty, enter a value")

   

2. You will notice that cell D2 now clearly indicates that C2 is empty. Please enter a value.

If you want to apply the IFERROR formula to a column or row, simply copy and paste it.

Limitations of the IFERROR Formula.

Here are some limitations of the IFERROR formula:

1. The IFERROR formula is compatible with Excel 2007 and above. For versions below that, such as Excel 2003 or earlier, this formula will not work. Instead, you can use the IF and ISERROR functions, which will be discussed later.
2. IFERROR will mask all error values, including #DIV/0!, N/A, #VALUE!, #REF!, #NUM, #NAME, etc., not just #div/0! errors. If your formula returns an error other than #div/0!, it will be treated in the same way. This can lead to incorrect calculations and decisions.

Use the IF formula to solve #Div/0!

#Div/0! can be solved (not just masked or trapped) by using the logic of the IF formula, but the solution will differ from case to case.

The general syntax of the IF formula is as follows:

=IF(Logical_test, [value_if_true], [value_if_false])

A logical test is a condition that you want to test. If it is true, the first value is returned; otherwise, the second value is returned. To better understand, let us continue with our simple example:

1. Enter the following formula in cell D2:

   =IF(C2,B2/C2,"")

   

2. You will see a blank cell in cell D2. In this case, the formula states that if the C2 cell contains a non-zero value, proceed with the division of B2 by C2; otherwise, return an empty value.
3. Let’s change the formula to display a custom message.

   =IF(C2,B2/C2,"Input Value in C2")

   

4. You will see ‘Input Value in C2’ in the D2 cell. In this case, the formula states that if the C2 cell has a non-zero value, proceed with the division of B2 by C2; otherwise, display the text ‘Input Value in C2’.
5. Enter the following formula in cell D2:

   =IF(C2<>0,B2/C2,"Input non-Zero Value in C2")

   

6. Now, whenever the value of C2 is not equal to zero (denoted by <>), the formula will calculate; otherwise, it will display ‘Input a non-zero value in C2’.
7. You can combine multiple criteria in a single IF formula by using OR. For example, if we do not want to calculate the formula if B2 or C2 is empty, we can use the following:

   =IF(OR(B=2"",C2=""),"",B2/C2)

   

The same logic can be applied to other, more complex scenarios as well. There is no limit to the logic that can be used with IF and OR to overcome any #div/0! error, but that logic is dependent on your particular use case.

Remove the #DIV/0! error using ISERROR and IF.

If you are using an older version of Excel (2003 or earlier) or plan to share your workbook with a user of such a version, then IFERROR will not function, and you will need to use IF and ISERROR to mask or trap #div/0! errors.

ISERROR is a Boolean function that checks for errors. If an error is present, it displays ‘True’; if not, it shows ‘False.’ Let’s continue with our simple example.

1. Enter the following formula in cell D2:

   =ISERROR(B2/C2)

   

2. You will see ‘True,’ indicating that there is an error. Again, enter the following formula in cell D2:

   =IF(ISERROR(B2/C2),"Not Available",B2/C2)

   

3. This means that if there is an error, display “Not Available” (you may use any other string if you prefer); otherwise, calculate B2/C2.

A point to remember is that ISERROR will also mask all other errors, not just #div/0! errors, and must be used with caution to avoid incorrect calculations or decisions.

Remove the #DIV/0! error in the PivotTable.

#DIV/0! errors in PivotTables can be handled differently. Moreover, a PivotTable will not display the desired text in place of a #DIV/0! error if the text is the name of a row or column. Look at the image below, and you will notice #DIV/0! errors in cells D6, D7, D9, and F5.

To change this:

1. Click anywhere in the PivotTable, and head to the PivotTable Analyze tab in the Ribbon.
2. Expand the PivotTable option and select Options.

   

3. Select ‘Layout & Format‘ and in the ‘Format’ section, enable ‘For Error Values Show.’
4. In the text box, enter “Unavailable” (or any other text that you want to use), and the #Div/0! errors will be replaced by “Unavailable.” Remember this point.

   

   If your PivotTable is showing some other errors, they will also be masked; therefore, use the masking with caution.

Hopefully, these lines have clarified your understanding of #div/0! errors. If you have any questions, feel free to post them in the comments section.