Home » Computers » Software

How To Check For And Reduce Errors In Microsoft Excel

Aug 17, 2007
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.
About the Author
Author is a trainer with a Microsoft Office training company, the UK industry leader in its sector. For more information on Excel training, visit http://www.MicrosoftTraining.net.
Rating:
Please Rate:
(Average: Not rated)
Views: 153
Print Email Report Share
Article Categories
    • Artists
    • Gambling
    • Humanities
    • Humor
    • Movies
    • Music
    • Photography
    • Tattoos
    • Television
    • Classic Cars
    • Motorcycles
    • Recreational Vehicles
    • SUVs
    • Trucks
    • Vans
    • Branding
    • Business Opportunities
    • Careers and Jobs
    • Corporate
    • Customer Service
    • Direct Mail
    • Entrepreneurship
    • Ethics
    • Financing
    • Franchising
    • Home-Based Business
    • Human Resources
    • Import and Export
    • Leadership
    • Management
    • Market Research
    • Marketing and Advertising
    • Negotiation
    • Network Marketing
    • Networking
    • Organizational
    • Presentation
    • Project Management
    • Public Relations
    • Small Business
    • Strategic Planning
    • Team Building
    • Telemarketing
    • Training
    • Ask an Expert
    • College and University
    • Home Schooling
    • K-12
    • Languages
    • Online Education
    • Psychology
    • Accounting
    • Credit
    • Currency Trading
    • Debt Consolidation
    • Insurance
    • Investing
    • Leasing
    • Loans
    • Mortgage
    • Mutual Funds
    • Personal Finance
    • Stock Market
    • Structured Settlements
    • Taxes
    • Wealth Building
    • Coffee
    • Cooking
    • Gourmet
    • Recipes
    • Wine and Spirits
    • Acne
    • Aerobics
    • Alternative Medicine
    • Beauty
    • Cancer
    • Cosmetics
    • Depression
    • Diabetes
    • Diseases and Conditions
    • Fitness Equipment
    • Fitness
    • Hair Loss
    • Heart Disease
    • Medicine
    • Men's Health
    • Muscle Building
    • Nutrition
    • Skin Care
    • Supplements and Vitamins
    • Weight Loss
    • Women's Health
    • Yoga
    • Arts and Crafts
    • Babies
    • Collecting
    • Elderly Care
    • Genealogy
    • Hobbies
    • Parenting
    • Pets
    • Pregnancy
    • Woodworking
    • Feng Shui
    • Gardening
    • Home Appliances
    • Home Security
    • Interior Design
    • Landscaping
    • Affiliate Programs
    • Article Marketing
    • Auctions
    • Audio
    • Banner Advertising
    • Blogging
    • Broadband
    • Domain Names
    • E-Books
    • E-Commerce
    • Email Marketing
    • Ezines and Newsletters
    • Forums
    • Internet Marketing
    • Link Popularity
    • Pay-Per-Click
    • Podcasting
    • RSS
    • Search Engine Marketing
    • Search Engine Optimization
    • Security
    • Social Media
    • Spam
    • Video
    • Viral Marketing
    • Web Design
    • Web Development
    • Web Hosting
    • Copyright
    • Cyber Law
    • Intellectual Property
    • National, State, Local
    • Patents
    • Regulatory Compliance
    • Trademarks
    • Buying
    • Selling
    • Baseball
    • Basketball
    • Boating
    • Cycling
    • Extreme Sports
    • Fishing
    • Football
    • Golf
    • Hockey
    • Hunting
    • Martial Arts
    • Running
    • Scuba Diving
    • Soccer
    • Swimming
    • Tennis
    • Dating
    • Divorce
    • Marriage
    • Weddings
    • Astrology
    • Buddhism
    • Christianity
    • Faith
    • Hinduism
    • Islam
    • Judaism
    • Meditation
    • Metaphysical
    • New Age
    • Cable and Satellite TV
    • Cell Phones
    • Communication
    • Gadgets and Gizmos
    • GPS
    • Satellite Radio
    • Video Conferencing
    • VoIP
    • Addictions
    • Coaching
    • Goal Setting
    • Motivational
    • Stress Management
    • Time Management
    • Clothing
    • Electronics
    • Fashion
    • Gifts
    • Jewelry
    • Causes and Organizations
    • Environment
    • History
    • Holidays
    • Men's Issues
    • Nature
    • Philosophy
    • Politics
    • Women's Issues
    • World Affairs
    • Air Travel
    • Camping
    • Cruises
    • Destinations
    • Outdoors
    • Article Writing
    • Book Reviews
    • Copywriting
    • Fiction
    • Non-Fiction
    • Poetry
    • Quotes
    • Screenplay
    • Tools and Resources