Each time you click the Evaluate button, Excel will solve the underlined part of the formula and show you the result. Here’s the same worksheet we looked at in a previous video when we talked about debugging formulas using the F9 key. The Age column contains a formula that calculates age from Birthdate, and the Status column uses the IF function to label each person in the list as either an Adult or Minor. Let’s use the Evaluate feature to see how these formulas work. You can find Evaluate Formula on the Formulas tab of the ribbon, in the Formula Auditing group. To use Evaluate Formula, select a formula, and click the button in the ribbon. When the window opens, you’ll see the formula displayed in a text box with an Evaluate button below. One part of the formula will be underlined – this is the part currently “under evaluation”. In this case, solving the TODAY function is the first step in solving this formula. When we click Evaluate, the TODAY function is evaluated and returns a date in Excel’s serial number format, and the YEARFRAC function is underlined, as the next step in the evaluation process. On the next click, the YEARFRAC function is evaluated, and the INT function is underlined. One last click and the formula is solved with a result of 12. Notice that the Evaluate button changes to Restart at the end of the evaluation process. Click Restart to evaluate the formula again. Now let’s Evaluate the Status formula. In this case, E5 is solved first to get Age, which is used in the logical test in the IF statement. 12 is less than 20, so FALSE is the result of the logical test. “Minor” is the final result since “Minor” is given as the value to use if false. Let’s restart the evaluation, and look at the “Step in” and “Step out” features. In this case, because E5 is itself a formula, we can “step in” and evaluate that formula, too. The process is the same as a normal evaluation. When done, click “step out” to continue evaluating the original formula. The ability to step into other formulas is an optional feature. It’s available when a formula refers to other formulas or to cell references. If you step into another formula and decide you don’t want to evaluate it, just click “Step out” to continue evaluating the main formula. So there you have it. Evaluate Formula is a handy compliment to using the F9 key. And it’s a great way to understand how Excel solves formulas.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
