No #DIV/0! Excel Errors – Fast Friday Fact
Excel errors are no fun. Here's how to clean up some. Image via Wikipedia
Are you tired of looking at spreadsheets that proudly proclaim that you’ve divided by zero? You know those spreadsheets. They’re riddled with ugly fields that show #DIV/O! or #N/A.
Cleaning your spreadsheet doesn’t require an Excel ninja and is a snap to do. Microsoft’s Excel 2007 (sorry if you don’t have it, but it’s 2009 already so it’s time) has a nifty function called iferror. You might be familiar with the function iserror, but iferror is maybe the easiest Excel command to use.
To ensure that #DIV/0! or other errors don’t show on your spreadsheet, simply preface your formula with iferror and then include a default for the error. You’re instructing Excel that if an error exists that you want a symbol or number or other thing to appear in the calculated cell. If no error exists, Excel simply does the calculation.
If you were dividing cell A2 and B2, your formula might look like this:
=A2/B2
With this function, you simply instruct Excel that you’re going to give your own error message if, say, the item in field A2 is a zero. Your formula would look like this:
=iferror(A2/B2,”—”)
All you’ve done is added iferror before your calculation, added a comma after your calculation and included your own error message surrounded by quotes. The result is a more professional, easy-to-read spreadsheet.
Now go fix your reports. We’ll see you again on Monday.
