Suppose you’ve copy pasted raw data into a Microsoft Excel worksheet template containing calculated cells. Often you see that some cells containing functions show the #DIV/0! error. This is a way for Excel to inform you that the input data or the reference cells have invalid values.

Sometimes you can just delete the #DIV/0! errors and sometimes you must keep those but have the freedom to suppress the errors. If you don’t know how to resolve or remove the #DIV/0! error in Excel read this simple Excel tutorial until the end.

Here you’ll learn when you often get the #DIV/0! error, which Excel functions often show the #DIV/0! error and how to remove those easily. Don’t forget to try the #DIV/0! error removal methods on your Excel worksheet as you read through this guide.

When Do You Get #DIV/0! in Microsoft Excel?

In Microsoft Excel, the #DIV/0! error occurs when you attempt to divide a number by zero. This error message is Excel’s way of indicating that a formula or function in a cell is attempting to perform a division operation where the denominator is zero, which is mathematically undefined.

For example, if you have a formula like =A1/B1 in a cell, and B1 contains a 0, Excel will return the #DIV/0! error. This error helps you to identify and rectify issues in your spreadsheet calculations, preventing misleading or incorrect results.

Excel Functions Can That Return the #DIV/0! Error

Here are the Excel functions that will generate the #DIV/0! error when there are invalid values in the data input or reference cells or reference cells containing a #DIV/0! error:

• You mostly get a #DIV/0! output when using the division operator “/” directly in a cell, like this: =A1 / B1, and the value of B1 is either empty or 0.
• The QUOTIENT function returns the integer portion of a division. If the divisor is zero, you’ll get a #DIV/0! error.
• VLOOKUP function (with approximate match) might result in a #DIV/0! output when you forget to sort the lookup range in ascending order.
• HLOOKUP function (with approximate match) might result in a #DIV/0! error if you didn’t sort the lookup range in ascending order.
• When the lookup range is not sorted in ascending order, you might encounter the #DIV/0! while using INDEX and MATCH functions (with approximate match).
• If you use the SUMPRODUCT function with arrays that involve division and one of the elements in the divisor array is zero, it can lead to a #DIV/0! output.
• If you use the AVERAGE function and the count of cells with values is zero, it can result in a #DIV/0! error.
• Similar to AVERAGE, if you use the STDEVP function and the count of cells with values is zero, it can result in a #DIV/0! error.
• The DIVIDE formula in Power Pivot in Excel data models can generate the #DIV/0! error if the denominator is 0.
• The MATCH function returns the relative position of a specified value in a sorted array. If the value is not found, it can return an error, potentially leading to the #DIV/0! error.

How to Highlight all #DIV/0! Errors in Your Worksheet

You can use the Find and Replace tool to highlight all the cells that give the #DIV/0! error in your worksheet:

1. Go to your Excel worksheet and press Ctrl + F.
2. In the Find what field type #DIV/0!.
3. Choose the Values option in the Look in the drop-down list.
4. Click the Find All button.
5. Click on the hyperlinks below the Find and Replace dialog to locate all #DIV/0! cells one by one.
6. Cycle through the list to highlight the exact cell and edit the function to resolve the #DIV/0! error.

Why Would You Want to Remove #DIV/0! in Excel?

Here are some reasons why you might want to remove or resolve #DIV/0! errors in Excel:

• Removing #DIV/0! errors enhance the readability of your spreadsheet by eliminating distracting error values.
• Users may misinterpret or be confused by cells displaying errors, so removing them can prevent misunderstanding.
• When performing data analysis or calculations, it’s beneficial to have consistent and accurate results without the interference of error values.
• Aggregating data using functions like SUM or AVERAGE can yield inaccurate results if cells with errors are included.
• A spreadsheet free of visible errors appears more professional and reliable to colleagues, clients, or stakeholders.
• Formulas referencing cells with #DIV/0! errors can propagate the error, affecting subsequent calculations. Removing the errors stops this propagation.
• Graphs and charts can be distorted by cells with errors, so removing them ensures the accuracy of your visual representations.
• If your Excel data is used in other applications or systems, having cells without errors like #DIV/0! ensures proper integration and functionality.

Resolve Formula Errors to Remove #DIV/0! in Excel

Here’s how you can get rid of #DIV/0! in an Excel cell containing a formula:

• If the divisor in the formula is a blank cell or a numerical 0, replace that with an integer or cell reference containing values more or less than 0 to fix the error.
• If the function is referring to another cell in the worksheet or workbook containing #DIV/0! error, replace that cell reference, or resolve the #DIV/0! error of the referred cell.

Delete #DIV/0! Errors in the Worksheet

When you get #DIV/0! errors in some calculated cells in your worksheet, it means that the input dataset has a few blank cells or numerical 0s.

If this is acceptable, you can follow the Find and Replace method mentioned earlier to locate all the #DIV/0! errors in the worksheet.

Then, select one #DIV/0! error in the Find and Replace results to highlight the respective cell and hit the Delete button.

Remove #DIV/0! in Excel Using the IF Function

If you’re using a dated Excel desktop app (older than Excel 2007) or want to add backward compatibility to your Excel workbook, you can use the functions IF and ISERROR together to suppress #DIV/0! errors.

In the above example dataset, I’m calculating the unit price of a few products. Some of the calculated cells containing the function =B2/C2 generate #DIV/0! errors.

That’s because the values in column C are either numerical 0s or blanks. Here’s how I handled the #DIV/0! errors by nesting the =B2/C2 formula inside IF and ISERROR functions:

=IF(ISERROR(B2/C2),"Invalid Input",B2/C2)

I can now copy and paste the above formula across column D to apply it to all other cells in the column by dragging the Fill Handle down the column.

To repurpose the above-mentioned formula in your worksheet, replace formula elements B2/C2 with your own formula.

For example, your formula is =C2/D2. So the formula becomes as shown below:

=IF(ISERROR(C2/D2),"Invalid Input",C2/D2)

Hide #DIV/0! in Excel Using the IFERROR Function

Since Excel 2007, Microsoft introduced a standalone formula to mask errors. It’s known as the IFERROR function.

Using this formula is also straightforward. You just need to put the IFERROR syntax, start the parenthesis, enter your formula, enter the error text within double quotes, and finally close the parenthesis.

Suppose, you’re getting the #DIV/0! error when calculating unit product price from total revenue divided by the number of products sold. Since the number of products sold field is empty or numerical 0, you get this error.

Instead of using the plain division formula, which is =B2/C2, you can replace that with the following formula:

=IFERROR(B2/C2, "Invalid Input")

Avoid #DIV/0! Using PivotTable Options

If you’re seeing the #DIV/0! in the Values field of a PivotTable, follow these steps:

1. Go to the PivotTable Values field that shows the #DIV/0! error and select it.
2. Right-click on the cell and choose PivotTable Options from the context menu.

3. On the PivotTable Options dialog, checkmark the For error values show and enter a text, like Invalid Input.
4. Click OK to apply the changes you’ve just made.

Errors in the PivotTable Values field will be replaced with the text you’ve set up in the PivotTable Options dialog.

Suppress #DIV/0! Error Using Conditional Formatting

Here’s how you can hide #DIV/0! error by setting up a conditional formatting rule:

1. Highlight the column or cell ranges that contain formulas.
2. Click the Conditional Formatting button.
3. Hover your mouse cursor over the Highlight Cells Rules option.
4. An overflow menu will show up.
5. There, click on the More Rules option.

6. On the New Formatting Rule dialog, click on the Format only cells that contain selection.
7. Click on the Cell Value drop-down list and choose Errors.
8. Click the Format button and choose a Fill Color.

9. Go to the Fonts tab and choose the same color as the Font Color.
10. Click OK on the Format Cells dialog.
11. Click OK on the New Formatting Rule dialog as well.

Excel will automatically fill all the cells containing any errors including #DIV/0! with the Fill and Font Colors you’ve chosen.

Best Tips to Avoid #DIV/0! Error in Excel

Here’s how you can avoid #DIV/0! error in Excel:

• Implement data validation rules to prevent users from entering zero as a divisor in the first place.
• Protect your worksheets to prevent accidental changes. This can help maintain the integrity of your data and formulas.
• If you’re working with calculations that involve division, ensure that the divisor is not zero by checking the values in the cells.

• Enable Error Checking in Excel to get notified about errors as you work. To do this, go to the Formulas tab and click on the Error Checking drop-down menu. Click the Error Checking option on the context menu to get to the Error Checking dialog box.
• Implement an IF statement to check if the divisor is zero before attempting the division. For instance, check this formula:

=IF(B1<>0, A1/B1, "Cannot divide by zero")

Conclusions

So, now you know how to remove #DIV/0! error in Excel when using various functions like QUOTIENT, VLOOKUP, HLOOKUP, SUMPRODUCT, AVERAGE, and so on. Use the methods mentioned above to get rid of the annoying “unable to divide by zero error” in Excel.

If the above Excel tutorial helped you, you can write a comment below. If you know a better method that I missed here, don’t forget to mention that in your comment.