How to Use the “Evaluate Formula” Feature in Excel

Did you just put together a very smart formula in Excel that makes you want to give yourself a pat on the back?

But plot twist – Excel returns an error instead of the results? 😲

How do you troubleshoot the formula to identify the problem?

Doing it manually might take you hours of pondering on the issue with no guaranteed output.

You can understand and identify the problematic part of your formula by evaluating it in Excel (also called debugging).

Formula evaluation enables you to break down and understand different parts of a formula (step-by-step) to identify the problem more systematically 📚

There are two smart ways how you can do it in Microsoft Excel, and I am going to cover them both in this tutorial. Get your free practice workbook for this guide here, and come along with me to learn these.

Using the F9 key
Using the “Evaluate Formula” tool
Conclusion

Using the F9 key

If you’re a coder you’d know the F9 key as a supernova.

The F9 key is an equally mighty superstar when it comes to Excel. Here I have a long formula with some functions combined in it 🦹‍♂️

However, all that it returns is an error, and I can’t really understand what’s going wrong.

Let’s try using the F9 key to figure it out.

Step 1) Click on the cell containing the formula to activate the formula in the formula bar.

Step 2) Now go to the formula bar and select each part of the formula using your cursor. I have selected the first part, the SUM function, and the numbers to be summed.

Step 3) Press the F9 key.

Excel returns the result of the formula which is basically the sum of the cells A2:A4.

The sum of 31 for these cells tells that Excel can run the formula until the first part without posing any errors.

Step 4) Repeat the same step for the next part of the formula. Select it (i.e., PRODUCT(B2:B4)) and press the F9 key.

Excel returns 180. This part of the formula is also functioning perfectly 👌

Step 5) Check the next part by selecting (i.e., AVERAGE(D2D4)) and pressing the F9 key.

There you have it – the #NAME! error.

This is the part of the formula that’s causing the entire formula to run an error.

This narrows down the scope of the formula’s area you need to check for errors. Look closely into this part of the formula to find the error.

Step 6) Press the Esc key to go back to the original formula and crack down on the mistake.

Pro Tip!

A #NAME! error usually occurs when you’ve used a function name that doesn’t exist in Excel’s function library (maybe a misspelling).

Or if you have used cell references / named ranges that do not exist.

In the above formula:

Click to copy

The AVERAGE function looks appropriate. But the cell range D2D4 seems wrong (a colon is missing). It should be written as D2:D4 instead.

Step 7) We have corrected the cell range by adding a colon between D2 and D4.

Step 8) The formula has now been amended. Press enter to see the results now.

No more #NAME! error this time. The formula now returns the correct results of the formula i.e., 559.33 🚀

This is how you can use the F9 key to evaluate any formula in Excel in parts.

Finding the error in a formula is only one instance of why you might want to use the Evaluate formula tool.

This tool can prove handy for debugging long and nested formulas. It helps understand how a formula works (one step at a time) and shows the intermediate formula results at multiple stages.

Using the “Evaluate Formula” tool

The same exercise that we did above using the F9 function can also be done using the Evaluate Formula Tool of Excel.

It is an in-built tool that appears in the Formula tab, and I will show you how to use it just now.

We’re no longer looking for errors in our formula. This is just a logical function that we’d step by step evaluate to see the intermediate results of each part 🧐

Step 1) Select the cell containing the formula.

Step 2) Go to the Formulas Tab > Formula Auditing Group > Evaluate Formula

Evaluate formula feature on the formulas tab

This will launch the Evaluate Formula dialog box as here.

Step 3) Click on the Evaluate button to see what lies behind the underlined part of the formula (C6).

Excel breaks the logical test into absolute numbers. Very clearly, the logical test asks if 559.33 is greater or smaller than 600.

Pro Tip!

When the underlined part of your formula refers to another cell that contains a formula, like this formula contains reference to Cell C6, the Step in button would be enabled 🎯

Clicking on the Step in button would show you the contents of the referenced cell.

Clicking on the Step Out button button would take you back to the previous formula.

Step 4) Again, click on the Evaluate button to see the result of this logical test.

The logical test returns FALSE.

After the first nested operation (the logical test) of this formula is performed, now Excel evaluates the next part which is to return the value_if_false / true.

Step 5) Click on the Evaluate button again to see what Excel returns for this formula in the next step.

Excel returns value_if_false cell values

The next result is the value_if_false which is the final output of this function.

Since we have reached the final output of this function, the Evaluate button is now replaced with the Restart button 😎

The Evaluate Formula tool of Excel shows what runs behind the formulas in Excel. We type in long and complex formulas and Excel returns the result for them.

However, how does Excel evaluate those formulas? The Evaluate Formula tool shows this to you step by step.

Pro Tip!

A potential advantage of using the F9 key instead of the Evaluate Formula tool is the sequence.

The Evaluate formula tool will evaluate each part of the formula in a sequence starting from the first part. So, say if you want to evaluate the 6th part of the formula, you’d have to press the evaluate button that many times sequentially to reach the 6th part of the formula.

Using the F9 key shortcut, you can instantly select whichever part of the formula you want to evaluate and hit the F9 key to see what it returns.

Conclusion

If you enjoyed learning what runs behind a formula in Excel spreadsheets, we are best friends. I love to walk through every argument of a formula to understand how each part of a formula is worked out by Excel before the ultimate result is returned 📑

Just like debugging formulas, my following Excel tutorials offer great insight into different aspects of using formulas in Excel (like dropping them down in a single go and fixing related issues).

Hop on to the following links to check them out.