trace error and step through formula in Excel

Q80. I have a cell with nested formulas that returns an error. In Excel, how can I step through the formulas to help with debugging?

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.

excel trace error and step through formula

To do it in Excel, here is the answer:

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

excel excel trace error and step through formula

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

excel excel excel trace error and step through formula

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

excel excel excel excel trace error and step through formula

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".

excel excel excel excel excel trace error and step through formula

 

You can find similar Excel Questions and Answer hereunder

1) How do I enter a formula in a cell using VBA (using Absolute Reference)?

2) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

3) How do I enter a formula in a cell using VBA (using Relative Reference)?

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

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

6) Formula converts date to quarter and year in Excel

7) how to write a formula in Excel

8) Formula does not calculate in Excel

9) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

10) Is there a way I can average a range of numbers even if there is an error value in range?

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

12) How can I calculate values applying complex Engineering formulas using Excel?

13) How can I loop through all WorkSheets and get their names using VBA?

14) How can I hide Formula Bar and Headings using VBA?

15) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?