How to Remove #DIV/0! Error in Excel (5 Methods)

Excel shows the Divided by Zero(#DIV/0!) error when the denominator of any formula is zero or empty. This article shows how to remove the div by 0 or #DIV/0! error in Excel in 5 different ways. The following picture highlights the ultimate results obtained from applying those methods.

remove #div/0! error in excel


Remove #DIV/0! Error in Excel: 5 Easy Ways

I am going to show you the 5 methods on how to remove #DIV/0! error in Excel. We will use the following dataset to illustrate those methods. So, let’s begin!

dataset to remove #div/0! error in excel


1. Remove #DIV/0! Error With IF Function

In the dataset, the formula in cell D5 gives the quotient of cells B5 & C5 as shown in the following picture. When the formula is copied to the later cells, some cells give the #DIV/0! error. We can remove the #DIV/0! error using the IF function. To do that, please follow the steps below.

Steps

1. First of all, replace the formula in cell D5 by entering the following formula in that cell.

=IF(C5,B5/C5,"Unavailable")

application of if function to remove #div/0! error in excel2. Then, copy the formula to the other cells using the fill handle tool.

3. Now, we can see that the #DIV/0! error is replaced by the text used in the formula.

#div/0! error removed using if function


2. Fix #DIV/0! Error Using IFERROR Function

An alternative way to remove the #DIV/0! Error is by using the IFERROR function. Please follow the steps below to apply this method.

Steps

1. At first, enter the following formula in cell D5:

=IFERROR(B5/C5,"Unavailable")

2. After that, copy the formula to the cells below.

3. Now, we see the #DIV/0 errors are removed as in the earlier method.


3. Hide #DIV/0! Error With ISERROR Function

Another way to remove the #DIV/0! Error is to use the ISERROR function. The steps are as follows.

Steps

1. First, apply the following formula in cell D5:

=(ISERROR(B5/C5)

2. This returns FALSE as the result.

3. Then, copy the formula to the cells below.

application of iserror function to remove #div/0! error in excel

4. Now, we see the errors are removed. But other quotient values are also gone.

5. To fix this problem, replace the earlier formula in cell D5 with the following formula.

=IF(ISERROR(B5/C5),"",B5/C5)

6. After that, apply the formula to the other cells below them.

application of if & iserror functions to remove #div/0! error in excel

7. Finally, the #DIV/0! errors are no longer there.


4. Avoid #DIV/0! Error by Changing Cell Value

The #DIV/0! error can be removed by changing the cell values that causing the errors. We can follow the steps below with our dataset to verify this method.

Steps

1. One way to avoid the #DIV/0! error is to change the 0 or empty values for the divisors to non-zero values as sown below.

2. Another way to avoid the #DIV/0! error is by replacing the 0 or empty values of divisors with #N/A. This replaces the #DIV/0! error with #N/A as shown in the following picture.

apply #N/A to remove #div/0! error

Read More: How to Remove Number Error in Excel


5. Remove #DIV/0! Error in PivotTable

Excel has options to remove the #DIV/0! errors in PivotTable. I have created a Pivot Table to show the sum of quotients from our dataset. But, it shows the #DIV/0! error. Let’s fix this by following the steps below.

#div/0! error in excel PivotTable

Steps

1. Firstly, click anywhere in the Pivot Table.

2. Then, from the PivotTable Analyze tab, go to PivotTable and then Options.

3. This will open a new dialog box.

4. After that, from the Layout & Format tab in the dialog box, check the checkbox for “For error values show:”.

5. Next, enter the text you want to show for errors in the text box. I have typed ‘Unavailable’ in this case.

6. Now, hit the OK button.

7. Finally, we see the #DIV/0! error is replaced by the text.

#div/0! error removed from PivotTable


Things to Remember

  • Always make sure that the values for the denominator in your formula are not zero or empty to avoid the #DIV/0! error in Excel.
  • PivotTable doesn’t show the desired text in place of the #DIV/0! error if it is considered as the name of a row or a column.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now, you know 5 different ways to remove the #DIV/0! error in Excel. You can use the comment section below for further queries. If you have other suggestions or shortcuts to remove the #DIV/0! error in Excel, please share them with us.


Related Articles


<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo