98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article
Excel articles
How to check for and reduce errors in Microsoft Excel
Sat 14th October 2006
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
![](https://www.stl-training.co.uk/asset/image/icon/expand_off.gif)
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsStopgap Ltd
Finance Manager Sue Dale Excel Advanced Great course simplified things down to an understandable manner SAUL trustee
Investment Analyst Ravil Varsani Introduction to Management Great course, lots of valuable content with real life relatable examples. Duracell UK Ltd
Order Management Expert Graham Lloyds Presentation Skills Extremely helpful course. Well paced, never felt bored. No topic felt redundant. Andrew was extremely friendly and engaging. Good level of interaction between presenter and us. I definitely feel more confident after today. Would recommend it to anyone. |
PUBLICATION GUIDELINES