ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks

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

Author: Bani Lamba

Published: 13 Oct 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello and welcome back to Excel Tips and Tricks! This week we have a Creator level post in which we’re looking at new and improved ways of checking for and handling errors.

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)
457
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”)
457
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,” “))
457
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:
457

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.
457

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
457
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.
457
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.
457
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.
457

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’:
457
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:
457

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’.
457
This will select all cells with errors.
457
To retain visibility of this selection, you can use ‘Fill colour’ to highlight the cells.
457
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!
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250