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.
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
IRRcan’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:
On your ribbon’s Home tab, go to
Find & Select >
Go To Special... (or via
Formulas and check
When you click
OK, Excel will format cells with errors on your active sheet in gray:
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:
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
Add-ins. Then, at the bottom under
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.
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:
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
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.
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.