Excel Tips and Tricks #457 - Excel Error Checking and Handling

Error handling functions

There are a range of functions in excel that can check and handle errors in excel workbooks.

ISERROR or IFERROR?

ISERROR can pick up all error types #VALUE!, #REF!, #DIV/0, #NAME?, #NULL! including #N/A errors.

The function can be used to check for errors in the values and then return a TRUE value if an error is present:

=ISERROR(value)

ISERROR can be used in conjunction with the IF function to check for an error and output a desired message if found:

=IF(ISERROR(A1), “output”)

The function can also be used in a formula with COUNT and IF functions to provide a total count of the number of errors in a cell range.

=COUNT(IF(ISERROR(CELL RANGE),1,” “))

On the other hand, IFERROR combines both the IF and ISERROR functions. Rather than combining functions manually as above, IFERROR allows you to provide a backup value or formula to be used if the primary one returns an error in a more simplified manner.

Do note that IFERROR carries an inherent risk of ‘covering up’ errors if used inappropriately, so remember that it should only be used to flag errors, not make the errors go away.

Tips on how to use IFERROR (and how not to use it!) were covered in more detail in tip #422. 

FILTER

The FILTER function in Excel is part of the relatively new set of array functions and is used to filter a range of data based on the criteria that you specify:

FILTER(array, include, [if_empty])

In the example data set below, I want to pull out information on items where there are errors in the total cost and units calculation.

To do this, the FILTER and ISERROR functions can be combined to filter the data where errors have occurred:

=FILTER(array, ISERROR(range))

The formula above provides me with the following output where I can clearly see the item number, description and then details of the error:

XLOOKUP

We introduced XLOOKUP in tip 337 as a more flexible and reliable alternative to VLOOKUP. As an added benefit, XLOOKUP has an option to handle #N/A errors.

For example, in the data set below I would like to find the units of different items using XLOOKUP. However, if information regarding an item is missing, I can enter a value into the optional 4th argument to replace a #N/A error with the specified output:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found ])

Again, remember that this shouldn’t be used to cover up the error, rather to make it easier to manage. In the example below, by identifying where information is missing we can follow up on this error to resolve it.

Built-in Error Checking

Error checking

Another way to check for errors in formulas in excel is using the built-in ‘Error Checking’ in excel in the formula auditing tab.

The dropdown offers three options including:

1. Error checking
2. Trace error
3. Circular References

The error checking option allows you to identify and check for common errors when using formulas. The tool will identify and evaluate errors in the worksheet one by one to give you more information on the location of the error and the type of error.

You can then use error checking to either trace the error, ignore it, or edit the formula.

The trace error option allows you to trace cells in a formula that display an error. For example, the error in the formula cell D9 is caused by the value in cell O9.

The circular references option allows you to detect circular references in formulas. When there are circular references in your workbook, it will identify the location of the circular references.

Conditional formatting

Conditional formatting can also be used to highlight errors in the worksheet. You can do this by setting up a new rule. Then, select the ‘Format only cells that contain’ rule type.

From the ‘format only cells with’ dropdown select ‘Errors’:

You can select a preferred format. In the example below I’ve selected a red fill for cells with errors. Once confirmed, the output looks as follows:

Highlight using ‘Go To Special’

Under the ‘Find & Select’ option, we can use ‘Go To Special’ to identify and select cells with errors by selecting ‘Errors’ under ‘Formulas’.

This will select all cells with errors.

To retain visibility of this selection, you can use ‘Fill colour’ to highlight the cells.

While not an exhaustive list, we’ve hopefully given you a few ideas on how to minimise errors in your spreadsheets. If you’re looking for more guidance and best practice on reviewing your spreadsheets for errors, this recent webinar and our thought leadership paper How to Review a Spreadsheet may come in handy. Enjoy!