Home / Thought Leadership / Spreadsheet Whack-a-Mole: The Unwinnable Game
Spreadsheet Errors

Spreadsheet Whack-a-Mole: The Unwinnable Game

December 17, 2020

Why are Spreadsheets So Popular?

“Excel is the best tool non-programmers have to write code and that is why it wins” wrote Salvatore Sanfilippo, a highly regarded distributed systems engineer, on YCombinator news.

The idea for the first spreadsheet program occurred to Dan Bricklin while attending Harvard in the mid 70s. It seemed unlikely to Bricklin that anyone else had thought of it before. He went on to create VisiCalc, the first electronic spreadsheet program that debuted on Apple II and went on to change the world of personal computing.

Steve Jobs credited the success of Apple II, the first truly personal computer sold by Apple, to VisiCalc.

Today, businesses of all sizes continue to rely on spreadsheets.

There is a very good reason for this— spreadsheets do a lot of things very well from personal expense tracking to financial forecasting, process automation and so on. Spreadsheets are powerful tools that empower end users to do complex programming tasks, without having to wait on IT resources, budgets and bureaucracy.

It’s difficult to foresee the need for non-programmers to program ever go away.

What Then, Is The Problem With Spreadsheets?

“Nobody sets out to create a mission critical spreadsheet, they ‘just happen’”, says Felienne Hermans, an associate professor at Leiden University in the Netherlands.

The great power that comes with Excel propels the creation of what starts out as a simple app— “I am just analyzing data”— into something that quickly becomes indispensable and is used by multiple people.

What makes spreadsheet programs so intuitive for the end users also make it attractive to the consumers of the spreadsheet created. When given a spreadsheet that does something in their domain, most people will easily find their way around it, enter new numbers, look at the results, all without the help of extensive documentation, help or tongue-in-cheek, an AI powered virtual assistant.

However, it is when individual spreadsheets start becoming critical business assets and are consumed across a department or across business lines, that problems start. To a risk averse person, everything feels as if it has been duct taped together.

The common lament heard from IT departments or professional developers is that spreadsheets are created without discipline. Further they;

  • Do not include comments or explanatory annotations
  • Are hard to maintain
  • Have so many copies nobody knows “which version is the single version of truth”
  • Have had absolutely no quality checks

Would you ever allow your software development group to have multiple copies of an app, no quality checks and release test versions of an application without warning? Yet, most organization do all of this with hundreds of thousands of spreadsheets.

What compounds all this is that the scale and amount of spreadsheets in a typical organization make it very hard, if not impossible, to know how many such spreadsheets exist and how to even go about taking care of the problems.

Problems pop up in the only way they know how to— uninvited and unannounced.

What Are The Choices?

Given that spreadsheets are powerful, what choices do you have in order to bring a measure of sanity and risk reduction? Let us consider all options— from the very absurd to the most benign.

  • Ban– Ban all spreadsheets
  • Do nothing– Leave things as-is and ignore any problems
  • Follow SDLC for spreadsheets– Mandate that all spreadsheets (or some known ones) are handed off to IT and only created using the best software development life-cycle practices
  • Convert a spreadsheet to a proper application– Transform the spreadsheet into a proper application in a proper application platform
  • Use Spreadsheet Alternatives– Mandate that all spreadsheets use a different technology that allows end users to create functional applications but with none of the drawbacks
  • Use friction-less spreadsheet risk management– Allow end users to leverage the full power and flexibility of spreadsheets while still managing risk

Ban

Not much needs to be said about this first option. Even those most open to disruptive changes knows it’s infeasible.

Do Nothing

The second option is probably unpalatable to you if you are a compliance and risk professional in your organization.

Follow SDLC

Following SDLC for spreadsheet development and maintenance works well for 2 cases:

  1. A completely new spreadsheet application that has all requirements well defined, with a team of business analysts, QA, and development resources available. This begs the question— why use spreadsheets at all? Why not make a proper application in this case?
  2. A mature and stable spreadsheet without frequent changes. Since the spreadsheet is all set and is a long-standing one with well-known usage, you could mandate that any changes will now be done in a well-regulated manner.

This approach is effective in patches, but it ignores the problem of why spreadsheets are so popular in the first place. New spreadsheets crop up at an alarming rate and before you know it, they become mission critical.

As soon as you move a spreadsheet to an IT responsibility, the increased turn-around times force many users to create a spreadsheet of their own, because they feel (and many times rightly) that their business cannot wait.

Alternatively, training business/ end users to do spreadsheet development and maintenance using SDLCapproaches can be very difficult to accomplish.

Converting a Spreadsheet to an Application

If you convert the spreadsheet to an application, the same applies. The business user’s requirement changes and before long they are exporting data from your newly created application into a spreadsheet that is on its way to becoming mission critical.

Use Spreadsheet Alternatives

There are some alternate end user-friendly technologies that are evolving in order to create zero code platforms. These range from things like Zero/ No-code platforms to using programming languages like Python and R.

These technologies fall across a wide range on the spectrum between powerful and end user friendly.

The key fact that remains here is that they remain end user technologies.

The biggest question remains— will you be able to move most, if not all, of your mission critical spreadsheets? Will you be able to mandate that new ones are created only using these alternate technologies?

Additionally, there is currently no one technology that can fully replace a spreadsheet. Any replacement strategy needs to deal with heterogeneity and the consequences it brings to risk management.

Friction-less Spreadsheet Risk Management

Apparity recognizes that the longevity of the spreadsheet is a testament to its versatility.

Even if you choose one or all the above approaches (except for banning spreadsheets), spreadsheets will always pop up and evolve in an organization, no matter how small. The moment you replace one, another one will pop up.

Anecdotally, one of our customers, a famous lease-to-own company, moved an existing capacity planning spreadsheet to SAP after a year of developing requirements and working with consultants. Within the first 2 weeks after it was moved, the department asked for a button on the web application to export to Excel.

So yes— whacking spreadsheets can be fulfilling, but it may not be entirely productive or successful.

Apparity allows end users to continue leveraging the power and flexibility they love while still helping you manage risk. Apparity’s intelligent active management does not require any extra behavioral change by the user in their normal activities. It stands aside, serving as both a companion as well as a detective tool for audit and compliance teams to help understand the ever-growing spreadsheet landscape in your organization.

Subscribe for Updates

Subscribe to our newsletter for exclusive content.

Subtle White Feathers

Subash Kalbarga

Subash Kalbarga is the Chief Technology Officer and a Co-founder of Apparity. In this role, he guides the development of Apparity’s technical architecture.

Related Articles

Top 3 Issues of EUC Discovery

Top 3 Issues of EUC Discovery

Vaguely Defined Not all Excel spreadsheets, databases, scripts or BI tools are end user computing (EUC) applications....

Share This