The spreadsheet may be one of the most powerful and most dangerous tools that a business user has in their toolkit. In fact, Microsoft estimates that there are over 1 billion Office users worldwide. Yet, spreadsheets are the go-to for many types of analysis and modeling needs. They are easy to use and great for creating simple visualizations. And, are an easy way to manipulate and shape data from different sources.
The power of Excel is also what can make it dangerous. Which makes me think of the famous line from Spiderman “With great power comes great responsibility”.
In this article, we will discuss the 3 most common types of spreadsheet errors.
1. Mechanical Spreadsheet Errors
A mechanical spreadsheet error is caused by mistake in typing, cutting and pasting or other simple manual operations. Basically, these types of spreadsheet errors can be grouped into the human errors category. I have seen studies that show 85-90% of spreadsheets have errors in them or 1-2% of formulas are incorrect.
As with any manual processes, there is always the chance of human error. People rushing to get a quick spreadsheet analysis out the door will key in the wrong cell or copy a value to the wrong place.
To reduce mechanical spreadsheet errors we need to build controls and check into processes.
2. Logic Spreadsheet Errors
You may have received an error like this at some point while you were working in Excel.
The good news is Excel recognizes there is an error and prompts you to fix it. The bad news is the errors typically don’t make much sense. As a result, people usually just start over.
The other possibility is that Excel doesn’t recognize the error and the user moves on thinking it’s fine. But, now your analysis will be incorrect and cause bad decisions to be made.
In addition, logic errors can be caused by selecting the wrong algorithm or writing a formula in the wrong order. Except for the most advanced users, spreadsheet errors happen frequently and can be some of the most damaging.
To reduce logic spreadsheet errors, build a library of common business rules that are proven and vetted for people to use in their models.
3. Omission Spreadsheet Errors
“People don’t know what they don’t know” is a common phrase and it applies to this category of spreadsheet errors. Excel models are based on complex business processes and if Excel is the only tool people use, business-critical components can be left out of the model completely.
To reduce omission spreadsheet errors, build blueprint models that include all critical business components. As a result, each user isn’t recreating models with limited knowledge or bias.
Overcoming the Challenge
In conclusion, spreadsheet-based models are here to stay. They are a part of the standard business toolkit. Thus, saying the answer to spreadsheet errors is to eliminate the spreadsheet is not a viable solution.
Finding a solution to spreadsheet errors while still allowing users the flexibility of Excel can be done! Above all, finding the balance between these two goals will create benefits for the organization including:
- Streamlining manual modeling process to lessen reliance on Excel
- Moving toward a single version of the truth
- Creating an environment where people can collaborate
- Create corporate standards for calculations and hierarchies
- Reduction in mistakes and confusion in your models
Download this Infographic for more information on spreadsheet errors or watch this video on how Basin Electric implemented a modeling solution to help reduce the possibility of spreadsheet errors via manual processes.
