- ➥ Go to the beginning of the free online Excel Course
When collaborating on a spreadsheet, we may come across a formula or two that someone added, and we may not understand exactly what it works for. Luckily, Excel gives us a tool to be able to evaluate formulas step by step..
In Excel we are going to find a large number of very interesting options such as the possibility of obtaining currency exchange rates, truncating and rotating text, calculating payments, interest or even the term of a loan. We have hundreds of possibilities and one of these is to evaluate formulas.
How to use the evaluate formulas tool in Excel
We start by opening the Excel sheet and selecting the cell that has the formula that we are going to evaluate. Next, we go to the Formulas tab and we are going to select Evaluate Formula in the Formula Audit section that we will find on the ribbon..
Here we will see the formula in the box of this new window. What remains to be done is click on Evaluate to start the process. The tool will take care of evaluating the formula from the inside out, so we are going to notice that it explains the underlined part first.
Let's see our example to understand it a little better: =IF(SUM(A1:A5)>20,AVERAGE(A1:A5),"No"). The formula says that if the sum of cells A1 and A5 is greater than 20, average the cells in question; otherwise, show “No”..
Once we click on Evaluate, the underlined part of the formula will show the result. In the example formula, add traps A1 to A5 and check if the result is greater than 20.
Then, we will click on Evaluate again, the next underlined part is evaluated and allows us to see the result. In this case, the result is False, because the sum actually does not exceed 20.
Once we reach the end, we will see the result in question. For this formula the result is "No", precisely because of what we mentioned before.
We can also choose Restart to be able to start the evaluation step by step or simply Close to be able to exit the tool.
Let's give another example for better understanding. Here we are going to see the input and output functions of the tool. For this we will use the basic IF function formula: =IF(A1=5,"Yes", "No"). This says that if the value of cell A1 is equal to that of A5, then display “Yes”, otherwise just display “No”.
Here we are going to see the formula with the underlined part and the step by step button available right next to Evaluate.
We will click on Step by step to be able to visualize the constant of the formula. We can see that it appears in its own box. Here we can see that it is 1 because it is the value that cell A1 has.
After this, we can click Exit to close the window, we can also continue with Evaluate to work with the formula in question. So this step will evaluate if 1 equals 5.
So, we click on Evaluate to see that the result is False, 1 is not equal to 5.
In this way, the result of the formula will be “No”.
When we see a formula that we don't quite understand too much. The Evaluate function will help us understand it a little better.