#DIV/0! Error in Excel – How to Fix It?

In Excel, the #DIV/0! (division-by-zero) error occurs when a formula attempts to divide a number by zero or by an empty cell.

For instance, the dataset below shows a division-by-zero error in cell D2 because the formula attempts to divide the value 5,000,000 in cell C2 with the zero value in cell B2.

#DIV/0! Error in Excel

Note: If you reference an empty cell in a formula, you will also get the #DIV/0! error because  Excel treats an empty cell as a zero in mathematical operations.

The #DIV/0! error occurs because division by zero is an invalid operation. It is mathematically impossible, and its result is undefined.

Fixing the #DIV/0! error in Excel will make your reports look clean and professional, prevent cascading errors in dependent calculations, etc.

In this tutorial, I will show you four ways to fix the #DIV/0! error in Excel.

Method #1: Fix #DIV/0! Error By Using the IFERROR Function

You can use the IFERROR function, which traps all errors in Excel to catch the #DIV/0! error and replace it with a more meaningful value. 

Below is the syntax of the IFERROR function:

IFERROR(value, value_if_error)

The IFERROR function takes a value or formula as its first argument and an alternative return value as its second argument.

The function returns the second argument if the first argument results in an error. However, if the first argument does not result in an error, the function returns the result of the first argument.

Suppose you receive the following Excel report with #DIV/0! errors in cells D6 and D9 and want to replace the errors with empty strings.

Excel report with #DIV/0! errors

Here’s how to do it:

  1. Replace the formula in cell D2 with the formula below:
=IFERROR(C2/B2,"")
IFERROR formula to remove div errors
  1. Double-click or drag the fill handle in cell D2 to copy the formula down the column.
copy the formula down the column

Cells D6 and D9 are now blank because they contain empty strings.

Note: In this example, we have used an empty string as the second argument of the IFERROR function. However,  the second argument can be any value or text. For example, you could use “No Info” as the second argument to return the text instead of an empty string.

Also read: #REF! Error in Excel

Method #2: Fix #DIV/0! Error By Using IF and ISERROR Functions

Suppose you use a previous version of Excel that does not have the IFERROR function, or you want to share your Excel file with someone using an older version that does not have the IFERROR function.

In that case, you can combine IF and ISERROR functions to trap the #DIV/0! error and return a message or an alternative result.  

I have explained the IF function in Method #3.

Below is the syntax of the ISERROR function:

ISERROR(value)

The function returns TRUE if its argument returns any error and FALSE if it doesn’t return an error.

Suppose you receive the following Excel report with #DIV/0! errors in cells D6 and D9 and want to replace the errors with “No Info.”

Excel report with #DIV/0! errors

Here’s how to do it:

  1. Replace the formula in cell D2 with the formula below:
=IF(ISERROR(C2/B2),"No Info",C2/B2)

Note: In this formula, the ISERROR function’s TRUE or FALSE result becomes the IF function’s first argument, allowing the IF function to return either the second argument or the result of the third argument.

IF and ISERROR function combo to remove div error
  1. Drag or double-click the fill handle in cell D2 to copy the formula down the column.
copy the formula down

The #DIV/0! errors in cells D6 and D9 have been replaced with “No Info.”

Note: In this method, you can replace the ISERROR function with the ISERR function, but be aware that the ISERR function traps all errors except #N/A.

Also read: #VALUE! Error in Excel – How to Fix!

Method #3: Fix #DIV/0! Error By Using the IF Function

You can use the IF function to instruct Excel to skip a division calculation if the denominator is zero and return a message or an alternative result.

Below is the syntax of the IF function:

IF(logical_test, [value_if_true], [value_if_false] 

The IF function has three arguments: the condition, what to do if the condition is true, and what to do if the condition is false.

Suppose you receive the following Excel report with #DIV/0! errors in cells D6 and D9 and want those errors replaced with zeroes.

report with division error in Excel

Here’s how to do it:

  1. Replace the formula in cell D2 with the formula below:
=IF(B2=0,0,C2/B2)
IF formula to get rid of div error in Excel

Note: In this example, we have used a zero as the second argument of the IF function. However,  the second argument can be any value or text. For instance, you could use two quotes (” “) as the second argument to return an empty string instead of a zero.

  1. Double-click or drag the fill handle in cell D2 to copy the formula down the column.
apply the formula to the entire column

Notice that the IF function has replaced the #DIV/0! errors in cells D6 and D9 with zeroes.

  1. Format the values in column D as a percentage.
Format the values in cells

Notice that the report now looks clean and professional without the #DIV/0! errors.

Also read: #NAME? Error in Excel – How to Fix!

Method #4: Fix #DIV/0! Error By Entering Non-Zero Numeric Values In Denominator Cells

Sometimes, you might receive an Excel report with #DIV/0! errors because of empty denominator cells or denominator cells with zeros due to data entry errors.

Where division is involved, manually check if denominator cells are empty or contain zeroes and enter the correct values if they are available.

For instance, the report below has a #DIV/0! error in cell D2 because the data entry person mistakenly entered a zero in cell B2 instead of the correct value of 4,200,000.

Div error because of data entry issue

When you enter the value 4,200,000 in cell B2, the formula replaces the #DIV/0! error in cell D2 with the correct percentage.

Enter value instead of 0

Note: You can implement data validation rules to prevent users from entering zeros as divisors or leaving denominator cells empty. These rules control the type of data or values that users can enter into a cell.

You can use the steps below to set up data validation in Excel:

  1. Select the cells where you want to apply the validation.
  2. Click the Data tab, open the Data Validation drop-down menu on the Data Tools group, and select ‘Data Validation.’
Click on Data validation option
  1. Select the validation criteria in the Data Validation dialog box, configure the settings as needed, and click OK.
Select the validation criteria

How to Find and Highlight All Cells With #DIV/0! Error in a Sheet or Workbook

You can use the Find and Replace feature in Excel to find and select all cells in a worksheet or workbook with the #DIV/0! errors.

You can then delete the errors or highlight the cells, for instance, in yellow. Highlighting the cells in color can help you decide how to handle the errors using the abovementioned methods. 

Suppose you have received the report below and want to find and highlight all cells with the #DIV/0! errors in yellow.

dataset with division errors in Excel

Here’s how to do it:

  1. Press CTRL + F to open the Find and Replace dialog box.
  2. Do the following on the Find and Replace dialog box:
    • Type #DIV/0! on the ‘Find what’ drop-down.
    • Choose the appropriate option on the ‘Within’ drop-down menu. The default option is ‘Sheet,’ but you can also choose ‘Workbook’ to find the cells with the error in the entire workbook. 
    • Choose ‘Values’ on the ‘Look in’ drop-down menu.
    • Click Find All.
Find and Replace to find DIV errors

The above step will find all the cells with the #DIV/0! error and show their addresses in the area at the bottom of the Find and Replace dialog box.

  1. Press CTRL + A to select all the cells that contain the error. The references of the selected cells at the bottom of the Find and Replace dialog box will be highlighted in blue.
CTRL + A to select all the cells

Note: At this point, you can press Delete to remove the #DIV/0! errors from the worksheet or workbook. Stop here if you are not interested in continuing with the steps to highlight the cells in yellow.

  1. Click Close to dismiss the Find and Replace dialog box.
  2. On the Home tab, open the Fill Color drop-down on the Font group and click Yellow on the color palette.
Choose color to highlight cells with DIV error

All the cells with the #DIV/0! errors are highlighted in yellow.

Cells with DIV error are highlighted

I have shown you four ways to fix the #DIV/0! Error in Excel. I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment