Evaluate Formula in Excel

What is Evaluate Formula in Excel?

Evaluate Formula in Excel tool is used to understand the formula’s working. This feature works on each argument in a formula to recognize and secure any errors that occur.

The Evaluate Formula assists us in evaluating complex formulas and examining each part of a nested function to calculate the final result.

For example, we will calculate the data’s total using the MAX IF Excel Formula using the EVALUATE FORMULA Option.

Explanation and Uses

There are many calculations and logical tests to evaluate the nested formula calculation. The Evaluate Formula shows the different parts of a nested formula in the dialog box and evaluates in the order the formula is calculated. 

To evaluate the formula uses the F2 key for toggling between Edit and Enter modes in Excel. To change the formula, select the cell containing the formula and press F2 to enter the Edit mode. The cursor points at the end of the closing parenthesis in the cell. The left and right arrows are used to navigate within the formula. To shift to select the formula parts, the arrow keys are used. Click the Delete or Backspace keys to delete the cell references or elements of the formula. Press Enter to complete the formula for editing. To exit the Edit mode, press the Esc key without changing the other modes. Here, we will show a quick, simple, and effective way to fix formulas and functions in Excel.

The Evaluate Formula saves hours; it is the tool that will help to troubleshoot the formula. The formula that contains a cell references a cell on the worksheet; Excel adjusts those cell references automatically when a copy or moves the cell that contains the formula, no matter where a copy or move it within the worksheet or workbook.

How to Evaluate Formula in Excel?

#1 – Evaluate Excel Formula Using F9 Key

We will use IF Excel Formula using F9 Key– IF Function tests whether the condition is fulfilled and returns the result as “true” and “false” for the logical text or value.

The syntax of the IF Function is; =IF(logical_test,value_if_true,value_if_false).

For example, the below image shows values, and we will calculate the logical condition of the given data using the IF Excel Formula using the F9 Key.

In the table,

• Column A: contains Values.
• Column B: contains Output.

The steps to evaluate the formula using the IF function are as follows:

• Step 1:  Select empty cells for the output. We have selected cell B2 in this case.

• Step 2:  Next, we will enter the IF formula in cell B2.

• Step 3: Enter the condition as A2<100.

• Step 4: The complete formula is =IF(A2<100“True”,“False”) in cell B2.

• Step 5: After entering each value in the preceding step, press the Enter key. The result is obtained as True in cell B2.

• Step 6: Press Enter and drag the cursor to cell B3, as shown in the following image.

If we do not understand the formula, we can evaluate it by pressing the F9 key.

• Step 7: The F9 key is used to evaluate quickly and fix the formula to understand and proficiently work in Excel. The F9 key is used to determine only the part of the formula by replacing the actual formula with the result of the formula.

Note: The above example shows the logical test used in the formula, i.e., A2<100. We detect the status by evaluating the logical test. If the value is <100, then the result is “True,” or else the result is “False.” When the condition tests return “True,” that means the logical test is “TRUE,” and if the logical test is “FALSE,” it will return “False.”

#2 – Example

For example, the below image shows values, and we will calculate the logical condition of the given data using the IF Excel Formula using the F9 Key.

In the table,

• Column A: contains Name.
• Column B: contains Output.

The steps to evaluate the formula using the IF function are as follows:

• Step 1:  Select empty cells for the output. We have selected cell B2 in this case.

• Step 2:  Next, we will enter the IF formula in cell B2.

• Step 3: Enter the condition as A2= “John”.

• Step 4: The complete formula is =IF(A2= “John”,“True”) in cell B2.

• Step 5: Press Enter key. The result in cell B2 is returned as TRUE in cell B2.

• Step 6: Press Enter and drag the cursor to cell B3, as shown in the following image.

If we do not understand the formula, we can evaluate it by pressing the F9 key.

• Step 7: The F9 key is used to evaluate quickly and fix the formula to understand and proficiently work in Excel. The F9 key is used to determine only the part of the formula by replacing the actual formula with the result of the formula.

Note: The above example shows the logical test used in the formula, i.e., =“John.” We detect the status by evaluating the logical test. If the name is =“John,” then the result is “True,” or else the result is “False.” When the condition tests return “True,” that means the logical test is “TRUE,” and if the logical test is “FALSE,” it will return “False.”

#3 – Evaluate Formula by Using EVALUATE FORMULA Option

We will use SUM Excel Formula using EVALUATE FORMULA Option– SUM Function enables users to add individual numeric values, cell references, ranges, or all three together. The Auto Sum function in Excel, i.e., “∑” automatically adds all the numeric values listed in a particular row or column.

The syntax of the SUM Function is; =SUM(number1,[number2],…).

For example, we will calculate the data’s total using the SUM Excel Formula using the EVALUATE FORMULA Option.

In the table,

• Column A: contains Numbers.
• Column B: contains Output.

The steps to evaluate the SUM formula using the EVALUATE FORMULA are as follows:

• Step 1:  Select empty cells for the output. We have selected cell B2 in this case.

• Step 2:  Next, we will enter the SUM formula in cell B2.

• Step 3: Enter the condition as A2:A10.

• Step 4: The complete formula is =SUM(A2:A10) in cell B2.

• Step 5: Press Enter key. The result is returned in cell B2 as shown in the image below.

• Step 6: Select the Evaluate Formula option from the Formula Auditing in the Formula tab.

• Step 7: Select the formula cell in the worksheet.

• Step 8: The window called the Evaluate Formula pops up.

• Step 9: The formula is shown in the Evaluation box.

• Step 10: Select the Evaluate button to start the evaluation of the formula

• Step 11: The evaluated result is shown in the Evaluation box.

• Step 12: Select the Close button to close the evaluation. We can also select the Restart button to evaluate the formula.

Important Things to Note

• In this article, we discuss How to Use Evaluate Formula Using F9 Key 
• Press the Esc key when evaluating the formula.
• The “#REF!” error occurs when it contains multiple cells.
• The excel shortcut keys ALT + M + V allows us to evaluate the formula cell by cell.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the Excel Evaluate Formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Evaluate Formula in Excel. Here we explain various methods to evaluate excel formulas, with examples & downloadable excel template. You can learn more from the following articles –

• Excel Negative Numbers
• ROWS Function In Excel
• VLOOKUP for Text