When you’re working with Microsoft Excel, it’s important to be aware of the formulas that are used. A formula is a set of instructions that tells Excel how to do something. There are many different formulas in Excel, and it can be hard to know which one to use when you’re trying to do a simple calculation. Here’s a guide on how to evaluate formulas in Microsoft Excel:
- Look at the formula name The first thing you’ll want to look for is the formula name. This will tell you which part of Excel the formula applies to, and it will also give you some information about what type of data the formula is applied to. For example, if there’s a function called “SummarizeData” in your workbook, then the SummarizeData() function will apply the SummarizeData() formula to all data in your workbook.
- Check whether it works with data Next, look at whether the formula works with data. If it doesn’t work with data, then you can try changing some of the variables or values within the equation before trying again. If everything still doesn’t seem right, then you may need help from an experienced Microsoft Excel user!
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.
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.”
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.
