Evaluate formula tool helps you figure out what is wrong with your formula. When you are confused about a formula in a worksheet, or is just isn’t work right, this is the tool that you can use to debug that formula.
Sometimes formulas doesn’t behave the way we expect from them. Alternatively, there might be a perfectly fine formula that you want to fully understand. There is a tool named Evaluate Formula that calculates the formula step by step in a pop-up window. You can see how the formula works at each step.
You can find Evaluate Formula button under Formula tab, inside of Formula Auditing group.
Imagine that you are looking at a formula like the one below. Understanding this formula at a glance is out of question. You need to break it apart to see which calculation each part does.
Or you can simply click on the cell contains this formula and open Evaluate Formula tool. This way you can observe calculations made by the formula step by step and understand how all the calculations works from start to finish.
You can run your formula by clicking Evaluate button. With each click on the button, it runs one step of calculation.
By keeping clicking on Evaluate button until the end, you can observe how your formula goes to result.