Are extremely long, formulas nested arguments intimidating you? The Evaluate Formula feature can help you untangle these complex formulas! This tool essentially solves complex formulas step by step. In this guide, we’re going to show you how to step through complex formulas using the Excel Evaluate formula.

Download Workbook

Evaluate Formula Window

You can open the Evaluate Formula window by clicking its icon under the Formula tab. Evaluate Formula is the final item in the Formulas Auditing section.

The Evaluate Formula window offers three options:

  • Evaluate: Shows the value of the active section
  • Step In: Displays the formula in the selected reference
  • Step Out: Goes back to the previous cell and the formula

The Step In and Step Out buttons become disabled if there are no formulas in the selected direction. Also, you cannot step in if the formula refers to a cell in a separate workbook

How to step through complex formulas with Evaluate feature 02

You can use these tools on the selected cell. The cell selection is done automatically by Excel based on the formula execution order. Below is the list of rules that Excel takes into account:

  • Left-to-right
  • Inner-to-Outer order of parentheses
  • Mathematical operations order
  • Logical order
  • Inner-to-Outer order for nested functions

Functions

Let’s see the tool in action! Also, feel free to download our sample workbook.

If you click on the cell M14 and open the Evaluate Formula window, you will see the following display:

By the default, the left-most cell will be selected. A selected cell is shown with an underline. Clicking the Evaluate button replaces the reference of the cell with its value.

How to step through complex formulas with Evaluate feature 04

You cannot redo an Evaluate action. However, these changes will not be applied to your actual formula in the cell. Let’s move onto the next reference. This time, we will press Step In.

Step In action displays the interior formula of the stepped in cell in new section. For each stepped in reference, you will see a new section.

How to step through complex formulas with Evaluate feature

Use the Step Out button to return one step above. You cannot step out once you have reached the top level. If anything goes wrong, you can always use the Close button and you can start over anytime.

To learn more about identifying and analyzing spreadsheets, see Formula Auditing.