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, but they can also impact other calculations if the cell they are in is referenced in another formula.
Fortunately, you can take a few simple steps to remove or hide this error.
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 returns 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 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 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 occurred because the value held in cell D28 was entered incorrectly as ‘Haselnuts’ instead of ‘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.
When the criteria are 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 help prevent confusion or mistakes that may occur if the error remains visible.
It’s important to note that this method does not remove the error from the cell but hides it from view. Therefore, the error could still impact any formulas referencing it.
I’d recommend only using this method 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 eliminate the #DIV/0 error in Excel and ensure that your formulas are accurate and reliable, regardless of the function used. These simple steps can help you keep your spreadsheets error-free and ensure that your data analysis is efficient and effective. So, whether using the AVERAGE function or any other formula in Excel that involves division, follow these methods to prevent errors and ensure that your data is always accurate.
Was this post helpful? Let me know in the comments below.
Certified Microsoft Office Specialist
Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.
Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.
And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!”
Happy Excel-ling!!