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.

We will use the following dataset to illustrate those methods.

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.

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

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

Steps

• Enter the following formula in cell D5:

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

• Copy the formula to the cells below.

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

• Replace the earlier formula in cell D5 with the following formula:

• Apply the formula to the other cells below them.

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.

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.

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.

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

• How to Calculate Mean Squared Error in Excel
• How to Calculate Root Mean Square Error in Excel
• [Fixed!] NUM Error in Excel
• [Fixed!] VALUE Error in Excel
• Excel Error: The Number in This Cell is Formatted as Text

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