Auditing formulas

The Auditing group on the Formulas tab helps you understand and check a workbook’s formulas: where a result comes from, what it feeds, how it is worked out, and where the mistakes are.

Trace precedents

Precedents are the cells a formula reads.

1. Click the cell with the formula.
2. Click the up-arrow button in Formulas › Auditing (its tooltip is Trace precedents — what feeds cell**. Press again to go a level deeper.**).

Arrows are drawn on the grid from each precedent to the cell, and the cell is ringed. A range the formula reads, such as B2:B9, is drawn as one box with one arrow, not an arrow per cell. A precedent on another sheet is drawn as a short dashed arrow from a small square, in amber rather than blue.

Click the button again to go one level further back — the precedents of the precedents. Each level’s arrows are fainter than the one before. When there are no more levels, further clicks leave the arrows as they are.

The status bar sums up what was found, for example 5 precedents, across 2 levels, 1 on another sheet. If the trail leads back to the starting cell, it adds and the trail comes back to cell. If the cell reads no other cells, it says cell does not read any other cell.

Trace dependents

Dependents are the cells whose formulas read the current cell.

1. Click the cell.
2. Click the down-arrow button in Formulas › Auditing (Trace dependents — what reads cell**. Press again to go a level deeper.**).

Dependents are found on every sheet of the workbook; those on other sheets are drawn as off-sheet arrows. Click again to go a level deeper. If nothing reads the cell, the status bar says Nothing on this sheet reads cell.

Tracing from a different cell, or switching between precedents and dependents, starts a new trace.

Remove arrows

Click the eraser button in Formulas › Auditing (Remove the trace arrows). The status bar says Arrows removed. When there are no arrows, the button is unavailable (No arrows to remove).

Evaluate a formula

Evaluate Formula shows how a formula is worked out, one step at a time.

1. Click the cell with the formula.
2. Click Evaluate in Formulas › Auditing. It is unavailable when the cell holds no formula (… holds no formula to take apart).

The Evaluate dialog, titled with the cell’s address, lists every step at once. Each numbered step shows the formula as it stands, then the part being worked out and its result, such as B2*C2 → 240. The last line shows the Result.

IF, IFS, IFERROR, IFNA, CHOOSE and SWITCH are worked out only as far as the branch the formula takes, so the dialog never shows an error from a branch that isn’t used. Click Close when you are done.

Error checking

Error checking looks through every formula and value in the workbook for common mistakes.

1. Click Errors in Formulas › Auditing.
2. Read the list in the Error checking dialog. Each item shows the sheet and cell, the rule it breaks, and a sentence about what is wrong.
3. For an item:
   • Click Go to to close the dialog and select the cell.
   • Click Fix, where offered, to apply the suggested correction. Hover over Fix to see what it will write. The list is checked again afterwards, and the status bar says cell fixed.
4. Click Close.

The buttons along the top of the dialog turn each rule on or off; the list updates as you change them. Seven rules start on and two start off, as in Excel. With nothing found, the dialog says so.

Watch window

The Watch window is a panel that shows the values of chosen cells while you work elsewhere — on another sheet, or far down the same one.

Watch cells

1. Click the Watch window button in Formulas › Auditing (Watch window — keep an eye on cells while you work elsewhere). The Watch window panel opens on the right.
2. Select the cells to watch, on any sheet.
3. Click + at the top of the panel (Watch the selected cells).

Empty cells in the selection are skipped. The status bar says Watching followed by the number of cells, or explains that the cells are empty or already watched.

Each watched cell shows its sheet and address (and its defined name, if it has one), its current value — (blank) when empty — and its formula. Values update as the workbook recalculates.

Use the list