Lesson 2: Trace the Cause with Excel's Auditing Tools

Why Trace an Error?

In Level 1 you learned to read an error value. This level covers the next question: where the error starts. A formula that shows #VALUE! may not hold the fault itself. The cause is often a few cells upstream, and the error is passed along to every formula that uses it. Excel's auditing tools on the Formulas tab, in the Formula Auditing group, let you follow that chain one step at a time without guessing.

Set Up the Practice Table

DO: Type this into a blank sheet in Excel for Microsoft 365 on Windows.

In F1, enter: =IF(SUM(D2:D4)>50,"Over budget","OK")

F1 shows #VALUE!. The goal is to prove which cell causes it.

Task 1: Step Through the Formula with Evaluate Formula

Evaluate Formula calculates a formula in the same order Excel does, one part at a time.

1. Select F1. You can evaluate only one cell at a time.
2. Select Formulas > Evaluate Formula.
3. The part about to be calculated is underlined. Select Evaluate. The result appears in italics.
4. Keep selecting Evaluate until a step returns an error. Here, SUM(D2:D4) turns into #VALUE!. That is the first part that fails.
5. If the underlined part is a single reference to a cell that contains its own formula, select Step In to see that formula. Select Step Out to go back.
6. Select Restart to run it again, or Close to finish.

Points to know:

• Step In is not available the second time a reference appears in the formula, or for a reference to a cell in a different workbook.
• Some functions recalculate every time the sheet changes, so the dialog can show a result that differs from the cell. These include RAND, RANDBETWEEN, NOW, TODAY, OFFSET, INDIRECT, CELL and INFO, and in some cases SUMIF.

Task 2: Follow the Arrows with Trace Precedents and Trace Dependents

• A precedent is a cell that a formula refers to. B3 and C3 are precedents of D3.
• A dependent is a formula that refers to a cell. D3 is a dependent of C3.

1. Select F1 and select Formulas > Trace Precedents. An arrow points from D2:D4 to F1.
2. Select D3 and select Trace Precedents again. Arrows point from B3 and C3.
3. Read the colours. Blue arrows lead from cells without errors. Red arrows lead from cells that cause errors.
4. Select C3 and select Trace Dependents. This shows every formula that the bad price feeds, which is everything your fix will affect.
5. Select Remove Arrows when you have finished.

A black arrow pointing to a small worksheet icon means the link goes to another sheet or workbook. Excel can only trace into another workbook when that workbook is open.

Task 3: Keep Key Cells in View with the Watch Window

In a large workbook, the source cell and the result cell can be far apart. The Watch Window keeps both in view.

1. Select F1 and D3.
2. Select Formulas > Watch Window, then Add Watch > Add.
3. Fix the cause: change C3 to 4. In the Watch Window, D3 changes to 20 and F1 changes to "OK" while you work.

The Watch Window shows each cell's workbook, sheet, name, cell, value and formula. You can set one watch per cell. A watched cell that links to another workbook appears only while that workbook is open. To remove a watch, select it and select Delete Watch.

Task 4: Test Part of a Formula with F9

F9 calculates only the part of a formula you select.

1. Type ten back into C3. Select F1 and click in the formula bar.
2. Highlight exactly SUM(D2:D4) and press F9. That text changes to #VALUE!.
3. Press Esc. This is required. If you press Enter, the calculated result replaces that part of the formula for good. If you press Enter by mistake, press Ctrl+Z straight away.
4. Repeat in D3: highlight C3 and press F9. It shows "ten", which is text where Excel expected a number. Press Esc.

Task 5: Use Error Checking as a Guided Tour

1. Select Formulas > Error Checking. Excel goes to the first error on the sheet.
2. Choose an option:
   • Help on this Error opens help for that error.
   • Show Calculation Steps opens Evaluate Formula.
   • Ignore Error stops Excel flagging that cell.
   • Edit in Formula Bar lets you fix the formula.
3. Use Next and Previous to move between errors.

Cells that Excel flags have a small triangle in the top-left corner. Click the warning icon beside one to see the commands for that error.

Differences in Other Versions

• Excel 2021 and 2019 for Windows: these tools are in the same place on the Formulas tab.
• Excel for Mac: Evaluate Formula is not available. Use F9 on parts of the formula instead. For the Watch Window, open it first, then select the cells to watch.
• Excel for the web: Trace Precedents and Trace Dependents are not available. When an auditing tool is missing in the browser, use Open in Desktop App.

Key Takeaways

• The cell that shows an error is often not the cause. Trace it back to the source.
• Evaluate Formula shows the first step that fails. Step In opens a referenced formula.
• Red tracer arrows lead from cells that cause errors. Blue arrows lead from cells without errors.
• The Watch Window keeps distant cells in view while you fix the cause.
• After pressing F9 on part of a formula, always press Esc.
• Mac has no Evaluate Formula, and Excel for the web has no tracer arrows.