If you analyze data, chances are you use spreadsheets in some capacity in your organization. Spreadsheets are easy to use for simple data analysis and calculations, and they allow you to visualize tabular data via charts and graphs.
However, spreadsheets have limitations that many business people are not fully cognizant of. Relying on them for sophisticated data analysis and business decision-support can be inefficient.
Even small spreadsheets can contain thousands of links, formulas, and cell references, creating many of opportunities for error. Even in simple spreadsheet calculations, a cell reference that is off by even one row or column returns different results than what you expected.
Let's take a look at some common pitfalls when using spreadsheets for business analytics. We’ll also look at how business intelligence (BI) is a more reliable method for business decision support.
Spreadsheets are inherently risky because they typically are updated manually, and error-checking is extremely challenging. A calculation can easily become broken in the course of adding or updating data. An error in one cell can snowball throughout your data. Don’t believe spreadsheet errors represent a significant risk? They are blamed for:
- A $6 billion derivatives trading loss at JPMorgan Chase. Investigators found that the model that underpinned the hedging strategy operated through a series of manually completed spreadsheets that should have been automated but never were.
No business can afford to base important business decisions upon something that can contain mistakes.
Increased Security Risk
Spreadsheets are frequently distributed via e-mail. Unfortunately, both spreadsheets and e-mail suffer from weak access-control capabilities, so you could easily expose unsecure data to both employees and non-employees. Did your spreadsheet with the comp plans for your sales team just get forwarded to your competition? Even password-protected spreadsheets can easily be compromised by free or inexpensive third-party tools (just Google “how to crack a spreadsheet password”).
Another security hazard is that with spreadsheets, anyone can change the data. Also, there is no built-in audit trail. If someone edits a formula or data, there’s no recording of who made the edit and why. There is also no archive or roll-back capability.
Spreadsheets Simply Cannot Handle Large Data Sets
As they grow in size, spreadsheets become slow and unwieldy, resulting in lost productivity as users wait for queries to complete. Links and formulas commonly break, and ensuring formula validity becomes more and more problematic, if not impossible. Spreadsheets are designed to handle data across two dimensions; analyzing more than this involves creating pivot tables or manually creating views of additional dimensions, which is fragile, time consuming and error-prone.
In addition, reports that must be updated daily (or more frequently) present significant problems when maintained as spreadsheets. Users must manually manipulate them to append the latest data and delete or archive the oldest data. Formulas must then be copied and validated to transform the raw data columns into the final data elements that are displayed in the report.
Spreadsheets are not Intuitive for Your Average Business User
Most business users can use functions such as “sort” and “sum,” but find it difficult to create a moderately complex formula. For more advanced analysis, business users must often lean on the “power users” within their organization to get the job done, which wastes time for everyone.
It’s also complex to update data. Users must manually recreate transformations and debug any calculations each time the data is refreshed. Merging data from different sources is difficult, time-consuming and filled with the potential for errors.
Business intelligence as a solution
While spreadsheets are useful for simple ad hoc calculations, when they are used for purposes more appropriately addressed with a BI tool, they can expose your company to reputational and financial risk, lost productivity and significantly slower time-to-insight as information workers struggle with the limitations of spreadsheet-based reporting and analysis.
Technical Operations Manager