Dividing by zero results in infinity ...

... but not in Excel

How to Suppress the Excel #DIV/0! Error


I have numbers in Columns A and B of an Excel worksheet. In column C I have a formula that divides the number in Column A by the number in Column B. This works OK provided the number in Column B is not zero or missing. When it is, I get the #DIV/0! error. Is there a formula I can use to suppress the error?


Yes there is. Here is a formula to try in cell C1:


As a bonus of applying this formula and copying it down Column C, you can sum the column content without #DIV/0! appearing as the result.


DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.