5 Excel-lent Tips For An Error Free Spreadsheet
Did you know that an astonishing 88% of spreadsheets contain errors?!
A University of Hawaii study suggests that there’s an error rate of more than 1 in 100 formula cells Panko, 2008. We recently conducted our own study, analysing over 1,000 cashflow forecasts and discovered that the average number of cells in a cash flow forecast is 1177…begging the question, how much confidence do you have in your cash flow?
No one is immune to spreadsheet errors and whether you are forecasting your household expenditure or you are the London Olympics and you’ve accidentally oversold an event by 10,000 tickets (this actually happened), the fact is, mistakes happen, and human data input is the most consistent cause of errors.
But don’t worry, we’ve compiled a list of 5 simple solutions to make sure you don’t get caught out!
The more eyes you can get on your spreadsheet the better
Check your work before you publish it
The excitingly named European Spreadsheet Risks Interest Group (EuSpRIG) recommends comparing your spreadsheet to previous work, printing a copy, reading it as the intended viewer and then waiting until the next morning before checking again. The simplicity of this advice means it is often overlooked but a quick review of your work should always be your first port of call.
Run a test
Another suggestion from EuSpRIG is to run a few simple tests on your spreadsheet. Ctrl + [ is a useful test to show what cells make up your total. Using a few tests as a backup measure is an invaluable trick that can save you unnecessary hassle in the long run.
Graph your data
Graphing your data used to be time-consuming but these days can be done at the click of a button. Forbes cited this as the most efficient way to spot mistakes in your spreadsheet as it shows up coding errors and draws attention to anomalies. This is a good habit to get into as it only takes a few seconds and can show glaring inaccuracies.
Use a tool
As well as Excel’s auditing toolbar there are loads of spreadsheet auditing add-ons out there that will help you highlight any mistakes and map out your data in a simple format. As long as you are happy with limited functionality and only really need them as a no thrills error discovery tool then they are worth your attention.
Peer Analysis
The Society of Actuaries recommends peer analysis as a good way to flag up mistakes in your work. A fresh pair of eyes can bring a new perspective and you can return the favour for them. However, they then go on to say the best results will come from a group of 3 or 4 people working together. Basically, the more eyes you can get on your spreadsheet the better.
A combination of these will be your most effective tool in the fight against spreadsheet errors, now go forth and multiply… or SUM… or whatever…
Cash flow forecasting without spreadsheets
Float is an online cash management and forecasting tool that helps you manage your business and keep on top of your cash flow. By projecting your future cash in the bank, it’s easier to make the right decisions for your business. Sign up for a free trial here!
