3. Dependency Auditing and Check Totals

Someone else's workbook almost always contains errors you can't see from the results. Here's how to find them:

• Trace Precedents and Trace Dependents (Formulas tab) draw arrows showing where a formula gets its data and who uses its result. A total with no arrow coming from the last row is suspicious;
• Show Formulas (Ctrl+`): in a column of formulas, a cell holding a typed number instead of a formula stands out right away;
• Evaluate Formula shows the calculation step by step;
• numbers stored as text are marked with a green triangle. SUM and SUMPRODUCT skip such text;
• a check total is the same total calculated another way. A cell like =IF(ABS(total1-total2)<0.01,"OK","Error") tells you when the model has drifted apart. The 0.01 tolerance is there because of fractional tails.

The sheet holds a six-month report with three errors hidden in it: the March price is stored as text, May's revenue is typed in as a number, and the quantity total doesn't cover the whole range.