What is formula auditing in Excel? Definition and the audit tools

Microsoft's vocabulary for the two directions comes first, because every tool below is built on it. Precedent cells are cells "that are referred to by a formula in another cell"; dependent cells are cells that "contain formulas that refer to other cells". Auditing is walking that graph until the number on screen stops being a surprise — the problem GoSkills describes as receiving "a file with complex formulas" and being unable to tell "how the values are calculated".

The Formula Auditing group

Seven tools sit on the Formulas tab, in the Formula Auditing group.

Tracer arrows

Microsoft's instruction is the whole feature: "To display a tracer arrow to each cell that directly provides data to the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Precedents." Trace Dependents is the mirror image. The arrows carry one extra signal — "Blue arrows show cells with no errors. Red arrows show cells that cause errors" — and Excel-Easy notes that clicking repeatedly walks a further level back up the chain each time.

The behaviour to know before relying on them: "All tracer arrows disappear if you change the formula to which the arrows point, insert or delete columns or rows, or delete or move cells." Arrows are a reading device, not a record — trace, note what you learned, then edit.

Evaluate Formula

Where arrows map the graph between cells, Evaluate Formula opens one cell up: "you can see the different parts of a nested formula evaluated in the order the formula is calculated." Press Evaluate repeatedly and each underlined fragment is replaced by its value — shown in italics — until the expression collapses to the answer in the cell. Step In jumps sideways, displaying the formula behind a referenced cell in the same box.

Three documented limits explain most of the confusion around it. Step In "is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook". Branching functions are only partly walked: "Some parts of formulas that use the IF and CHOOSE functions are not evaluated, and #N/A is displayed in the Evaluation box" — the branch not taken, not a fault. And nine functions can make the dialog disagree with the grid outright, because they "are recalculated each time the worksheet changes": RAND, OFFSET, CELL, INDIRECT, NOW, TODAY, RANDBETWEEN, INFO and SUMIF.

Error Checking and the Watch Window

Error Checking walks the workbook naming the rule each flagged cell broke — GoSkills' example is a value that "was the wrong data type". What each error value means belongs to Formula errors; the Circular References submenu belongs to Circular references. The Watch Window is the least used and most useful tool on a multi-sheet model: it "allows you to monitor specific cells and their values without having to scroll to the location of that cell", so you can edit an assumption twelve tabs away while watching the outputs move.

Show Formulas first

Before any arrows, turn the sheet inside out. GoSkills describes the result: "all cells within the worksheet that contain formulas will now display those formulas instead of the result of the calculation." Excel-Easy gives the shortcut — Ctrl + ` , the backtick above the Tab key.

Start here because it exposes the defect no arrow will ever point at: a cell in a column of formulas that is not a formula at all, just a number somebody typed over the top during a deadline. In value view it looks like its neighbours; in formula view it is the only one without an equals sign.

Auditing a workbook you did not write

The tools are individually simple; the practice is the order you use them in.

Map the shape before the detail. Show Formulas across every sheet, looking for hardcoded values inside formula blocks, references reaching onto other tabs, and columns where the pattern breaks partway down. Then let the workbook self-report: run Error Checking and the Circular References submenu, and clear what they name before forming theories.

Work backwards from the outputs. Start at the cells whose numbers get quoted to other people and trace precedents outward, one click at a time, until you reach cells holding constants rather than formulas. Those inputs are the model's real assumptions, and they are usually undocumented. Any formula you cannot read at a glance goes through Evaluate Formula rather than through guesswork, with the Watch Window holding the outputs visible while you do it.

The Google Sheets side

Google does not present the same set as a ribbon group, and its documented equivalents are keyboard shortcuts rather than dialogs. The Sheets shortcut list names "Show all formulas" (Ctrl + ~), "Show/hide formula help (when entering a formula)" on Shift + F1, and "Toggle formula range selection (when entering a formula)" on F2 — the closest thing to following a formula's inputs on the grid.

The consequence is that auditing in Sheets leans much harder on reading the formula itself, which is why a formula written to be legible a month later is worth more there than the same formula is in Excel.

How Formula Foundry handles this

Start with what Formula Foundry is not. It has no equivalent of Trace Precedents, Trace Dependents or Evaluate Formula, and it never evaluates your workbook — there is no calculation engine behind it. Mapping the dependency graph stays a job for Excel's ribbon and, in Sheets, for reading the chain by hand.

What it changes is the other half of an audit: understanding a formula once you have found it. Click the cell and it opens in the sidebar across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted — a 300-character nested IF becomes a shape rather than a line. The visual builder renders the same formula as a form with every argument named, and the AI assistant explains it step by step in plain English. On inherited files, the editor also flags names from external workbooks as the opaque tokens they are, so a formula quietly depending on a file you do not have stops looking ordinary.

When the audit ends in a rewrite, the constants are usually the problem: a tax rate typed into eleven formulas, ten of which agree. Global @@variables lift those into one named value used as @@TaxRate, and the snippet library keeps the corrected pattern for the next person. The audit walkthrough runs the sequence on an inherited file.

FAQ