How to Find and Fix Errors in Complex Formulas in Excel

Add to Favorites
Author:

Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to find out how to fix a formula or function in Excel that is broken or not returning the result that you expect.

Thankfully, there is a really great tool that will help you troubleshoot the formula and pin-point the problem.  It is called Evaluate Formula and it will save you hours!

Below, I will give you a sample problem and show you, step-by-step, how to find and fix the error.

Here, we have what should be a Vlookup formula that is not returning what we expect.  Let's take a look at the formula now:

This is one formula that contains three functions, the IF function, the IFERROR function, and the VLOOKUP function.  Looking at this, though it may seem a bit complex, everything appears to be input exactly how it should be.  All functions look OK and the IF statement check at the front, that requires cell A6 to equal yes, looks good to me, but the formula is not working.

Let's use the Evaluate Formula feature so that we dont waste any time troubleshooting this formula.

Select the cell that contains the formula or function that is not working and then go to the Formulas Tab and click the Evaluate Formula button, which is located in the Formula Auditing section of the menu.

The entire formula contents of the cell appear in the window.  The Evaluate Formula feature will walk you through the execution of the entire formula and show you exactly what it sees.

Notice how A6 contains an underline beneath it?  This is what is evaluated first within this formula.  Click the Evaluate button at the bottom of the window to see how Excel evaluates this, in this case, what it will return for the cell reference A6.

The value of cell A6 has been returned into this formula and we can see how Excel is now comparing that value to the value yes to see if they are equal.  Hit Evaluate again.

FALSE.  We can see that the IF statement in the cell is preventing anything else from happening with this function because the condition in the IF statement has evaluated to FALSE.  Hit the Evaluate button once more and you will see the final result, which is a blank.

Now, hit the Restart button that appeared where the Evaluate button used to be and you can start over in order to more closely analyze everything.

This, time, if you would like, you can hit the Step In button, which allows you, when possible, to enter a cell that is being referenced by the current formula.  This feature is great for really complex formulas that are dependent upon many cells in Excel.

If you do that in this case, everything still looks OK for cell A6.  So, hit Step Out and let's get back to the main formula.  Now, the next step has already been evaluated for us so we can see what cell A6 returns to the formula.  Now, take a close look!

CRAP!!!!  There is a dirty stinking space after the yes in cell A6!!!!

Let's correct that by closing the Evaluate Formula window and going to cell A6 to remove the space from the end of "yes" and try our vlookup formula again to see what happens.

It finally works!

Now, we could have probably figured that out on our own after a while, but the Evaluate Formula feature showed very clearly, albeit, you must have been paying attention, that there was an extra space after the value coming from cell A6.  

Believe it or not, extra spaces, which are by nature very difficult to find, cause many many many MANY hours of troubleshooting for people.

In another tutorial, I will show you a simple way to avoid some of the headaches associated with spaces.

I hope you enjoyed the tutorial! :)

 


Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Highlight, Sort, and Group the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allows...
How to View the Selection Pane in Excel When there are No Visible Objects
Tutorial: The Selection Pane is a very useful tool in Excel that allows you to view all of the objec...
How to Create and Manage a Chart in Excel
Tutorial: In this tutorial I am going to introduce you to creating and managing charts in Excel. Bef...
Make Complex Formulas for Conditional Formatting in Excel
Tutorial: How to make complex formulas for conditional formatting rules in Excel. This will serve as...
Complex Structured References (Table Formulas) in Excel
Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Tutorial Details
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course