Mastering Excel: A Comprehensive Guide to Step-by-Step Formula Analysis

Quick Links

• Use the Evaluate Formula Tool in Excel

If you’re collaborating on a spreadsheet, you may want to break down a formula someone entered. While many times this easy, there are others where the formula is more complicated. Excel provides a tool to evaluate formulas step-by-step.

For nested or lengthy formulas, you can see how they work one step at a time from the inside out. This not only helps you to understand the formula and its arguments but can also assist you in troubleshooting formula errors.

As of May 2022, the feature is available on Windows with Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.

Use the Evaluate Formula Tool in Excel

Open your Excel sheet and select the cell containing the formula you want to evaluate. Go to the Formulas tab and choose “Evaluate Formula” in the Formula Auditing section of the ribbon.

You’ll see your formula placed in the center box. Click “Evaluate” to begin. The tool evaluates the formula from the inside out, so you’ll first notice it explaining the underlined portion.

Let’s walk through our nested formula example:

    `=IF(SUM(A1:A5)>20,AVERAGE(A1:A5),"No")`

. This formula says, if the sum of cells A1 through A5 is greater than 20, average the cells in A1 through A5, otherwise, display “No.”

Click here

When you click “Evaluate,” the underlined part of the formula shows the result . In our formula, it sums the cells A1 through A5 and sees if the result is greater than 20.

Then when you click “Evaluate” again, the next underlined portion is evaluated and shows the result. For us, the result is False because the sum is not greater than 20.

When you reach the end, you’ll see the final result that displays in your cell. For our formula this is “No” because the IF function’s formula displays the if-not-then result.

You can then select “Restart” to see the step-by-step evaluation again or “Close” to exit the tool.

Let’s look at another example where you can use the Step In and Step Out features of the tool. For this we’ll use a basic IF function formula rather than a nested formula:

    `=IF(A1=5,"Yes","No")`

. This says, if the value in cell A1 equals 5, display “Yes,” otherwise, display “No.”

Here we see our formula with the underlined portion and the Step In button available.

Click that button to display the constant for the formula. It will appear in its own box. You can see here it’s 1 because that’s the value in cell A1.

You can then click “Step Out” to close that box and continue with “Evaluate” to work through the formula. The next step evaluates if 1 is equal to 5, per our formula.

Click “Evaluate” to see that the result is False, 1 is not equal to 5.

Therefore, the formula’s result is “No.”

When you see a formula that you’re trying to understand, the Evaluate Formula feature in Excel can help. For more, look at these basic Excel functions .

Also read: