Download Excel Workbook
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!
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.
Let’s wrap the division formula around the IFERROR formula and see what happens.
=IFERROR(A7/B7,”-“)
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.
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:
You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, Power Query, and Macros & VBA!
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.
