How to Fix and Avoid Excel Formula Errors

Overview

If you use Excel much at all, you’ve likely come across a few different types of formula errors.  They can be annoying to troubleshoot, and they look unprofessional if left unaddressed.  In this article, we’ll explore the most common types of formula errors, what causes them, and how to avoid and fix them.  We’ll also look at some tips for handling formula errors efficiently and effectively.

For a great resource for all of this information in a workbook reference you can keep handy and use to copy and paste some of the approaches mentioned in this article, check out our Excel Formula Errors Guide downloadable workbook. 

Causes and fixes of Excel formula errors

Error-handling techniques

Here are some useful functions, tips, and techniques for handling errors.

Formula functions

There are several functions geared toward identifying and handling formula errors:

• ISERR() – This returns TRUE/FALSE if the formula contains an error other than #N/A.
• ISERROR() – This returns TRUE/FALSE if the formula contains any type of error.
• ERROR.TYPE() – This returns a numeric code, corresponding to the error type 🖼️.
• IFNA() – This returns an alternative value in place of an #N/A error.
• IFERROR() – This returns an alternative value in place of any type of error.
• IF() – This is great for testing all kinds of conditions and handling them specifically for the types of errors you are experiencing or expect to experience.

Handle errors proactively

There are several ways to handle errors before they get to your formulas, including:

• Data validation – if you’re capturing data via inputs in Excel, make sure you put the appropriate data validation in place. Check out this article with lots of data validation tips.
• Power Query – the most flexible and efficient way to get data into Excel, validate it, clean it, transform it, and enrich it.
• Macros – Another flexible approach to cleaning and validating your data with more power than data validation and Power Query.
• Intermediary formulas – you can also clean and validate your data with formulas before the ultimate ones that will output data in the way your data will ultimately be consumed.

Consolidated error checks and tie-outs

A great approach to managing errors and tie-out comparisons is to check for them throughout your files, but also reference those error checks in one central place. That way you aren’t searching all throughout your workbook every time you want to evaluate the errors. Simply review the one central sheet.

Use conditional formatting

Conditional formatting is one of the most powerful and flexible features in Excel. Given how visually-oriented most of us are, it’s a great way to identify errors, exceptions, outliers, and anything else you want to easily call out within your data. Two examples I frequently use are highlighting any formula errors and highlighting any hard-coded values (where there should only be formulas.

Be careful ignoring errors

You may be tempted to wrap all your formulas in the IFERROR() function. And that can certainly work. But that could suppress bigger issues with your data that might go unnoticed because you’ve told Excel to show a zero or blank value when errors are found. This is where those intermediary formulas can be really helpful.

Macros that help address errors

The quicker you identify and resolve formula errors, the quicker you can start analyzing and acting on those formula results.  Here are a few macros I’ve used that help address formula errors.

• Wrap If Error – This will wrap all the formulas in the selected cells with the IFERROR() function and add a specified value to use when there is an error.
• List Cell Errors – This will loop through all worksheets in your workbook and build a list of all the cells containing formula errors with the sheet, range, error type, and a link to the cell containing the error.
• Filter Picker – This has several helpful filter shortcuts, including an option to filter to all cells containing errors.
• Conditional Formatting Picker – This provides several helpful conditional formatting shortcuts, including an option to highlight all cells containing formula errors.

Summary

The more you use Excel, they more you’ll likely encounter errors.  It’s just the price of admission.  But armed with a solid understanding of the errors you’re likely to encounter, what they mean, how to address them, and some error-handling techniques, you’ll be able to proactively prevent them and make that frustration short-lived.

Do you have any great formula error-handling techniques?  Let us know in the comments below!