Auditing & Troubleshooting Formulas

Eight chapters of building increasingly ambitious formulas, automations, and data models raises an obvious question: what happens when one of them breaks, or worse, silently produces the wrong answer with no error at all? This chapter covers Excel's own error values and its built-in auditing tools — the ones that turn "something's wrong somewhere in this workbook" into "here, specifically, is what's wrong."

Excel's Error Values: What Each One Actually Means

Every error value this course has encountered along the way, gathered in one place:

Trace Precedents & Trace Dependents

Formulas → Trace Precedents draws arrows from the selected cell back to every cell that feeds into it. Formulas → Trace Dependents draws arrows forward, showing every other formula that relies on the selected cell. On a workbook the size of Excel Fundamentals Chapter 10's Budget Tracker — or this course's own Data Model relationships (Chapter 5) — these arrows turn "which cells actually affect this number" from a manual hunt into a single click.

Error Checking & the Watch Window

Formulas → Error Checking walks through every cell Excel has flagged as containing a likely error, one at a time, with a plain-language explanation and the option to trace its precedents directly from the same dialog. The Watch Window (Formulas → Watch Window → Add Watch) keeps a specific cell's current value visible in a small floating panel, even while you're actively editing a completely different worksheet — useful for confirming a distant total updates the way you expect while you work on the data feeding it.

Evaluate Formula: Stepping Through a Calculation One Piece at a Time

Formulas → Evaluate Formula steps through a nested formula one function call at a time, showing the actual underlying value at each stage before moving to the next. This is precisely the "read from the innermost parentheses outward" technique this course has used informally since Excel Fundamentals Chapter 4's nested IF and Excel Advanced Chapter 2's nested XLOOKUP — except now it's an actual built-in tool doing the tracing for you, rather than working it out by hand on paper.

IFERROR and IFNA: Catching Errors Gracefully

IFERROR wraps any formula, catching whatever error it produces and substituting a fallback value instead:

IFNA is narrower — it only catches #N/A specifically, letting every other error type through unchanged:

That distinction matters more than it looks: a broad IFERROR around a lookup would just as happily swallow a genuine typo in the range address (a real #REF!, meaning something is actually broken) as it would a legitimate "no match found," displaying the same harmless-looking fallback value either way — with no way to tell, just by looking at the result, which situation actually occurred.

Circular References: When a Formula (Indirectly) Refers to Itself

A circular reference occurs when a cell's formula depends — directly or through a chain of other cells — on its own value. Excel shows a warning dialog the moment one is created, and the cell typically displays 0 or the last successfully calculated value instead of a real result. The single most common accidental cause: summing a range that includes the very cell the total is being written into.

Formulas → Error Checking → Circular References lists every cell currently involved in a circular chain, which is genuinely necessary once the loop passes through several cells rather than a single obvious one like the example above.

Hands-On Exercises