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

#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.

 Download for Win Recovery Rate 99.7%
 Download for Mac Trustpilot Rating 4.8

Page Table of Contents

Updated By

Tracy King

Updated on

24 July 2024

Min Reading

9 Min

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:

div0 error

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:.

pivot table format

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.

N/A error

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.

ref error

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.

name error

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.

formula wizard

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.

 Download for Win Recovery Rate 99.7%
 Download for Mac Trustpilot Rating 4.8

  • Updated by

    Tracy became a member of the EaseUS content team in 2013. Being a technical writer for over 10 years, she is enthusiastic about sharing tips to assist readers in resolving complex issues in disk management, file transfer, PC & Mac performance optimization, etc., like an expert.…
Why Trust EaseUSEditor Reviews

20+

Years of experience

160+

Countries and regions

72 Million+

Downloads

4.8 +

Trustpilot Score

Data Recovery Service

Search