|
|
|||
On this page ...
[The auditing toolbar] - [Finding links to other workbooks] - [Highlighting cells containing formulas] - [Comments in cells] - [Cell or range names]
Errors and design flaws are very common in spreadsheets and it is important to be aware of steps that can be taken to design more reliable and robust models. Excel includes some tools which can be used to investigate spreadsheets and clarify the processes and calculations being performed.
This toolbar contains nine tools to help interrogate cells and worksheets to find the order and flow of data through a model. To display the toolbar select Tools | Auditing | Show Audit toolbar.
[1] Trace Precedents - if the current cell contains a formula, arrows will be drawn leading back to the source cells.
[2] Remove precedents arrows
[3] Trace Dependents - arrows will be drawn from the current cell to any other cells that incorporate it into their formulas.
[4] Remove dependents arrows
[5] Remove all arrows
[6] Trace errors - if the current cell is displaying an error (e.g. #DIV/0!), an arrow will be drawn leading back to the cause of the error.
[7] Add a comment to the current cell (tool is also available directly from the Insert menu) - edit a comment which will only appear whenever the mouse cursor hovers over a particular cell. Cells containing a comment are marked with a small red triangle in the top right hand corner of a cell. To Edit or Delete a comment, select the option from the smart menu that appears when you click on the cell with the right mouse button. Comments can be used to describe the purpose of a formula or to note the source of piece of information.
[8] Circle invalid data - highlights any cells which fail Data Validation rules that have been defined for the current range. Although the validation rules prevent you from typing an illegal value into a cell, a formula may produce an exceptional value. For example, if all values in a range must be less than 50, a total of several other values may break the rule.
[9] Remove circles around data
Occasionally a spreadsheet opens and a dialog box informs the user that the 'workbook contains links to information in another workbook ... Do you wish to update ...'. This can be perplexing if you have no idea which cell in your model is linked. The menu option Edit | Links lists the source of any links, but not the cells making the call. To find it, select one or more worksheets and then choose Edit | Find. In the Find dialog box type .xls and then <Find Next>.
It may improve the clarity of your spreadsheet if all cells containing formulas are coloured in a particular way. Select Edit | Go to | Special and then tick formulas (or any other choice). All such cells on the current sheet will be selected. Then choose Cells | Formatting and add any font, border or patterns to the selected cells.
A spreadsheet can be made much easier to understand and audit by using named cells to define important and commonly used variables. Numbers representing values such as the inflation rate, the current period number or a tax rate can all be given names which are then used in formulas instead of a distant cell reference.
To name the current cell (or range) simply type a name into the 'Name Box' (located to the left of the formula bar). Cell names can also be added, modified and deleted from the menu option Insert | Name | Define. A name can then be used throughout your spreadsheet as an alternative to the cell reference. E.g. = SUM(F20:F40) * My_Int_Rate.
[Tips on design and a tool for checking spreadsheets ...]
| Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about obtaining this file |
| file: excel-audit.htm | © meadinkent.co.uk 2006 | Last updated Aug06 |  |
