#DIV/0 is one of the most frequent errors in tables when we are in middle of work. It is frustrated to see such annoying error. How to correct #DIV/0 error? And how to avoid #DIV/0 error in Excel down the line? Read and bookmark this page, check methods to fix the #DIV/0 error.
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
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:
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.
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.
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.
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:
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")
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.
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.
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.
Related Articles
Word Cannot Complete the Save Due to a File Permission Error Mac [Fixed]
How to Use Windows File Recovery on Windows 10/11
Look! 5 Methods to Fix PNG Files Not Opening in Windows 10/11
[Solved] Cannot Connect to Camera Please Make Sure to Close Other Apps