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.
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.
Here’s how to do it:
- Replace the formula in cell D2 with the formula below:
=IFERROR(C2/B2,"")
- Double-click or drag the fill handle in cell D2 to 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.”
Here’s how to do it:
- 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.
- Drag or double-click the fill handle in cell D2 to copy the formula down the column.
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.
Here’s how to do it:
- Replace the formula in cell D2 with the formula below:
=IF(B2=0,0,C2/B2)
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.
- Double-click or drag the fill handle in cell D2 to copy the formula down the column.
Notice that the IF function has replaced the #DIV/0! errors in cells D6 and D9 with zeroes.
- Format the values in column D as a percentage.
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.
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.
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:
- Select the cells where you want to apply the validation.
- Click the Data tab, open the Data Validation drop-down menu on the Data Tools group, and select ‘Data Validation.’
- Select the validation criteria in the Data Validation dialog box, configure the settings as needed, and click OK.
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.
Here’s how to do it:
- Press CTRL + F to open the Find and Replace dialog box.
- 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.
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.
- 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.
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.
- Click Close to dismiss the Find and Replace dialog box.
- On the Home tab, open the Fill Color drop-down on the Font group and click Yellow on the color palette.
All the cells with the #DIV/0! errors are highlighted in yellow.
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: