How to Fix the #DIV/0! Error in Excel | Your Comprehensive Guide for 2024

Examine a genuine scenario from ExcelForum: "How to resolve #DIV/0 error in Pivot Table Excel 2007? Hi there! I'm facing an issue. I'm attempting to compute an average in a Pivot Table, but instead of displaying the values, it shows the #DIV/0 error... Can anyone assist? Many thanks!"

In this article, we will guide you on how to instantly fix the #DIV/0 error. First, familiarize yourself with the error's basics, and then employ the matching solution. Additionally, we have provided methods to handle other common Excel errors at the bottom of the page.

Quick Navigation:

What is #DIV/0 Error in Excel and How Does It Occur
How to Fix the #DIV/0 Error
How to Prevent the #DIV/0 Error in Excel
Frequently Asked Questions About #DIV/0 Error
Bonus Tip: How to Remove Other Excel Errors (#N/A, #REF!, #NAME?)
Conclusion

What is the #DIV/0 Error in Excel and How Does It Occur

When a number is divided by zero in Microsoft Excel, the cell will display the error message "#DIV/0!". For instance, if you input a simple formula such as =3/0 or =4/BLANK, the result will appear as #DIV/0! error, as illustrated in the image below:

How to Fix the #DIV/0 Error

The simplest way to resolve the #DIV/0 error is by employing the IFERROR formula. The IFERROR function serves to detect and rectify errors within a formula. As Microsoft states, "IFERROR returns a value you specify if a formula results in an error; otherwise, it provides the formula's result."

查看此处的教程：如果您的公式是 = A2/B2，您可以输入 =IFERROR(A2/B2, "")。

The formula will evaluate your previous formula and then return either 0 or the result of the formula.

How to Avoid the #DIV/0 Error in Excel

1. The divisor in the formula is not blank or zero.

2. Enter "#N/A" in the cell reference as the divisor. If the result shows as "#N/A", it indicates that your divisor is invalid.

3. Alter the cell reference in the formula or function to another cell that contains a non-zero or non-blank value.

4. Ensure your value is correct.

Has the Excel error been resolved using the above method? For more information on Excel errors, you can refer to the frequently asked question and the other three errors in the following section.

Frequently Asked Questions about #DIV/0 Error

1- How do I eliminate the #DIV/0 error in my calculated field?

The quick way is to use the IFERROR function. Its syntax is IFERROR(value, value_if_error).

If you're using the formula =B1/C1, you can use a formula like =IFERROR(B1/C1,0) instead. After pressing Enter, the content will now display as 0.

2 - How to eliminate the #DIV/0 error in an Excel Pivot Table?

Step 1. Click on the ANALYZE tab, and then click PivotTable.

Step 2. Select Options, and the PivotTable Options window will appear.

Step 3. Under the format section, you can select For error values show:

Step 4. Click OK.

Additional Tip: How to Resolve Other Microsoft Excel Errors (#N/A, #REF!, #NAME?) When working with Microsoft Excel, you may encounter various error messages that can disrupt your workflow. Here's how to address some common errors: 1. **#N/A Error**: This error typically indicates that a formula or function is referencing a value that is not available. To fix it: - Check if the cell reference is correct and points to a valid cell. - Ensure that the function or formula you're using has the correct syntax. - If using VLOOKUP or HLOOKUP, ensure the lookup value exists in the table array. 2. **#REF! Error**: This error occurs when a cell reference is invalid, such as a deleted or shifted cell. To resolve it: - Review the formula or function containing the error and check for any broken cell references. - If cells have been moved or deleted, update the formula accordingly. - In case of external references, verify that the external file or sheet still exists. 3. **#NAME? Error**: This error appears when Excel doesn't recognize a function, cell reference, or text string. Solutions include: - Check spelling and capitalization of the function or named range. - If using a custom function, ensure it's correctly defined and accessible. - Correct any typos in formula syntax or remove unnecessary characters. Remember, always double-check your formulas and references, and utilize Excel's built-in help and online resources to troubleshoot these issues effectively.

1 - How to Resolve the #N/A Error

When you utilize VLOOKUP, HLOOKUP, or other similar functions, the #N/A error occurs because the formula is unable to locate the specific data within the referenced table.

To rectify this error, you need to locate the existing text within the source data.

Causes of the error:

1. Incorrect lookup value
2. Extra space characters exist in the source data.
3. Wrong search range
4. Approximate value and exact value
5. Mismatch lookup value

To handle the error using IFERROR, for example:

If the #N/A error appears in cell C8 when using VLOOKUP, such as in the formula "=VLOOKUP(C8, data, 2, 0)" where the "Price" is not found, you can utilize the IFERROR function to handle this, like this: "=IFERROR(VLOOKUP(C2, data, 2, 0), "Not found")". This will display "Not found" instead of the error message.

2 - How to Resolve the #REF! Error

What causes the #REF! error to occur? This error typically happens in Excel when you have deleted the cells that are being referenced by a formula. The #REF! error appears because the data in those cells is no longer valid, and the formula cannot find the reference it needs to perform the calculation.

To correct the error: Press Ctrl + Z to undo the deletion action. If this shortcut works, the error will be resolved.

To delete the error: Press Ctrl + F to search for the #REF! error, and then type a blank space to replace it.

3 - How to Resolve the #NAME? Error

Typically, this error occurs when your formula includes unrecognized text, function errors, typographical mistakes, incorrect formatting, non-existent references, or other issues. The appearance of this error indicates that you need to rectify some grammatical or formula-related errors. Microsoft recommends utilizing the Formula Wizard instead of typing the formula manually to avoid such issues.

To fix the #NAME? error using the Formula Wizard:

Step 1. Navigate to the Formulas tab and click on Insert Function. This will bring up the Insert Function dialog in Excel.

Step 2. Select the formula you want to insert, and click OK.

Step 3. Excel will open the Function Arguments window. Enter the desired context here.

Conclusion

We've emphasized the #DIV/0 error, but we've also addressed solutions for other frequent issues. Naturally, to prevent such errors, it's advisable to meticulously check the format of the values and the presence of the referenced data.