Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error

Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error

I previously posted this example with the SUMIF function for bypassing the #DIV/O! error.

Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function, but ordinarily you shouldn’t use a catch-all error function like that. Some errors, such as #REF!, #N/A, or #NAME? are invaluable for identifying other kinds of errors which, if undetected, will destroy the integrity of your workbook’s calculations without you realizing it.

The simple formula =B4/C4 in the picture on the left returns that familiar (and ugly-looking) #DIV/0! error when a zero or empty cell is the denominator in a division formula.

The alternative formula =IF(C4,B4/C4,"") in the picture on the right avoids the visible #DIV/0! error, without trying to catch every error under the sun. You can replace the null string with text if you prefer, such as “No prior years”, but the idea is to have a pleasant-looking worksheet for users who’d rather see something meaningful in a cell, or nothing at all, instead of the #DIV/0! error.

Note that this =IF(C4,B4/C4,"") syntax will work in all versions of Excel. Starting with version 2007, you can also use the =IFERROR function, for example =IFERROR(B4/C4,"").