EVALUATE FORMULA in Excel to find errors

Image of software function sign fx
Description of Excel's Evaluate Formula function

Purpose: to find where a formula in Excel contains an error, using the EVALUATE FORMULA function, step by step.

If you compile a formula in Excel which results in an error, it is useful to be able to unpick the formula step by step to see at which point the formula is breaking.

Excel has an inbuilt function — EVALUATE FORMULA — which enables you to do just that.

Where to find the EVALUATE FORMULA command on Excel’s ribbon

Evaluate Formula is found on the Formulas section of Excel’s ribbon.

Excel's ribbon showing the Formulas section
Evaluate Formula command in Excel's ribbon

TIP: add the command to the quick access toolbar on the ribbon — see our our short video and topic page on how to customize the ribbon in Excel.

Evaluate Formula worked example

For example, we’re compiling a two-way lookup formula using VLOOKUP and MATCH. The formula is resulting in an error, and we want to see where the error is occurring.

In the example below, we have made two attempts which result in different errors (#N/A and #NAME?), and a third attempt which is successful. We are looking up the value for Dimension3 for Nov-19 (=45).

Examples of VLOOKUP MATCH formulas showing error results.


  1. Click in the cell containing the formula
  2. Click on the ‘Evaluate Formula‘ command in the ribbon.
  3. The Evaluate Formula dialog box opens, displaying the formula.
Excel's Evaluate Formula dialog box displaying a formula
  1. Now click on the Evaluate button, and Excel takes you through the formula one step at a time. Each click of the button takes you to the next step. You will see where an error appears within the formula. The most recent step result will be in italics.
Evaluate Formula dialog box in Excel showing a formula with an error

In our example above, there is an error with finding the value “01/11/2019” in the range $B$2:$F$2. The value we’re looking for is November 2019 (entered as Nov-19) and Excel needs us to match the date format exactly.

In the correct formula below, when we use Evaluate Formula to inspect our VLOOKUP MATCH formula, Excel is finding the correct column (Nov-19 is column #3 in our range) to extract the value from.

Evaluate Formula in Excel showing a correct evaluation of the formula

TIP: With VLOOKUP the known value you are using to find the correct row of the target value HAS to be in the left-hand column of the range you are looking in (the ‘Dimensions’ column in our example below). Excel defines this as Column Number 1, and hence our target column for Nov-19 is Column Number 3.

Example of a VLOOKUP MATCH range and result