You’re not using Excel right until you master these error functions

Excel errors aren't always a bad thing. Sometimes, it's just because there isn't data in the relevant cells yet, and some other times the error itself is the data. Regardless, errors make the sheet look nasty and might make your boss think you don't know how to use Excel. Luckily, you can prevent errors from creeping into your spreadsheet by using Excel's error functions.

Troubleshooting errors in Excel can be a nightmare because they sometimes appear in hard-to-spot places. If you've ever had the pleasure of dealing with VLOOKUP failures in a large Excel sheet, you know what I'm talking about. So, let's look at some useful error-checking and error-handling functions to ensure your spreadsheets are harder to break, and that they break with style.

Functions for error checking in Excel

Detect and identify issues in your sheets

Error-checking functions tell you whether a value is an error by returning TRUE, FALSE, or an error code. They provide valuable insight and allow you to, for instance, use conditional functions to respond to these errors. Just be sure to familiarize yourself with Excel's logical operators if you want to use these error-checking functions with conditional logic effectively.

ISERROR

You can use ISERROR to test whether a value or the results of a formula are an error. It will return TRUE if it detects an error or FALSE if it doesn't. You won't know what the error is, but you won't need to in many scenarios unless you want to do conditional logic that handles that specific error.

=ISERROR(value)

One of the best use cases for ISERROR is checking if you're dividing a number by zero or a non-numeric value (#DIV/0!). This error can break dependent formulas and undermine your analysis. Here is what the formula would look like.

=ISERROR(A1/A2)

If either A2 (the divisor) is zero, the formula will return TRUE. As mentioned earlier, functions like these are best paired with conditional functions like the IF function. That way, you can do something useful with the result (e.g., display a message or perform a different calculation).

=IF(ISERROR(A1/A2), "Unknown value!", A1/A2)

In this formula, the first parameter is the condition, which checks the value returned by ISERROR. "Unknown" is the value it returns if the condition is true, meaning there is an error. A1/A2 is what it returns if the condition is false, meaning no error was found.

ISERR

ISERR works in the same way as the ISERROR function. However, you should use it only when you want to ignore the #N/A (Not Available) error—it commonly occurs when using lookup functions like VLOOKUP and MATCH. It returns TRUE for any other error, except #N/A. Basically, use it if missing data will not affect your formulas, but other errors will.

=ISERR(value)

You can use ISERR in the same way as the ISERROR function.

=ISERR(VLOOKUP(A5, Products!A:B, 2, FALSE))

ISNA

If you are looking for the #N/A error in particular, you can use the ISNA function to detect it. If it detects missing data, it will return TRUE; otherwise, it will return FALSE.

=ISNA(value)

Here’s an example of how to use it:

=ISNA(VLOOKUP(A6, Products!A:B, 2, FALSE))

ERROR.TYPE

When the ERROR.TYPE function detects an error, it returns a code according to the table below:

That means the following example will return 2 if is 0, because that would cause a division by zero error.

=ERROR.TYPE(A1/A2)

You can do something more specific with the error code returned by the ERROR.TYPE function, when you combine it with an IF statement.

=IF(ERROR.TYPE(A1/A2)=2, "Warning: Division error!", "Other error!")

In the above IF statement, if ERROR.TYPE(A1/A2)=2 evaluates to TRUE, it will show "Warning: Division error!" Otherwise, it will display the calculation as normal. You can handle more error codes in the formula by using nested IFs or SWITCH statements.

If the function doesn't find any error, it will return #N/A (the value itself, not the code).

Functions for handling errors in Excel

The best way to manage errors

Error-handling functions will catch errors and fix or replace them. They help you proactively manage errors without needing to write additional condition logic (although you can if you need to). This not only keeps the sheet looking clean, but it also protects dependent formulas from errors.

IFERROR

IFERROR returns the value of the calculation if no error is found. Otherwise, it will return a custom value or calculation when it catches an error.

=IFERROR(value, value_if_error)

Essentially, it's a shortcut that combines ISERROR and IF in one function.

=IFERROR(A1/A2, "Warning: Division error!")

This doesn't make the ISERROR and IF combination obsolete, however. It's still useful for scenarios where there's complex nesting with multiple conditions, or you need a TRUE or FALSE result to work with.

IFNA

IFNA is the error-handling version of ISNA. It returns the calculation if there is no #N/A error, and a custom value or calculation if it detects one.

=IFNA(value, value_if_na)

Here is an example of how to use it:

=IFNA(VLOOKUP(A2, Products!A:B, 2, FALSE), "Warning: Missing Value!")

Build better workbooks

If you aren't using these error functions in Excel, you're not using the spreadsheet program correctly. They're essential to data accuracy, especially if you're dealing with critical or large sheets. With them, you can ensure your calculations and data analyses will be reliable to a reasonable degree. They don't make your Excel sheets 100% error-proof—people can always find a way to break them—but they do remove a lot of potential headaches.