Excel 2010: Auditing Tools

There are times when you inherit a spreadsheet and wonder how it works. You can identify the cells with formulae, but still have difficulty tracing the sources or subsequent calculations. This is where Excel’s Auditing Tools can help point you in the right direction (literally).

Tracing Precedents and Dependents

Select a cell with a formula. To find the cells that feed into your chosen calculation, click on the Formulas tab and choose Trace Precedents. The blue arrows or marked areas indicate which cells are used in the calculation of this formula. Choose Trace Dependents to indicate which cells use this cell in further calculations.

Blue arrows on a spreadsheetIt is worth knowing that these are multiple-level tools so you can take the calculations to the next stage and beyond. For example, if the original data cell is used in a calculation, Trace Dependents will point the cell out. If this is used in a further calculation, click Trace Dependents again (you don’t need to move from that cell) to see the next calculation, and so on. Just keep clicking until you reach the end of the route.

For 3-D spreadsheets, a sheet icon will appear if the precedents are coming from a number of other sheets. Double click the arrow to open the sheet list where you can select a particular location to view further (double-click).

3D spreadsheets list