Tuesday, 12 June 2018

Excel - Auditing a Worksheet

Trace Precedents & Dependants.

The formula auditing tools can be found on the Formulas tab on the Ribbon.
Select Formulas Tab from the Ribbon.

Formula auditing uses the terminology Precedents and dependents. Precedents are the cells that provide data to a specific cell and Dependents are the cells that depend on or are affected by the value of a specific cell.

Select the cells that contains the formula for which you want to find the precedent or dependant cells and click either the Trace Precedents or Trace Dependants buttons to display the tracer arrows. One click shows the first level of precedents or dependents; keep clicking to trace through the original or terminus of the calculation.

Blue Arrows: are displayed to show all the cell relationships.

Red Arrows: are used if a cell contains an error value.

Black Arrows: pointing to a worksheet icon is displayed, if the cell reference is on another worksheet. Double click on the black arrow to display the GoTo box that will display the linked worksheets.

The other workbook must be open to continue the trace. Click on the Remove Arrows to remove all Auditing arrows.

Watch Window.

When working with large or complex worksheets, where the result of the formula is dependent on the results of another formula in another cell, you may want to monitor
cell values. 

To do so, you can use the Watch Window. To add a cell to the Watch Window, click Watch Window

from the Formula tab in the Formula Auditing group. The Watch Window is displayed. Click and drag to select the cells you require to watch and then click Add.