Auditing

Microsoft Excel is a potent spreadsheet application that can be used for many business-related functions. However, file sizes may become very large and there might be several users editing the same spreadsheets. Consequently, flaws and errors are common in spreadsheets. Several tools are available for auditing spreadsheets.

The Excel Audit Toolbar contains nine tools, which can be used to interrogate cells (and worksheets) to determine the appropriate flow and order throughout your model. If you want this toolbar to display permanently, select Tools – Auditing – Show Audit Toolbar. Here are the nine auditing tools:

Remove all precedents arrows
Remove all dependents arrows
Remove all arrows
Trace precedents (arrows will be drawn that lead back to the source cells if the current cell contains a formula)
Trace dependents (arrows will be drawn from the current cell to any other cells in which formulas it is incorporated)
Remove all circles around data
Circle all invalid data (data validation rules are applied for the current range and all cells that fail this validation are circled. A formula might still produce an exceptional value, even if the validation rules may prevent you from typing illegal values into cells)
Add comments to a current cell (this comment will only display when the mouse pointer hovers over a particular cell. A small, red triangle in the top right-hand corner identifies cells that contain comments. Right-click on the cell to bring up a context-sensitive menu that would allow you to edit or delete the comment. Acknowledgement of an information source or a formula description could be contained in a comment). This tool can also be accessed directly from the Insert menu.

These auditing tools will help you “clean up” the data in a spreadsheet so that they are free from errors.