How to Correct #DIV/0 Error in Excel | Check Your Full Guide 2023

Check real case from excelforum: “How to fix #DIV/0 error in pivot table excel 2007? Hello! I have a problem. I am trying to calculate an average on a pivot table and it shows the #DIV/0 error instead of values.....Can anyone help? Thanks!”

In this article, we will show you the instant way to correct the #DIV/0 error. Check the basic info of error first, then apply the match solution. Moreover, we have outlined methods to tackle other Excel errors at the end of the page.

Quick Navigation:

What is #DIV/0 Error in Excel and How Does It Happen
How to Correct #DIV/0 Error
How to Avoid the #DIV/0 Error in Excel
FAQ about #DIV/0 Error
Extra Tip: How to Remove Other Microsoft Excel Error (#N/A, #REF!, #NAME?)
To Sum Up

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

When a number is divided by zero, the table of Microsoft Excel will show the #DIV/0 error. For example: if you enter an easy formula like =3/0 or =4/blank, your results will show as #DIV/0 error. As the picture shows:

How to Correct #DIV/0 Error

The easiest way to fix the #DIV/0 error is to use the IFERROR formula. IFERROR function is good to trap and correct errors in a formula. As Microsoft said: "IFERROR returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.”

Check the tutorial here: If your formula= A2/B2, and you can type =IFERROR (A2/B2, "").

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

How to Avoid the #DIV/0 Error in Excel

1. The divisor in the formula isn't blank or zero.

2. Enter #N/A in the cell reference as the divisor. If the results show as #N/A, it means your divisor is invalid.

3. Change your cell reference in the formula or function to another cell with a non-zero or non-blank value.

4. Make sure your value is right.

Have you resolved the excel error by the above method? To seek more info about Excel errors, you can read the frequently asked question and the other three errors in the next part.

FAQ about #DIV/0 Error

1- How do U get rid of #DIV/0 in my calculated field?

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

If you are using =B1/C1, you can use a formula like =IFERROR(B1/C1,0). Then you can press Enter. The content now displays 0.

2 - How to remove #DIV/0 in Excel pivot?

Step 1. Under the ANALYZE tab, click PivotTable.

Step 2. Choose Options and the PivotTable Window will display.

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

Step 4. Click OK.

Extra Tip: How to Remove Other Microsoft Excel Error (#N/A, #REF!, #NAME?)

1 - How to Fix #N/A Error

When you use VLOOKUP, HLOOKUP, or other functions, the #N/A appears due to the formula can not find the source data in a referenced table.

To resolve this error, you need to find the existing text in the source data.

Causes of the error:

• Incorrect lookup value
• Extra space characters exist in the source data.
• Wrong search range
• Approximate value and exact value
• Mismatch lookup value

To handle the error by using IFERROR, for example:

 If the #N/A show in C8 when using vlookup, like =VLOOKUP (C8, data, 2, 0) / "Price" is not found, you can use IFERROR to wrapped around vlookup like =IFERROR (VLOOKUP(C2, data, 2, 0), "Not found")

2 - How to Fix #REF! Error

What cause #REF! did the error happen? If you have deleted the cells referenced by formulas, your excel will show #REF! error because the cell's data is invalid.

To fix the error: Press Ctrl + Z to undo the delete operation. If the Ctrl + Z works, this error will be corrected.

To delete the error: Press Ctrl +F to find #REF! error, and type blank to replace it.

3 - How to Fix #NAME? Error

Generally, when your formula contains unrecognized text, function error, spelling mistakes, incorrect format, non-existed reference, and more. When this error shows, it means you need to correct some grammar or formula error. The recommended method released by Microsoft is to load the Formula Wizard instead of entering the formula manually.

To remove #NAME? Error with the Formula Wizard: 

Step 1. Move to the Formula tab and click Insert Function. Your Excel will display the Insert page.

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

Step 3. Excel will load the Function Arguments window, input the context you want.

To Sum Up

We have highlighted the #DIV/0 error, but we've also provided solutions to other common errors. Of course, in order to avoid these errors, it is best to double-check the format of the values and the existence of the referenced data.