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

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

Table of Contents

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

Was This Page Helpful?

Updated by Cedric

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.

Read full bio

Written by Roxanne

Roxanne is one of the main contributors to EaseUS and has created multiple posts on digital devices like PCs, Mobile phones, tablets, Mac, etc. She loves to share ideas with people of the same interest.

Read full bio

Product Reviews

TrustScore 4.7 | 323 reviews
  • 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