How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline
The #DIV/0!  error is a simple error to understand. It can occur mainly when we attempt to divide two values, directly or referenced in certain cells. It is basically caused when the divisor is 0.

In this article, you will learn the following:

Let’s consider different examples to examine different causes of #DIV error and learn how to get rid of DIV 0! Download the Excel Workbook below to follow along and understand better:



What is #DIV error in Excel?

Excel may return the #DIV/0! error when the:

  • Number is divided by blank cell
  • Number is divided by 0

If we click the exclamation warning sign beside #DIV/0! Error, excel would explain that “The formula or function used is dividing by zero or empty cells.”


How to fix #DIV Error In Excel

Example 1 – Number is divided by blank cell

The Excel DIV 0 is a common and simple error that can easily be dealt with. To correct this error, all we have to do is make sure that the cell reference provided does not belong to an empty cell.

In the example below, the formula in column C is a simple dividing formula. But, cells B9 and B12 have blank cells respectively, and hence result from the division in column C returns the #DIV/0 Error.


Now if we simply correct our mistakes by editing the cells in column B, the errors will disappear.

Like so!

How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline


Example 2 – Using the IF function

In most cases, the referenced cell should actually contain the value 0 or be empty. A simple solution to this will be to use an IFERROR formula and get rid of the error message.

We can design the IFERROR formula in such a way that it either returns 0 or any value as desired if the result of the original formulas occurs to be an error.

What does it do?

It returns a value that you set if a formula has an error

Formula breakdown:

=IFERROR(Value, Value if Error)

What it means:

=IFERROR(The Formula, What do you want to show if The Formula has an error?)

If you have a calculation that results in an error like, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, then you can clean it up by using the IFERROR function which allows you to replace the error it with a custom text.

In this example, cell B9 contains 0, and cell B12 is empty. The corresponding cells C7 and C12 are thus displaying #DIV error.

How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline

Let’s wrap the division formula around the IFERROR formula and see what happens.


#DIV Error in Excel

Here, we specified in the formula that if the value in cell C7 returns an error, it will be replaced by our custom text – hyphen (-).

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline

Hence as cells B9 and B12 had blank or 0 values, the corresponding cells in column C should produce an error but IFERROR changes it to display hyphen (-).



So it’s clear now that the DIV 0 error is simply caused when a number is being divided by 0 or a blank cell. If any other formula is applied on the cell displaying the #DIV/0! Error, then it will also show the same error.

Now you can easily overcome this problem and continue with smooth functioning in Excel!

Click here to learn about the Top 20 Common Errors that you may encounter while working on Excel.



Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!


Click here to access these FREE Excel courses!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn