Eliminate DIV/0 error on a sheet or entire workbook

0

Is there a way to get rid of a DIV/0 error on a Sheet and or a workbook, not just a Cell?

Answer
Discuss

Discussion

I don't know what error white is. I do know how to do it with individual cells. I was looking for a short cut to do all at once.
Dave_209 (rep: 4) Jul 16, '18 at 1:45 pm
Add to Discussion

Answers

0
Selected Answer

There is no way to get rid of the error except to get rid of the error lol:
Excel can't divide any number by zero and when your formula demands such a division the error will be indicated. Remove the error to get rid of the indicator.

This formula will avoid the error.

=IFERROR(A1/B1,"")

Here the presumption is that B1 could be any number including zero. If it is zero an error will occur and the IFERROR function diverts the calculation from the faulty division to display "" instead. If effect, you get rid of the error indicator.

However, this formula could itself become the source of an error. Here is how:
Obviously, the division of A1 by B1 should result in a number. But when B1 = 0 the IFERROR function inserts "" as a result which is a string (text). Another formula, referring to that cell in the expectation of finding a number to calculate with may not be able to interpret the null string as zero and therefore throw an error.

The obvious way to deal with this problem is not to insert a null string in a cell which should contain a number. Amend the above formula to write a zero instead: =IFERROR(A1/B1,0). Then, if you wish not to see the zero, suppress its display by cell formatting or worksheet setting.

There are more ways of avoiding accidental divisions by zero. If the method suggested above doesn't work smoothly for you let us take a look at your formulas which produce the error. Best, post a workbook so that we can also see the referenced cells. You can attach a workbook to your original question.

Discuss
0

I rather liked the idea of changing the text color to white; seems like the author removed his post though :/.

If you simply want to make it so they aren't visible without changing or adding formulas, then do this:

  1. Home tab > Find & Select (far right of the tab) > Go to Special
  2. In the window that opens click next to Formulas and then uncheck each sub-option except for Errors and hit OK.
  3. Change the text color to the same as the cell color, usually white.

Now, all the errors will appear to have disappeared without having to change anything in the worksheet.

You can change it back by following the same steps and just changing the text color to black or whatever you want.

Discuss

Discussion

Yes, I removed my suggestion as it seemed too simple in front of expert's answer. But anyway, thanks for liking this idea.
Chhabi Acharya (rep: 100) Jul 18, '18 at 9:21 pm
Yes I liked it's simplicity for sure! I think it can be good to have multiple perspectives to solving a problem; maybe someone in the future who visits this page will prefer the simpler answer, so never feel shy to offer some input :)
don (rep: 1442) Jul 19, '18 at 7:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login