Pinterest Pixel

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

The #DIV/0!  error is a simple error to understand. It can occur mainly when we attempt to... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline 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:

download excel workbook Div-error.xlsx

 

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

See also  The COUNTIF Function for Beginners in Excel

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.

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

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

 

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.

See also  BETWEEN Formula in Excel

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

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

=IFERROR(A7/B7,”-“)

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

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.

See also  MONTH Formula in Excel

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

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 (-).

 

Conclusion

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.

 

HELPFUL RESOURCE:

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!

If you like this Excel tip, please share it
How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline How to fix the #DIV/0! error in Excel Formulas | MyExcelOnline
See also  Excel Pro Tip: Reverse Sales Tax Calculator in Excel
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...