How to find Formula Errors in Excel Spreadsheets

Most Excel workbooks contain errors which in some cases lead to unpleasant “surprises”. Spreadsheet errors come in many different flavors: Some of them are easy to spot but others are much more subtle: When you forget to update an external data source for example or when you copy a formula from the cell above instead of from the cell to the left. Or you end up counting some cells twice etc. etc.

Since there are so many different errors, this blog post is concentrating on formula errors (as they are easy to find) and will leave other types of errors for future blog posts.

Table of Contents

1. What are formula errors?
2. Go To Special
3. Error Checking
4. Inquire add-in
5. Automated error checking
6. Peer review
7. Conclusion

What are formula errors?

As the name says, formula errors are caused by formulas or functions that return an error. Here is an overview:

• #DIV/0!: A number is divided by 0 or an empty cell.
• #N/A: A value is not available to a formula or function. E.g. VLOOKUP doesn’t find a match.
• #NAME?: Some text is not recognized in a formula. E.g. you use a named range with a typo.
• #NULL!: Intersection of two areas that don’t intersect.
• #NUM!: A formula or function contains invalid numeric values. E.g. if IRR can’t find a result.
• #REF!: A cell reference is not valid. E.g. you deleted a cell that is used in another formula.
• #VALUE!: Can occur if a formula contains cells with different data types. For example if you are adding two cells and one is a number and one is a letter.

Excel offers a few built-in ways to find errors in formulas, let’s go through them one by one:

Go To Special

On your ribbon’s Home tab, go to Find & Select > Go To Special... (or via Ctrl-G and Alt-S):

then select Formulas and check Errors:

When you click OK, Excel will format cells with errors on your active sheet in gray:

Error Checking

You can also loop through the errors in a more convenient way (rather than just highlighting them as we did in the previous section): Go to the Formulas tab in your ribbon and click on Error Checking in the section Formula Auditing. This opens the following pop up from where you can click Next to get to the next error:

Inquire add-in

In the more recent versions of Excel, Microsoft has included the Inquire add-in. If you don’t see an Inquire tab in your ribbon, go to File > Options > Add-ins. Then, at the bottom under Manage, select COM Add-ins and click on Go.... In the pop-up check the box next to Inquire. The tab in the ribbon should now show up.

Once the Inquire tab is available in the ribbon, click on Workbook Analysis and you will get an extensive analysis of the contents of your workbook. As an example, you can also list your formula errors:

Automated error checking

By default, Excel shows you errors in formulas (and quite a few more) by highlighting the cell with a green triangle in the upper left corner of the cell. Select the cell and click on the trace error button that appears. This will explain the error as well as suggest help on it. If the error is expected, you can also ignore it:

To control which errors are marked with this green triangle, go to File > Options > Formulas:

Peer review

An effective way to reduce errors and a good complement to automatic error checking are peer reviews. Peer reviews are standard practice in software development (i.e. a colleague looks at your changes before they will find their way into the code base).

For Excel, this has been a difficult task for the longest time as there were no good solutions for version controlling and peer reviewing Excel workbooks.

xltrail, a solution similar to GitHub or SharePoint, makes the task of peer reviewing changes in an Excel workbook trivial: It allows you to see what changed between two versions of the file and makes changes that may have happened in hidden sheets or columns visible:

For example, the above screenshot shows how deleting one column (in red) introduced a lot of #REF! errors that can easily be caught in a peer review process.

Conclusion

We have looked at a few different ways of how to spot formula errors in Microsoft Excel. Let us know in the comments below which method is your preferred one or if you use another technique to spot these type of errors.