How to check for and reduce errors in Microsoft Excel

Sat 14th October 2006

Important decisions can be based on data output from a spreadsheet. For example, an accounting spreadsheet not only describes a company's financial history but also forecasts its future. A tiny error that initially appears insignificant can easily grow to give a totally incorrect financial picture. For this reason just one tiny error in one cell can spell disaster.

There are ways to detect and correct most errors but no foolproof system exists. The prudent user should stay alert to this danger and undertake some excel software training to be able to use all available tools to detect them. Six ways to check for errors and minimise the chance of them being generated are given below.

The validation tool
The Validation option in the Data menu makes it possible to perform input range checks without having to write sophisticated software code. You have options to select the data type (e.g. whole number, date) to be validated and the upper and lower values for the input data. Excel will then check that the data falls within these ranges. The validation rules you have developed can also be stored and used in other sheets or workbooks later on.

Reviewing formulas
The edit menu can be used to look for errors in your formulas. It will not check that they are calculating what was intended but can at least check that there are no trivial syntactic errors, a formula referencing a text entry for example. If you do receive any error messages you can then use the audit tools described below to track down the error's source.

The auditing toolbar
The auditing toolbar will help you track down the source of any errors. When you find an error message if you activate the cell which contains the message and click on the trace error symbol on the toolbar Excel will draw arrows to highlight all the cells which are connected to that cell. Red arrows indicate the cells that are propagating the error, blue arrows indicate correct input values. The source of the error is therefore the cell that has a blue arrow leading in but a red arrow pointing out.

Adding explanatory comments
Adding explanatory comments to cells and using cell names can minimise errors and improve readability. The comments can be set to appear all the time or to appear just as an indicator which will expand to the whole comment when the cursor passes over it.

Avoiding the rounding off option
Although using the rounding off option doesn't alter the data contained within the spreadsheet it does affect how numbers are displayed. Confusion can arise when some numbers are rounded off whilst others are not or when the rounding off is displayed in different ways. When building a spreadsheet model it is best to turn the rounding option off and employ a consistent number of decimal places. Failing to maintain the decimal consistency can produce irritating and confusing results and may mask errors.

Work more efficiently
Most people use excel inefficiently by creating too many workbooks, preparing complicated reports that contain too many links between workbooks and using formulas haphazardly. This means that many hours need to be spent updating and auditing these spreadsheets. By completing some basic excel training ways to work more efficiently can be identified, thereby reducing the chance of errors being introduced and simplifying the process of detecting them if they are.


 

Author is a trainer with a Microsoft Office training company, the UK industry leader in its sector. For more information on Excel training, visit https://www.stl-training.co.uk.

Original article appears here:
https://www.stl-training.co.uk/article-78-excel-error-check-reduce.html