Table of Contents
- Best Data Recovery Software
- Video Recovery
- SD Card Recovery
- Recycle Bin Recovery
- Recover Data from USB
- Recover Deleted Emails
- Hard Drive Not Showing Up in Windows
- Recover Unsaved Word Documents
- Recover Deleted Files in Windows 10/11
- Recover Files from Virus Infected Hard Drive
- Best Free Photo Recovery Software
- Recover Files from Formatted Hard Drive
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.
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.
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.
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.
Was This Page Helpful?
Cedric Grantham is one of the senior editors of EaseUS who lives and works in Chengdu, China. He mainly writes articles about data recovery tutorials on PC and Mac and how-to tips for partition management. He always keeps an eye on new releases and likes various electronic products.
EaseUS Data Recovery Wizard is a powerful system recovery software, designed to enable you to recover files you’ve deleted accidentally, potentially lost to malware or an entire hard drive partition. Read More
EaseUS Data Recovery Wizard is the best we've seen. It's far from perfect, partly because today's advanced disk technology makes data-recovery more difficult than it was with the simpler technology of the past. Read More
EaseUS Data Recovery Wizard Pro has a reputation as one of the best data recovery software programs on the market. It comes with a selection of advanced features, including partition recovery, formatted drive restoration, and corrupted file repair. Read More