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:

- Go to your Excel worksheet and press
`Ctrl`+`F`. - In the
**Find what**field type**#DIV/0!**. - Choose the
**Values**option in the**Look in**the drop-down list. - Click the
**Find All**button. - Click on the hyperlinks below the
**Find and Replace**dialog to locate all**#DIV/0!**cells one by one. - 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:

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

- On the
**PivotTable Options**dialog, checkmark the**For error values show**and enter a text, like**Invalid Input**. - 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:

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

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

- Go to the
**Fonts**tab and choose the same color as the**Font Color**. - Click
**OK**on the**Format Cells**dialog. - 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.

I use the ISBLANK with IF to return a blank cell in order to avoid an error statement (I use this if the value is Blank and never 0). In my world, I prefer to see blank cells. I also use IF and Less Than “1” to return a blank cell if true and follow the formula if False (I use this if the value is always 0 and never blank). I use named ranges a lot instead of cell references.

1.) example… =IF(ISBLANK($C12),””,SUMIF(Invoice_PO_Nbr,’PO”s’!$C12,Invoice_Amt))