How to avoid and cure spreadsheet errors and calculation problems

"95% of all spreadsheets over 100 lines contain calculation errors and 75% of companies have experienced significant problems as a result of faulty data."
A survey by PriceWaterhouseCoopers

The level of errors in business critical spreadsheets is truly worrying but why does it happen?  Surely everyone knows how to use Excel

"The truth is that Excel and Word are easy to start using, but they are not easy to use well."
Christine Solomon - founder of Business Media Group inc.

The top five reasons for spreadsheet errors

Lack of Input control

The data validation offered by Excel is crude.  In practice, few users implement it even if they know it exists.  So it's not surprising that spreadsheets contain the most basic errors; for example entries that look like dates and numbers but are actually text strings.  The result is data you can't sort, you can't analyse - and  if you try it will give you the wrong answer.

Over Complexity

Few spreadsheet based 'systems' are planned.  More often they start as a single worksheet and just grow.  Acceptance that  a spreadsheet is unmanageable often doesn't  come until the owner tries to add yet another layer of complexity.  In many cases there is already an accumulation of unnoticed mistakes and by this stage, the spreadsheet may well have become a business critical application.

Linked workbooks

Workbook links are usually a sign that a spreadsheet is already too complex.  Formula links are rarely necessary and my advice is simple - don't do it!  If you need share data from a common source, use Data Queries instead.

Repetition

If you need to repeatedly cut, copy and paste data it's only a matter of time before you make a mistake.  Automate regular tasks with Excel VBA or macros.  Better still, store your data in a database and use data query to import the data you need.

Re-keying

It is a statement of the obvious that re-keying data is an opportunity for error.  Excel can link automatically to Access databases, web sources and enterprise data in your accounting, payroll and HR systems.  If you must re-key, be sure to use check totals.

How PBMS can help resolve your spreadsheet problems

Spreadsheet Audit

The first step involves standing back from the detail and making an objective assessment of the problem.  If you can E-mail your spreadsheet to us, we will review it for you and send our initial recommendations free of charge.  If we need to visit your site, the initial consultation is usually free but we may charge travelling expenses, or we may ask a colleague in your area to undertake this.

Custom templates, formulas and functions

Spreadsheet functionality is often duplicated frequently throughout a workbook when it would be more accurate and efficient to develop a modular approach using custom templates, formulas and functions to handle complex and frequently used procedures.

Data cleansing

No need to discard existing data.  PBMS has a wide range of techniques for removing duplications and converting wrongly formatted data.

Input controls

Once you have established clean data, you need to ensure it stays that way.  Lookup tables and input forms with validation not only improve accuracy but also make data input so much easier.

Spreadsheet to Database conversion

Converting all or part of your spreadsheet to a custom database is often the most efficient way to re-gain control over your data.  The structure of your existing spreadsheet is usually enough tell us all we need to know.

Try the Excel v Access checklist test to find if you should swap your Excel spreadsheet for an Access database 

More about Microsoft Access & SQL Server capabilities.  Multi-user input & editing, enterprise data integration and powering your web or company intranet presentations.

Contact PBMS Consultants

For more information, call PBMS Consultants on +44 (0)1494 815306 or E-mail SpreadsheetSolutions@PBMSconsultants.co.uk.