Inhibit Excel's #DIV/0! error

Don't let Excel's #DIV/0! error spoil your spreadsheet. If the strategy's sound, inhibit the error.

If an equation or formula attempts to divide a value by 0, Excel displays the #DIV/0! error, but that doesn't mean the formula you're using is wrong. Most likely, the logic is sound enough. In mathematical terms, Excel returns this error when the divisor is blank or 0. As you can see in the spreadsheet below, the formula in questions works fine four out of five times. The formula =D4/B4 in cell E4 returns this error value because cell B4 is 0.

If you're the only one using the spreadsheet, you probably don't need to do a thing; you know what the error means. However, if you share the spreadsheet, you'll probably want to inhibit this error value to avoid confusion or worse — someone might think you're sloppy or even incompetent. To inhibit the display of this error, you can use the IF() function as follows: 

  1. Select cell E2 because you'll want to inhibit all of the formulas in column E, not just the one that's currently displaying an error value.
  2. Replace the simple equation with the following function: =IF(B2=0,"",D2/B2).
  3. Using the Fill handle, copy the new function to cells E3:E6.
When the value in column B is 0 or the cell is blank, the IF() function returns an empty string — that's the double quotation marks component — instead of #DIV/0! error. You can replace the empty string ("") with a more descriptive string, such as "Not applicable," if that makes more sense within the context of your spreadsheet's purpose. Don't let the #DIV/0! error value spoil an otherwise sound spreadsheet. Use the IF() function to suppress the error.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks