98.8% 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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsLive Nation
EA To SVP Finance Seray Hussein Excel Introduction Martin was amazing and really engaging. He was patient and explained topics really well. Will defiantly recommend Sancroft
Analyst Hetty G Office 365 End User Highly valuable day, which will definitely improve office communications and also my ability to use different functions. Nottingham College
People Services Business Partner Claire Mchugh Visio Introduction Marius was great, very knowledgeable and happy to go back over aspects that we were not clear on. Lots of great tips on shortcuts and easy ways to navigate the system. Thank you, |
PUBLICATION GUIDELINES