Challenge 3: Diagnosing a Circular Reference in a Total Cell — Solution Walkthrough

Most likely cause:

B11 was intended to total B1:B10, but was accidentally written to
include its own row as well:

  =SUM(B1:B11)

Since B11 is the cell holding this very formula, the formula now
depends on its own current value to calculate its own current value
— a direct circular reference, exactly the "range accidentally
includes its own total cell" scenario this chapter describes as the
single most common accidental cause.

Corrected formula:

  =SUM(B1:B10)

Excluding B11 itself from the range it sums resolves the circular
dependency completely — B11 now depends only on B1 through B10, none
of which depend on B11 in return.

Which auditing tool would have caught this immediately, before Excel's own warning:

Trace Precedents, run on cell B11 BEFORE ever entering or confirming
the formula (or immediately after, before dismissing the circular-
reference warning), would draw an arrow from B11 back to the range it
depends on — and that arrow would visibly point INCLUDING B11 itself,
immediately revealing that B11 is listed as one of its own inputs.
Seeing a precedent arrow loop back to the very cell you're
inspecting is an immediate, visual giveaway of exactly this mistake,
independent of whether Excel's own circular-reference dialog has
already appeared.

Notes:
- For a genuinely tangled circular reference spanning several cells
  (not just one obvious self-inclusion like this example), Formulas >
  Error Checking > Circular References is the more reliable tool,
  since it lists every cell involved in the loop directly rather than
  requiring Trace Precedents to be run repeatedly across several
  cells by hand to piece the whole chain together.