Tracing precedents and dependents to audit formulas in Grid

When a total looks wrong, the question is always the same: which cells is it really using? In a sheet of any size, reading formulas one at a time to find out is slow and error-prone. Grid by Zaweba has auditing tools that show the answer on the grid itself.

Arrows to the cells a formula uses

Trace precedents draws arrows from the cells a formula depends on to the formula cell. Select a total, trace its precedents, and you see straight away whether it covers the whole column or stops a few rows short, whether it picks up a stray heading, or whether it points at last year's rate instead of this year's.

Trace dependents works the other way. Select an input cell and the arrows show every formula that uses it. Before deleting a row, changing a rate or moving a block of data, tracing dependents tells you what would be affected. A cell with no dependents can be changed without worry; one with arrows going everywhere deserves care.

Following a summary's precedents back through a workings sheet to the raw data, a cell at a time, is a good way to understand a workbook you've inherited.

Error checking

Error checking points out cells with problems, so you don't have to hunt for them. Formula errors are the obvious ones, but a sheet can have issues that don't show as error values, and a quick check before sending a workbook on is good practice.

When a formula shows an error value, it's usually telling you something specific. A reference error means a cell it relied on was deleted. A name error usually means a typo in a function or a name that doesn't exist. A division by zero means an empty or zero divisor. Tracing precedents on the error cell shows which input is to blame.

Grid also watches for circular references, where a formula depends on itself through a chain of other cells. The status bar shows a circular-reference warning when one exists, so you notice it straight away rather than wondering why a figure won't settle.

Show Formulas

Show Formulas swaps every cell's value for its formula, across the whole sheet. It's the quickest way to spot inconsistencies: a column of formulas where one row has been typed over with a number stands out immediately. It's also handy for printing a record of how a sheet works. Switch it off again to return to values.

An auditing routine

Before relying on an important workbook, a short routine catches most problems. Show Formulas and scan each column for odd ones out. Trace precedents on every key total to check its range. Run error checking. Check the status bar for a circular-reference warning. Then switch back and compare a couple of totals with figures you can work out by hand.

These tools don't change anything in the sheet; they just show you what's there. In Grid by Zaweba they're the difference between hoping a spreadsheet is right and knowing it is.