| Feedback | Spreadsheet design tips | |

On this page ...

[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.

A section of the Formulas ribbon contains tools to help interrogate cells and worksheets to find the order and flow of data through a model.

__Trace Precedents__ - if the current cell contains a formula, arrows will be
drawn leading back to the source cells.

__Trace Dependents__ - arrows will be drawn from the current cell to any
other cells that incorporate it into their formulas.

__Remove Arrows__ - either remove all arrows or just those associated with
precedents or dependents.

__Show Formulas__ - displays all formulas in the current worksheet.

__Error Checking__ - contains 3 options. *Error Checking* will identify
any cells which contain errors such as #VALUE! and help you fix the
formulas. If you select a cell containing an error, *Trace Errors* will
display arrows to precedents. The *Circular References* option
will list any cells containing formulas which use a range that includes that
same cell.

__Evaluate formula__ - a dialog box helps you analyse the parts of a complex
formula.

Other tools which were included on the Auditing toolbar in previous versions of Excel are now to be found in different ribbon locations.

Adding a comment to the current cell ([Review] Comments | Insert Comment) lets you describe the purpose of a formula or to note the source of piece of information. A comment will only appear when the mouse cursor hovers over a particular cell and 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.

Data validation - Rules can be defined which specify appropriate values for a
range of cells ([Data] Data Tools | Data Validation).
Once these have been set, users will be prevented from entering inappropriate
values. The ribbon option to ** Circle Invalid Data** will highlight any
existing cell values 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.

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 ribbon option [Data] Connections |
Connections lists the
source of any links. Another way of finding an external link is to select one
or more worksheets and then choose [Home] editing | Find
and Replace. 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 [Home] editing | Find and Replace | Formulas. All such cells on the current sheet will be selected. Then choose a fomatting option to 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 ribbon section [Formulas] Defined
Names.
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. It has been rewritten for Excel 2010. |

file: excel-audit.htm | © meadinkent.co.uk 2016 | Last updated Feb14 | CMIDX S7 P1 Y |