How to Remove #DIV/0 Error in Excel

If you're an Excel user, you've probably encountered the dreaded #DIV/0 error at some point. This error occurs when you attempt to divide by zero.

In the example below cell A6 is blank, therefore Excel evaluates A6 as zero and the formula returns the #DIV/0! Error.

Excel #DIV/0 errors can be frustrating to deal with. Not only are they a bit distracting, they can unfortunately have an impact on other calculations should the cell they are in be referenced in another formula.

Fortunately, there are a few simple steps you can take to remove or hide this error.

Here are some ways you can remove a #DIV/0 error in Excel:

1. Use the IFERROR function to remove an Excel #DIV/0 Error
2. Use the IF function to remove an Excel #DIV/0 Error
3. Remove Excel #DIV/0 Error in Average formulas
4. Use the IFERROR function to hide AVERAGE #DIV/0 Errors
5. Use the IFERROR function to remove AVERAGEIF #DIV/0 errors
6. Hide a #DIV/0 error using Conditional Formatting

1. Use the IFERROR function to remove an Excel #DIV/0 error

One of the easiest ways to remove the #DIV/0 error is to use the IFERROR function. This function allows you to specify what you want to happen if an error occurs in a formula.

In the example below, cell E14 holds the formula =E13/E12 which is returning a #DIV/0 Error because cell E12 is blank.

To remove the #DIV/0 error, we can use the formula =IFERROR(E13/E12,""). This formula will return a blank cell if a #DIV/0 error occurs.

2. Use the IF function to remove an Excel #DIV/0 error

Another way to remove the #DIV/0 error is to use the IF function. This function checks whether the dividend cell is blank and returns a blank cell if it is. For example, the formula =IF(E12="","",E13/E12) checks to see if cell E12 is blank. If E12 is blank the IF function will leave cell E14 as blank, but if the cell isn’t blank Excel will divide cell E13 by cell E12 and return the result into E14.

Additionally you can use the IF function with a nested OR function to check if E12 is blank or holds a zero value. If either is True, Excel will leave E14 as blank.

3. Remove Excel #DIV/0 Error in Average formulas

When using AVERAGE functions in Excel, it's important to be aware of the #DIV/0 error that can occur if one or more of the cells in the range being averaged contains a zero, is blank or the range contains text only.

This is because the general formula for calculating averages is =sumtotal/count, and count can sometimes be zero. In the example below the COUNT function would return 0, resulting in Excel returning a #DIV0 error.

Here are some ways to remove #DIV/0 errors when using average functions in Excel:

4. Use the IFERROR function to remove AVERAGE #DIV/0 errors

Use the IFERROR function to catch any #DIV/0 errors that may occur when using the AVERAGE function.

For example, you can use the formula =IFERROR(AVERAGE(E19:E22),""). This formula will calculate the average of the cells in the range E19:E22, and if a #DIV/0 error occurs, it will return a blank cell instead of the error message.

5. Use the IFERROR function to remove AVERAGEIF #DIV/0 errors

When using the AVERAGEIF function in Excel, you may encounter the #DIV/0 error if the function is unable to find a match for the criteria within the range being evaluated. 

In the example below, the formula calculates the average of cells A28 through to A38, but only for the numbers that meet the condition held in cell D28. The actual numbers that the formula uses to calculate the average are in cells B28 through B38.

The #DIV/0 Error has occurred because the value held in cell D28 has been entered incorrectly as ‘Haselnuts’ not ‘Hazelnuts’. Therefore Excel is unable to find a match for the criteria resulting in the #DIV/0 error.

Once D28 is entered correctly the #DIV/0 error will disappear.

For times when the criteria is correct, but the #DIV/0 error is showing, wrap an IFERROR function around the AVERAGEIF function to hide the error.

6. Hide a #DIV/0 error using Conditional Formatting

Conditional formatting is a powerful tool in Excel that allows you to format cells based on their values or formulas. One useful application of conditional formatting is to hide the #DIV/0 error that occurs when you attempt to divide a number by zero. Here's how to do it:

1. Select the range of cells that contains the #DIV/0 error.

2. Click on the Home tab and then select Conditional Formatting.

3. Choose "New Rule" from the dropdown menu.

4. In the New Formatting Rule dialog box.

5. Select "Format only cells that contain" from the first dropdown menu. In the second dropdown menu, choose "Errors ".

6. Click the Format button.

7. Apply a format that will hide the error, for example change the font colour to white.

8. Click "OK" to apply the formatting rule.

After applying this rule, any cells in the selected range that contain the #DIV/0 error will appear blank. This can make your spreadsheet look cleaner and more professional, and it can also help to prevent confusion or mistakes that may occur if the error remains visible.

It's important to note that this method does not actually remove the error from the cell - it simply hides it from view. Therefore the error could still have an impact on any formulas referencing it.

I’d recommend only using this method for when you want to hide a #DIV/0 error, so it doesn't catch your attention and distract you.

Conclusion

By implementing these methods, you can effectively eliminate the #DIV/0 error in Excel and ensure that your formulas are accurate and reliable, regardless of the function being used. These simple steps can help you to keep your spreadsheets error-free and ensure that your data analysis is both efficient and effective. So, whether you're using the AVERAGE function or any other formula in Excel that involves division, be sure to follow these methods to prevent errors and ensure that your data is always accurate.