Tracing & Flow Diagram

Three commands in one Formula Explorer pane: trace what feeds a cell, trace what depends on it, and visualize a single formula as an interactive node graph. They navigate and highlight cells across every sheet in the workbook, but never change your data.

All three open the Formula Explorer task pane (one per Excel window). Precedents and Dependents populate the References tab; Flow Diagram switches to the Flow Diagram tab. Switching commands re-uses the same pane. None of the three change formulas or values.

What they write — and the single-workbook truth

To orient you on the grid, the pane paints the source cell light blue and each cell you click green (via cell fill). Those original fills are recorded and restored when the pane closes or you navigate away — but the paint goes straight through Excel’s formatting, so Ctrl+Z will not undo a highlight. On a protected sheet the highlight is silently skipped (the tree still builds). No formulas or values are ever written.

Tracing is single-workbook. Cross-sheet references in the active workbook are traced; references to other workbooks show as leaf nodes tagged external and cannot be expanded or focused (“External workbook references cannot be focused in this workbook diagram”). The ribbon tooltip’s “across sheets and workbooks” overstates this — and for Dependents it is inaccurate: the index only reads the active workbook, so a cell in another workbook that references yours is not discovered.

Trace Precedents

What it does

Builds a recursive upstream tree of every cell that feeds the selected formula. It first asks Excel for direct precedents (the native API) and falls back to parsing the formula text for ranges it can’t resolve that way. The References tab shows four collapsible sections: References (the direct addresses parsed from the formula), Formula Breakdown (each function/operator as a sub-row), Analytics (risk flags such as volatility), and Precedents (the recursive chain, summarized as “N precedents · N sheets · N cross-sheet” with a proof rollup when available).

What you see & how nodes behave

Each tree row shows the cell address, a type badge (Input, Named Range, External, Circular, …), its current value, and its location. Rows with further children show an expand chevron. The tree pre-loads the first couple of levels; deeper levels lazy-load on demand when you expand a row (no full rebuild), down to a maximum depth of 5. Click a row to jump Excel’s selection to that cell and green- highlight it. A Drill / List toggle switches between the nested function structure (Drill) and a flat, de-duplicated list of direct precedents (List).

Workflow

  1. Select a formula cell (Precedents requires a formula).
  2. Formula Tools → Precedents. The pane opens on References; the source cell turns blue.
  3. Read the Precedents section; click any row to navigate + highlight it.
  4. Expand a row (chevron, or Right-arrow / Enter) to trace one level deeper; keep going to reach raw inputs.
  5. Branches stop expanding at terminal inputs, external refs, or depth 5.

Troubleshooting

“The selected cell does not contain a formula.” — select a formula cell. External rows won’t expand — by design (other workbooks aren’t read). Highlighting disabled — sheet is protected — the tree still builds; only the green highlight is suppressed. Deep trees on large models take a moment (a “Processing…” status shows during the scan).

Trace Dependents

What it does

Builds the downstream tree — every formula cell that depends on the selected cell (its blast radius). Unlike Precedents, the selected cell need not contain a formula (you can trace what uses a hardcoded input). Discovery is two-phase:

  • Phase 1 — same-sheet, instant. Excel’s direct-dependents API returns same-sheet dependents immediately. If that call fails on cross-sheet links, a native dependent-arrow fallback is used (capped at 1000 arrows).
  • Phase 2 — cross-sheet, indexed. A reverse-dependency index of the whole workbook is built (scanning every formula cell once) to add cross-sheet and named-range dependents, shown as they resolve. The index is cached per workbook, so subsequent dependent traces are near-instant; it is rebuilt when sheets change or the named-range set changes. Changing your selection cancels an in-flight build and starts fresh for the new cell.

Adjacent dependents collapse into range-group rows you can expand to individual cells. The section header reads “Direct dependents (N outputs · N sheets · N cross-sheet)”. Lazy expansion goes to depth 5.

Workflow

  1. Select any cell (formula or hardcoded input).
  2. Formula Tools → Dependents. On the first run for a workbook the cross-sheet index builds (a brief wait); later runs are fast from cache.
  3. Click a dependent to navigate + highlight it; expand to walk the chain down to outputs.

Troubleshooting

“No formulas refer to this cell.” — the cell is an output or unused. First run is slow — the cold cross-sheet index build; subsequent runs hit the cache. A few dependents missing on a huge model — the native arrow fallback caps at 1000; the indexed phase covers the rest within the workbook.

Investigation controls: the action strips

The Formula Explorer is a model-audit cockpit, not a static tree. Two action strips turn tracing into a guided investigation — a header strip that acts on the whole outline, and a selected-row strip that acts on the row you click. They serve both Precedents and Dependents (the selected-row strip adapts to the mode).

The header strip

  • Trace Inputs — walk the selected branch all the way until it reaches terminal inputs, names, ranges, or the depth limit. (In Dependents mode the same button traces downstream outputs.)
  • Expand — load the next direct level for the selected row.
  • Focus / Show All — isolate a single lineage branch (hide everything else); toggle again to return to the full outline.
  • Collapse — collapse the selected branch.
  • Copy — copy the visible outline rows as a table (for review notes).
  • Stop — cancel a long trace mid-run.
  • Search chain — a filter box (cue text “Search chain”) that narrows the visible lineage by element, value, location, formula, or risk label.
  • Duplicates (Precedents mode) — show repeated precedent occurrences as separate rows. The same toggle becomes Group by sheet in Dependents mode, grouping downstream outputs by worksheet.

The selected-row strip — Precedents

Select a precedent row and a detail strip shows its address, value, and formula, with:

  • Explain Use — show how the source formula consumes this precedent.
  • Show Uses — show the formulas that use this precedent, inline.
  • Trace Upstream — continue from this precedent toward its own inputs.
  • Open in TrustLens — open the selected precedent in TrustLens.
  • Open Detail Pane — open it in a second docked pane for side-by-side review.
  • More — an overflow menu (Open in TrustLens / Open Detail Pane) that appears when the strip is too narrow to show every button.

The selected-row strip — Dependents

  • Explain Formula — show this dependent formula’s breakdown inline.
  • Trace Downstream — continue from this dependent toward downstream outputs.
  • Open in TrustLens — open the dependent formula in TrustLens.
  • Open Detail Pane — open it in a second docked pane for side-by-side review.

An investigation workflow

  1. Start with the whole tree (Precedents or Dependents).
  2. Search the chain to find a target row (by value, location, formula, or risk).
  3. Focus that one branch to cut the noise.
  4. Expand a level, or Trace Inputs / Trace Downstream to go deep.
  5. When a row matters, use Explain Use / Show Uses (precedents) or Explain Formula (dependents), Open in TrustLens, or Open Detail Pane for side-by-side review.
  6. Copy the visible investigation table into your review notes.

Flow Diagram

What it does

Renders the selected cell’s formula as an interactive node graph — the formula is parsed into function nodes, operator nodes, argument nodes, and cell/ constant references, laid out as a tree. It is a single-formula structural view (not a multi-cell trace). The cell must contain a formula.

What you see & controls

A compact formula viewer with reference hyperlinks sits above a white canvas. Function nodes are color-coded by family (logical blue, lookup purple, math teal, text orange, date amber, financial green, …) with the function name, a short description, argument sub-rows, and a result badge. A toolbar offers Drill / List, Prune (hide the branches an IF/logical didn’t take — only the active path shows), Copy, Save, and a TrustLens button. Pan by dragging the background; zoom with the slider or mouse wheel (10%–200%); Fit-to-view frames everything. Active-path nodes get a green accent and a “PATH” badge; pruned/inactive nodes are dimmed with an “OFF” badge.

Drill, List, and Prune

The diagram toolbar has three view controls — pick the one that fits the question:

  • Drill (the default) — the nested function/formula structure. Best for understanding nested formulas, function structure, and the calculation flow.
  • List — starts flat, then lets you lazily expand formula precedents one level at a time (see below). Best for following a dependency chain step-by-step while keeping the diagram less crowded than Drill.
  • Prune — an active-path-only view that hides the branches an IF/logical formula did not take. Best for IF/logical formulas where you want to focus on the branch/path that actually fired (a hint reads “Prune: active path only” / “Prune: showing all branches”).

Lazy tracing in List view. List doesn’t stop at the direct precedents. Each formula precedent node carries a small plus/minus expand control; click it (or double-click the node) to expand that node — the add-in parses that precedent cell’s formula and adds its precedents to the diagram on demand (the diagram re-lays-out around the new nodes rather than re-tracing the whole formula). Collapsing (the minus control) removes the lazily-added children. So you can walk a dependency chain one cell at a time and stop wherever you like — a less crowded way to follow a chain than Drill’s fully-nested structure.

Limits: only formula-like nodes expand — a constant, a plain value, or a range does not — and circular expansion is guarded (a node that would loop back onto an ancestor shows “Circular reference — cannot expand” instead). The expand control is a List-mode feature; Prune still applies to Drill mode only.

Prune works in Drill mode only

Prune applies only to the nested Drill view. Switching to List mode disables Prune and resets it — if a pruned view was active, the diagram returns to showing all branches.

Workflow & shortcuts

  1. Select a formula cell and press Ctrl+Shift+W (or Formula Tools → Flow Diagram).
  2. Read the graph top-down: root is the formula cell; leaves are references/constants.
  3. Click the canvas to focus it, then navigate nodes by keyboard: arrows move between parent/child/siblings, Space collapses/expands a node, Enter jumps to the node’s cell, Home/End go to root/last node.
  4. Toggle Prune to focus on the branch that actually fired; toggle Drill/List to switch between nested structure and a flat precedent list.
  5. Copy puts the diagram image on the clipboard; Save exports a PNG/JPEG/BMP file (clamped to 8000×8000 px). Click TrustLens to open the full workbench on the same cell.

Troubleshooting

“The selected cell does not contain a formula.” — Flow Diagram needs a formula. Crowded diagram — use Prune, switch to List, or zoom out. Keyboard nav unresponsive — click the canvas first to focus it. Exported image looks cropped — zoom out before exporting very large diagrams.

Related

  • TrustLens — a deeper single-formula workbench (proof, references, editor) reachable from the diagram’s TrustLens button.
  • CalcScope — step-by-step evaluation with live values and a plain-English explanation.