Established onExcel 365 and 2016 and later: arrows, evaluate, watch panelGoogle Sheets, web: formula view onlyBoth: a pasted value has nothing to trace
The first question about any figure is where it came from. Both products can answer it and they answer it very differently.
See every formula at once
Both have a key combination that switches the sheet from showing values to showing formulas. Columns widen, every cell displays its expression, and the shape of the calculation becomes visible in one glance.
This is the cheapest thing in this entire part. It reveals an inconsistent formula in the middle of a column, a typed constant among calculations, and a range that stops short, without any tool at all.
Follow the arrows
Excel draws arrows from a selected cell to the cells it depends on, and onwards from those, one level per press. The reverse direction shows what depends on the selected cell, which is the question to ask before changing anything.
References to other sheets appear as an icon rather than an arrow, and references to other files cannot be followed at all. Google Sheets has no equivalent feature, which is a genuine gap and the main reason auditing a large model is harder there.
Step through the expression
Excel can evaluate a formula one piece at a time, showing what each sub-expression produces before substituting it. For a nested formula this is the difference between knowing that the answer is wrong and knowing which part is wrong.
There is no equivalent in the other product, which for complicated formulas is a substantial difference.
The trick with a trap
Selecting part of a formula in the formula bar and pressing the calculate key replaces the selection with its value on screen, which is an instant way to see what a fragment produces.
Pressing return afterwards writes that value into the cell permanently, destroying the formula. The escape key is what leaves the cell unchanged. This is a genuinely useful technique and it has ruined a number of models.
What breaks tracing entirely
A reference built from text, described in the second part of this site, creates no dependency for the tools to follow. So does a value that arrived by pasting, which has no precedents because it is not a formula and looks exactly like one that has.
Both are invisible to every technique above, which is the argument for the arrangement in the fourth part: if typed values live in one region, the untraceable things are all in a known place.
The order to work in
Formula view first, because it is one keystroke and shows the most. Then dependents, before changing anything, to find out what a change would affect. Then precedents from the figure in question, to walk back towards the inputs. Then step through the specific formula that turns out to be wrong.
Most audits end at the second step, because the answer is usually a constant somebody typed.
What concentrated formulas cost here
The naming and array features described in the second part let one cell do the work of several, which is better to read and worse to trace: the intermediate values no longer exist in cells that can be inspected.
For a model that will be audited, that is an argument for keeping the steps in cells even where they could be combined. It is a real trade and it should be made deliberately rather than by preference.
The watch list
Excel can display a small panel showing the current value of chosen cells regardless of where you are in the workbook. Putting the check cells from the next entry into it means their state is visible while working anywhere in the file.
What we cannot verify
The formula view, the tracing arrows, the evaluation dialogue, the fragment evaluation and the watch panel are documented by the vendor and were confirmed on a current build. The absence of tracing arrows and step-through evaluation in Google Sheets was established by testing rather than from documentation stating the absence.
Tracing across sheets
Where a precedent is on another sheet, Excel draws a dashed line to a small icon rather than to the cell, and double-clicking that line opens a list of the external references, from which the cell can be reached.
It is two steps rather than one and it works. The equivalent for references into other files does not: those cannot be followed while the other file is closed, which is one more reason the previous part recommends importing over linking.
The audit that finds most things
Formula view, then look down each calculated column for a cell that reads differently from its neighbours. Two minutes, no tools, and it finds the typed-over formula and the short range, which between them account for most of what goes wrong.
Before you change anything
Look at dependents first, not precedents. The question that matters before an edit is what else moves, and it is the one people ask afterwards.
Where the audit should start
Not at the figure somebody is asking about. At the list of typed cells in the calculated regions, which the fourth part explains how to produce in one dialogue, because that list is where the answer usually is and it takes ten seconds to obtain.
In short
- One key combination shows every formula at once and is the cheapest audit available.
- Tracing arrows show precedents and dependents, one level per press, in one product only.
- Stepping through an expression shows which part is wrong rather than that it is.
- Evaluating a fragment in the formula bar is useful and writes the value in if you press return.
- A reference built from text and a pasted value both create nothing to trace.
- Concentrated formulas read better and remove the intermediate values an auditor would inspect.