How to Find Where a Number Comes From in Excel

You are staring at a figure in a financial model and someone asks how it was calculated. The cell holds a formula that points to other cells, which point to others again. To explain where a number comes from in Excel you have to follow that chain back to the inputs that actually drive it.

What "where it comes from" really means

Every value in a model is either typed in directly or computed from other cells. The cells a formula reads are called its precedents. A precedent can itself be a formula with its own precedents, so the real source of a number is usually several layers deep.

Finding where a number comes from means walking that tree of precedents until you reach the cells that are pure inputs, the ones you would actually change in a sensitivity check.

Read the formula first

Start with the obvious. Select the cell and look at the formula bar to see exactly which references it uses.

1. Click the cell that holds the number you are investigating.
2. Read the formula in the formula bar and note each cell or range it references.
3. Press Ctrl+[ to jump straight to the precedents on the current sheet and see them selected.
4. Press F5 then Enter to jump back to where you started.

Trace precedents with arrows

Excel can draw arrows from a cell to the cells that feed it. This is the built-in way and it works without any add-in.

The arrows show one layer at a time. Click the button again to push the trace back another layer. The catch is that arrows pointing to other sheets show up as a small worksheet icon rather than a real reference, so cross-sheet sources are awkward to follow.

1. Select the cell.
2. On the Formulas tab, click Trace Precedents in the Formula Auditing group.
3. Click Trace Precedents again to expand the next layer back.
4. Use Remove Arrows to clear the diagram when you are done.

Walk the chain without the clutter

Arrows become a tangle the moment a cell has more than a handful of precedents, and they cannot follow a link onto another sheet by clicking. ModelMint Formula Trace walks the chain one layer at a time using the arrow keys, and the live reference appears in the formula bar as you move so you always know exactly which cell you are on.

Because it steps rather than draws, you can chase a single number back to its true input without ever leaving the keyboard or losing your place.

• Move back through precedents one layer at a time with the arrow keys.
• See the live cell reference in the formula bar at every step.
• Follow the chain across sheets instead of stopping at a worksheet icon.
• Return to your starting cell when the source is found.

Confirm you reached the input

You have found the source when the cell you land on contains a typed value rather than a formula. That is the lever the number ultimately depends on.

If the chain keeps branching, note each branch and trace the largest contributor first. In most models one or two inputs explain the bulk of any output.