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.
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
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.
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.
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.
