The Detective
In a long or complicated spreadsheet, color coding becomes less useful. In these cases, consider using the the submenu under Tools > Detective. The Detective is a tool for checking which cells are used as arguments by a formula (precedents) and which other formulas it is nested in (dependents), and tracking errors. It can also be used for tracing errors, marking invalid data (that is, information in cells that is not in the proper format for a function's argument), or even for removing precents and dependents.
To use the Detective, selective a cell with a formula, then start the Detective. On the spreadsheet, you will see lines ending in circles to indicate precedents, and lines ending in arrows for dependents. The lines show the flow of information.
Use the Detective to assist in following the precedents referred to in a formula in a cell. By tracing these precedents, you frequently can find the source of the errors. Place the cursor in the cell in question and then choose Tools > Detective > Trace Precedents from the menu bar or press Shift+F7. The figure below shows a simple example of tracing precedents.
Cursor placed in cell
|
a) Initiate trace by clicking Trace Precedents
|
b) Source area highlighted in Blue, with arrow pointing to the calculation cell
|
Tracing precedents using the Detective.
We are concerned that the number shown in Cell C3 is incorrectly stated. Many times the solution to the concern can be seen visually as in our simple example. In this case cell C16 contains both numeric data as well as letters. Removing the letters resolves the problem in the calculation.
In other cases we must trace the error. Use the Trace Error function, found under Tools > Detective > Trace Error, to find the cells that cause the error.