Excel 2min Tip: How to remove error messages such as #DIV/0!

When you use a formula to calculate a new column of numbers, Excel will sometimes display error messages such as #DIV/0! if a number has been divided by zero.

Use the ISERROR function to eliminate error messages and keep the column of data tidy

Worked example:

1. Start up Excel. Open a blank worksheet.
2. In cells A1 to A6, type: 10, 20, 30, 40, 50, 60 (no commas)
3. In cells B1 to B6 type: 1, 2, 3, 4, 5, 6
4. Now in B2 and B4, replace the 2 and the 4 with 0 (zero).
5. In C1, divide B1 into A1 with the formula = A1/B1. You should see 10 in C1
6. Now copy down the formula to C6. You get the #DIV/0! error message in B2 and B4.

Insert ISERROR to avoid this

1. Click onto cell C1.
2. Either double left click on it, or press the F2 key. This allows you to edit the cell.
3. Amend.... = A1/B1 to... =IF(ISERROR(A1/B1),"-", A1/B1)

   This means: "If A1/B1 turns out to be an error, print a dash, otherwise print A1/B1"

4. Copy the new formula down to C6. The #DIV/0! errors are replaced with dashes.

   The dashes are left justified (the quotation marks in "-" are telling Excel that this is text). Move them to the right by clicking on the Align Right icon.

Contributed by David Carter for our sister site, AccountingWEB.co.uk [1]

[2]