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. Let’s replace this with custom text, with the following picture highlighting how the dataset will look.

remove #div/0! error in excel


We will use the following dataset to illustrate those methods.

dataset to remove #div/0! error in excel


Method 1 – Remove #DIV/0! Error With IF Function

In the dataset, the formula in cell D5 is simply B5/C5 as shown in the following picture. When the formula is copied to the later cells, some cells give the #DIV/0! error.

Steps

  • 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 excel

  • Copy the formula to the other cells using the fill handle tool.

  • We can see that the #DIV/0! error is replaced by the text used in the formula.

#div/0! error removed using if function


Method 2 – Fix #DIV/0! Error Using IFERROR Function

Steps

  • Enter the following formula in cell D5:
=IFERROR(B5/C5,"Unavailable")
  • Copy the formula to the cells below.

  • We see the #DIV/0 errors are removed as in the earlier method.


Method 3 – Hide #DIV/0! Error With ISERROR Function

Steps

  • Apply the following formula in cell D5:
=(ISERROR(B5/C5)
  • Copy the formula to the cells below.

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

We see the errors are removed. But other quotient values are also gone.

  • Replace the earlier formula in cell D5 with the following formula:
=IF(ISERROR(B5/C5),"",B5/C5)
  • Apply the formula to the other cells below them.

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

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


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

Steps

  • Change the 0 or empty values for the divisors to non-zero values as shown below.

  • Replacing the 0 or empty values of divisors with #N/A replaces the #DIV/0! error with #N/A as shown in the following picture.

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


Method 5 – Remove #DIV/0! Error in PivotTable

We have created a Pivot Table to show the sum of quotients from our dataset but it shows the #DIV/0! error. Let’s fix that.

#div/0! error in excel PivotTable

Steps

  • Click anywhere in the Pivot Table.
  • From the PivotTable Analyze tab, go to PivotTable and then Options. This will open a new dialog box.

  • From the Layout & Format tab in the dialog box, check the checkbox for “For error values show:”.
  • Enter the text you want to show for errors in the text box. We have typed ‘Unavailable’ in this case.
  • Hit the OK button.

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.


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