The electronic spreadsheet has been around in some form or other since 1962, with use becoming more widespread in the 1980s following the release of VisiCalc, Lotus 1-2-3 and Excel. What began as a tool to more efficiently add or subtract columns and rows of data has evolved into a powerful app that is capable of far more complex operations than just organizing and calculating numerical data, now even serving as a framework for creating apps. However, this ability to perform complex operations has not come at the expense of the user experience, and creating and managing spreadsheets remains something that anyone can manage with minimal training, meaning it is suitable for something as easy as maintaining lists, through to intricate tracking and analyzing of data.

Which is why business users have always favored spreadsheets.

Why Spreadsheets: The Good, The Bad, and The Future

The Positives

As the tools we use to create spreadsheets evolved, they have added features that allow for more than simply working with numbers and text. Users can now integrate charts, animation, and images, while using filtering, sorting and pivot tables to rapidly change the type of information displayed. While allowing for more complex operations, spreadsheets have also become increasingly flexible: adaptable to a wide variety of scenarios and uses, in almost any field – from household budgets, through to business finance, marketing, product management, and human resources planning.

Factors that have influenced the popularity of spreadsheets include:

• The fact that – at entry level – it requires very little training to use.
• It is cost effective, with open-source and commercial versions available for all operating systems, without the need for high-end hardware.
• In many instances, spreadsheets created on one platform can still be opened and edited on a completely different platform.
• It is a very powerful tool for editing, reading, reporting and analyzing data.

The Flaws

Despite their popularity, and ease of use, spreadsheets are not perfect, with the most significant flaw being how easy it is to make mistakes that aren’t easily detected. A 2008 analysis of studies related to spreadsheet errors suggested that 88% of spreadsheets have errors, with the author of this analysis writing that:

for large spreadsheets, the issue is how many errors there are, not whether an error exists.

He further notes that although the error rates are in line with those found in “programming and other human cognitive domains”, the development disciplines followed in programming are generally not followed in spreadsheet creation, which is often informal. A formula error in JPMorgan’s Excel based value-at-risk (VaR) model is thought to have contributed to the $5.8 billion losses reported after the London Whale trading debacle, which preceded reports of an Excel formula error also impacting on the results of a Harvard economics study.

Detecting errors on a simple spreadsheet maintained by a single person won’t be an arduous task, but in most business environments the spreadsheets are rarely simple, and seldom maintained by only one person. Even with strict policies in place to manage:

• the protection, validation and testing of formulas and macros
• change control
• version control

errors will still creep in because these measures are all manual, and subject to the fallibility of man. Other flaws inherent in current spreadsheet software include:

• Poor – or non-existent – audit trail functionality.
• The inability to generate real-time reports – with read-only rights – across the organization.
• Producing ad-hoc reports and analysis is often difficult and onerous.
• Managing and sharing large files is prone to problems.
• They lack effective processes for enforcing data ownership.
• They don’t offer a system for seamless, automatic back-ups, influencing data integrity concerns.

The Outlook

While some individuals want to sound the death knell for spreadsheets, others have started using the advent of cloud computing to revitalize spreadsheets by making them more collaborative, and eliminating some of the flaws outlined above. These online solutions are robust apps that support multiple dependencies, data sources, and – most importantly – multiple users. We are seeing online spreadsheet software that:

• Is comfortably able to handle big data.
• Loads data dynamically, from a variety of data sources, and integrates easily with other cloud-computing solutions.
• Is more modular, with add-ons that extend the functionality even further.
• Has error checking and version control built-in.
• Retains the ease-of-use and intuitiveness of traditional spreadsheet software, but offers better security.
• Offers automated back-ups, with effortless restoration.

Until your organization switches to using the next generation of spreadsheet software (and even after switching), there are a few measures you might consider putting in place, such as:

• Apply a common standard in the design and development of spreadsheets, addressing things such as cell formatting.
• Ensure that everyone who works with spreadsheets has had suitable training. Although basic spreadsheet work requires minimal training, more complex operations, including how to check the results of functions, can only be properly done with appropriate training.
• Implement best practices for collaborative work, including a peer review system, to help minimize errors.
• Add an About sheet to all complex spreadsheets, documenting the author, the purpose of the document, the version number, and any formatting conventions.
• Ensure that everyone is always using the same software version.

Conclusion

Despite the flaws, businesses of all sizes still use spreadsheets for a variety of purposes everyday: from critical business processes, to simple collections of data. And as we’ve seen traditional spreadsheet software evolve in what it enabled users to do, we can expect the new generation of online spreadsheet apps to advance this further. Simpler collaboration, dynamically loaded data, better scalability and easier integration with other software, means that the popularity of spreadsheets for business reporting and data analysis will not wane, but they will certainly be more dependable, and easier to manage.