Microsoft Excel · Calculation tools, not function references · Intermediate
Trace and evaluate a formula to locate an incorrect dependency in Excel
Trace and evaluate a formula to locate an incorrect dependency in Excel with a complete practice example, expected results and recovery checks.
Official documentation reviewed; example logic independently checked; native Excel execution not performed · Editorial standards
Quick answer: Use Trace Precedents to find the cells feeding a suspicious formula, then Evaluate Formula to inspect its intermediate calculation. An arrow proves a reference exists; it does not prove that the referenced cell is the correct business input.
Version and platform: Microsoft 365 Excel for Windows desktop; Mac, Excel for the web and Excel 2013 interfaces not covered. Use a disposable workbook for the exercise.
Build a formula with a plausible mistake
In a new worksheet, enter the following cell contents:
| Cell | Content |
|---|---|
| A1 | Quantity |
| B1 | 4 |
| A2 | Unit price |
| B2 | 12 |
| A3 | Discount rate |
| B3 | 0.10 |
| A4 | Old discount rate |
| B4 | 0.05 |
| A6 | Subtotal |
| B6 | =B1*B2 |
| A7 | Discounted total |
| B7 | =IF(B6>40,B6*(1-B4),B6) |
The intended rule is a 10% discount when the subtotal exceeds 40. B6 correctly returns 48, but B7 returns 45.6 because it uses the old 5% rate in B4. The correct answer is 43.2. The formula is syntactically valid, so an error code will not warn you about the wrong dependency.
Trace the incoming and outgoing relationships
Select B7 and choose Formulas → Trace Precedents in Formula Auditing. Its direct references include B6 and B4, exposing the obsolete rate. Choose Trace Precedents again to inspect the next level feeding B6: B1 and B2. Compare the arrows with the labels, not just the cell addresses.
Select B3 and use Trace Dependents. In this small fixture it should not reveal B7 as a user of the current rate, which supports the diagnosis. Remove the arrows with Formulas → Remove Arrows when you want to start a clean inspection. If arrows are not visible, check the workbook’s object-display setting described in Microsoft’s tracing guidance; do not treat a missing drawing as proof that the formula has no inputs.
Evaluate the incorrect formula
Select B7 alone and choose Formulas → Evaluate Formula. Use Evaluate to step through the underlined expression. The logic is:
- B6 supplies 48, so 48 > 40 is TRUE.
- B4 supplies 0.05, so the discount factor is 0.95.
- 48 × 0.95 gives 45.6.
When an underlined reference points to another formula, Step In can inspect that formula and Step Out returns to the previous level. Only one cell is evaluated at a time. Close the dialog before editing B7.
Repair the dependency and test the boundary
Change B7 to =IF(B6>40,B6*(1-B3),B6). Expect 43.2. Trace again to confirm the direct rate input is now B3, and evaluate again to see the 0.9 discount factor. Editing a formula can clear tracer arrows, so redraw them rather than assuming they persist after a repair.
Now change B1 from 4 to 3. B6 becomes 36, and B7 should return 36 because the discount threshold is not met. Change B1 to 5 and B2 to 8: B6 is exactly 40, so B7 should be 40, not 36. Equality does not satisfy the strictly-greater-than test. These cases check the decision rule as well as the corrected rate address.
Save as XLSX and reopen; check the final 40 result and the reference to B3. Do not expect tracing tools to reveal every possible dependency: Microsoft lists limitations for PivotTables, named constants, objects and closed external workbooks. Volatile functions can also make evaluation differ from a previously displayed result. This controlled fixture avoids those complications so the mistaken input remains unambiguous.
Official references
Documentation snapshots retrieved 20 October 2026. These instructions and example checks are documentation-based; no native Excel run or application screenshot is claimed.