How to reduce the number of errors in Excel spreadsheets

This is the default value for this text field

Author: Jake, Marketing  
Last updated: August 3, 2017

Today, thanks to Excel, anyone can be a maths wizard but it hasn’t made us infallible. Before you make big business decisions on the basis of your next spreadsheet, know this: Almost 90 percent of all spreadsheets contain errors.

This risk is unacceptable and demands attention. You have to be able to trust your information.

There are many technical, procedural and cultural steps you can take to improve the reliability of your spreadsheets. Here are great ways to reduce the risk of Excel errors.

1. Use software tools

There is a simple test which can spot Excel spreadsheet errors. You can press Ctrl + [on a formula], to show what cells make up the total. If it comes up with a #REF you can trace the error to the offending cell and correct the error. However, this method can be time-consuming. There are several software tools which are more effective in finding errors than this. We recommend:

XLTest. This checks the reliability of your spreadsheets quicker than the boring cell-by-cell inspection. XLTest’s comprehensive listings are best for audit record purposes.
Spreadsheet Auditor. This software can search your document for #REF error sources, examine and terminate errors, detect problem areas, make spreadsheets shareable and much more.

2. Use peer review

You wouldn’t publish a book or article without having it edited and proofread. Yet, most Excel users are happy with their first draft, the breeding ground of avoidable errors. Peer review is an essential step for any business-critical spreadsheet. This means asking colleagues to check your spreadsheet, test different scenarios, review your assumptions with you and generally cast a critical, fresh eye over your work.

3. Create industry standard financial models

Your financial models are forecasts of real world financial situations that you can create. These models depend on your assumptions and the source data. So there is a substantial risk of error from these directions.

You can reduce these risks by following guidelines on best practice from organisations like F1F9 and the European Spreadsheet Risks Interest Group.

finance-modelling (1).jpg

4. Train your staff in risk reduction

There are training and certification programmes, such as the Spreadsheet Safe certification, which help people avoid spreadsheet errors.

5. Cut the number of spreadsheets

If you reduce the number of spreadsheets, you reduce the errors associated with them. Many spreadsheets are really ad-hoc business applications: think of all those customer databases, cash flow forecasts, expense reports and so on. Replace them with dedicated software, such as Microsoft Dynamics ERP or other line of business applications, and watch the errors evaporate. You can also share spreadsheets in a controlled way using SharePoint Online.

6. Automate spreadsheet construction

Third party tools can generate spreadsheets directly from various accounting applications. By automating this process your financial statements will not have any mistakes due to Excel errors.

7. Try ClusterSeven

ClusterSeven’s cloud-based evaluation tool can inspect your file for errors. It compares your spreadsheet to pre-determined best practice rules. You simply upload and let the software do the rest.

8. Run your spreadsheets in the cloud

Using RedPixie’s own Azure Calculation Engine, you can turn complex Excel spreadsheets into well-engineered, reliable, high-performance applications that run in the cloud. This improves performance but it also brings the rigour of professional software engineering to your spreadsheets.

Take Excel to the next level

Despite the risk of error, Excel spreadsheets are not going anywhere. It’s cheap and easy to learn compared to competing software. However, manual input creates the risk of errors which can hinder your business practices and growth. Follow our tips and never find yourself betrayed by your own tools.

Topics: ACE, Analytics, Data, Excel, Finance, Office

KNOWLEDGE IS POWERGet weekly email insights from RedPixie

Free GuideLearn how to choose the right IT partner with our free guide
surface banner.jpg