Excel Made Easy

Trace error and step through formula in Excel

For example, in the Table below, I get "#N/A" result in 2 of the fields that I do not expect. The cells have nested formulas (Index and Match are used). In Excel, how do I step through the formula so that I can check and verify the result of each formula as part of debugging the error.

To do it in Excel, here is the answer:

a) Click on "Evaluate Formula" under "Formulas".

b) The "Evaluate Formula" dialog box pops up. Click on "Evaluate".

c) The look up value in "MATCH" formula is updated with I3 cell content. Click on "Evaluate" again.

d) "MATCH" formula is executed based on arguments provided and the results are returned. As can be seen from the Screenshot below, the Match formula returns #N/A indicating no match was found. Click on "Close".

Comparing the lookup value in I3 with the corresponding value in the table in D11, it is clear that the value in I3 is misspelt. Fix the incorrect value in "I3".

You can find similar Excel Questions and Answer hereunder

1) How to see to which cells a cell is connected or used by. How to see the dependents of a cell

2) How to trace errors in Excel. How to find where an error comes from in Excel

3) What are the different type of errors in Excel VBA, subscript out of range, compile error, time out, run time overflow

4) How do i apply a formula to an entire column in Excel

5) One of my formulas has returned an error - how can I trace the error to fix it?

6) You can find here the main error codes that happens in Excel VBA

7) How to see to which cells a cell is connected or used by. How to see the precedents of a cell

8) Applying a countif formula only to visible cells in a filtered list in Excel

9) How to handle errors in VBA with the handling error methods

10) Formula does not calculate in Excel