Excel Error and Error handling Roundup
Technology used: Office 365, Excel 2021
You just typed the closing parentheses of that powerful lookup formula that’s going to save you hours next month on the report. You slowly depress the Enter key while feeling empowered as if the Excel gods have chosen you! Feeling the click your stomach turns as you watch the fire bestowed upon you turn into a ball of steam that wisps away. Excel is taunting you. Instead of the sales figures, you see #N/A but what could be wrong?
Excel’s formula errors are unpleasant to see but they’re trying to help us improve our spreadsheet game. In this article, we’ll learn about the types of formula errors that Excel will display, a few formulas to help us avoid them and some thoughts on good use of error trapping.
What are formula errors
Formula errors are the result that Excel returns when it is unable to evaluate and provide a proper answer to the formula in the cell. The errors always begin with a number sign (i.e. #) and then a word to give you a clue as to what is causing the error. Below is a table listing common errors and some of their causes
| Error (As shown in the cell) | Description |
| #N/A | Commonly found in lookup formulas (e.g. Vlookup, Hlookup, Index, Match, etc) if the formula is unable to find what the formula is looking for |
| #VALUE! | Excel’s general error. Simply put, something is wrong with the formula. |
| #REF! | A cell reference is invalid, typical if you delete a row/column that was being referenced by the formula |
| #DIV/0! | Division by zero. The result of which is undefined mathematically. |
| #NUM! | Mathematical operations are being applied to non-numeric cells. You can force text to be treated as numbers using the VALUE() function. |
| #NAME? | Excel doesn’t recognize the formula name, usually the result of a typo (e.g. =SMU(A1:A5) instead of =SUM(A1:A5) |
| #NULL! | The range is not constructed correctly in the formula’s parameters (e.g A5 B7 instead of A5:B7) |
| #SPILL! | Excel is unable to return the results because there isn’t enough room. For example a FILTER function is returning 10 results but there are only 9 blank cells available |
What formulas can be used to stop errors?
Excel gives us two formulas to handle or trap errors. Using these formulas can make your worksheet more robust and user friendly. They can also allow your workbook to function despite Excel being unable to calculate a cell.
IFError
The IFError function allows you to provide an alternate value or formula if Excel encounters any of these errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Its usage is:
=IFError(cell reference or formula, value if error)
The cell reference is any cell or formula that you are testing to see if it has or results in an error.
The value if error is what will result if there is an error. Here you can be really creative. A common usage would be a helpful error message consider something like:
=IfError(1/0, “Division by zero is undefined”)
In this case the formula 1/0 (e.g. One divided by 0) would result in #DIV/0!, we can replace that with the more friendly error message explaining that we can’t divide a number by zero.
But you can also use a substitute formula. For example:
=IFError(1/0, A1/B1)
This formula will result in the value of cell A1 divided by B1. Coincidently if the values of A1 divided by B1 would result in an error, then that error will be displayed.
Using the IFERROR formula gives us a lot of flexibility in trapping and resolving errors.
IFNA
IFNA works very similar to IFERROR except it only works with the #N/A error. Otherwise it’s usage is identical. Recall from the table above that the #N/A error is only encountered with lookup formulas thus this formula will only trap cases where the lookup value can’t be found in the table.
Its usage is:
=IFNA(Cell Reference or Formula, value if error)
For instance:
=IFNA(Vlookup(A1,A2:B20,2,FALSE),”Value was not found”)
Would display Value was not found instead of #N/A if the lookup value couldn’t be found.
Similar to IFERROR you can use a formula or value in place of the value if error parameter, this can help keep the logic of your workbook functioning.
Consider this example:
As you can see, since New York is not in the list an #N/A is triggered, the first version of the formula returns a 0 which we could then use in our workbook if we need to.
Trap every error?
As you’re building your workbook consider the effects of trapping the error versus knowing there is an error. Sometimes we want to see the error, to know that the workbook is not working as we intended. For instance in the example above, we are returning 0 for cities not in the list, however since our worksheet is to tell us total sales $0 is also an acceptable answer. We have no easy way to tell if there were no sales in a city or that the city is not in the list, which might indicate a data issue.
This is a problem known in Computer Science as the semi predicate problem where an error can be confused as a valid value. To prevent this care must be used when selecting what errors we want to trap and which we want to allow to break the workbook.
Excel has a variety of error codes to help the user understand why their formula isn’t working. Excel also has useful formulas for trapping and resolving potential errors. Careful and considerate use of these can lead to well constructed and robust workbooks.
Do you want to learn how to automate your financial workpapers? Do you want to get started with VBA?
My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!
Learn More:
Function pages on Microsoft Support