# Error Handling in Excel Spreadsheets

If the formulas in your Excel reports and analyses display errors like #DIV/0! or #VALUE!, here are some simple ways to trap them.

6136

It’s virtually guaranteed. If you have a bunch of formulas like this…

M43:   =C43/D43

…then the divisor, D43 in this instance, will have a value of zero at some point. And when it does, your formulas will return: #DIV/0!

A visitor recently sent me a workbook with hundreds of results like this. It also had #VALUE! errors where his formulas were trying to divide a number by a label.

Whenever you set ratios in your workbook, it’s always a good idea to decide how you want divide-by-zero and other such errors displayed. For ratios, where divide-by-zero calculations are common, I like to display an empty cell for such errors. So my formula would be:

M43:   =IF(N(D43)=0,””,C43/D43)

Here, the N() function returns zero if cell D43 contains text, an empty cell, or zero. Otherwise, it returns the number in cell D43.

For more complex formulas, where the potential reason for an error might not be obvious, you could use:

M43:   =IF(ISERROR(formula), error-value, formula)

Years ago, however, I complained about this technique to Microsoft’s Excel team. As I remember, I said it made no sense to use formulas like this:

=IF(ISERROR(some-long-and-complex-formula), error-value, the-same-long-and-complex-formula)

There are two problems with this approach, I wrote. First, it doubles the complexity of our formulas. Second, where the formulas use lookups or other processor-intensive calculations, this technique doubles an already-long calculation time.

=FUNCTION(some-long-and-complex-formula, value-if-error)

Some years later, during the Excel 2007 beta, one of the Excel team asked if I had noticed the IFERROR function, which they had added because of my suggestion. So, in New Excel (Excel 2007 and above), you now can use a formula like this:

M43:    =IFERROR(C43/D43,””)

If you use New Excel and you don’t use IFERROR in your spreadsheets, it’s time to do so.

Previous articleEight Tips to Make Excel Easier to Use
Next articleExcel and the Rule of 72
Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,