Excel triggers the #DIV/0! error when you try to divide something by zero, or with an empty cell. Mathematically, anything divided by zero is deemed undefined and results in infinity. You will get this error if you’ve divided your value with the number zero.
You may also encounter the #DIV/0 error if any of the referenced cells in your formula contains a DIV/0! error. This is because any calculation involving infinity also results in an infinite value.
In this article, we will be discussing ways you can navigate through your spreadsheet to locate the values that could be causing the DIV/0! error. Additionally, we have also discussed how you can make Excel ignore this error, so keep reading!
Check your Formula
It is quite possible that you’ve made an error while entering your formula. For instance, you could’ve subtracted the divisor value from itself, resulting in the #DIV/0! error.
Select the cell with the error and head to the formula bar. Look for any errors in your formula. Once you spot the error, rectify it and hit Enter.
Analyze the Referenced Cells
Firstly, let’s look through the cells that are referenced in your formula that resulted in the #DIV/0! error. This will reveal what cell is causing the error. Additionally, it will also unveil if you’ve mistakenly selected an empty cell for division.
Select the cell with the error, then, on your keyboard use the Ctrl + Shift + { shortcut. This will select all the cells your formula has referenced. There, look if you’ve selected a value with the number zero or the #DIV/0! error.
Or, you could use the Trace Precedents function to navigate the affecting cell.
Use the Find Command to Locate Referenced Cells
As we’ve mentioned before, Excel shows the #DIV/0! when you reference another cell with the error while performing calculations in your active cell. One way around this problem can be to locate the cell and its references.
You can use the Find command to not only locate the cells with the error but also check what cells they are referencing to.
- On your keyboard, use Ctrl + F.
- Select the Options>> button.
- Next to Find what, enter #DIV
- Select the fly-out menu next to Look in and choose Values.
- Click Find All.
- In the find results, look under Cell and Formula.
- Check if you see any of the cells with the #DIV/0! error is used in the formulas of the other cells.
Omit the DIV/0! Error
If you’re only creating a template to add values in the future, then this method is for you. You could have entered the formulas but, left the cells empty for the future. As Excel reads empty cells as null or zero, you would run into the #DIV/0! error.
Although this error would automatically be fixed once you enter your numbers, you can choose Excel to look over the error with the IFERROR function.
The IFERROR function is a great function you can use to avoid error codes including #NAME? error, #VALUE! error, #DIV/0! Error, and so on. You will be needing to pass two arguments; your value, and your value if it results in an error.
For example, column A and column B have 5 values. The values that are yet to be filled are entered as 0. To avoid the #DIV/0 error while dividing cells in column A by cells in column B, we’ll be using the IFERROR function.
In a new cell, we entered the =IFERROR(A2/B2, “No value”). When you hit enter, you see that the result is “No value” instead of the #DIV/0! error. This is because we set the value to be “No value”, if the given calculation resulted in any type of error.