Addicted to spreadsheets: the problem with Excel-based solutions

Addicted to spreadsheets: the problem with Excel-based solutions
narrativio

How could 750 million users be wrong? Microsoft’s ubiquitous spreadsheet program, Excel, is one of the most popular business applications. Originally designed to compute data, Excel’s incredible flexibility means it gets used for an unbelievably wide range of key business processes, including data storage, reporting, customer relationship management (CRM), budgeting, project management, inventory management, data integration, time tracking and more. But spreadsheet use has a dark side. 

A figure is often quoted that 88% of spreadsheets have errors. Although the study that this is based on dates from 2008, the number today probably hasn’t changed much. Let’s face it, updates to Excel in past years have not addressed the core problems that lead to errors. These problems actually can’t be fixed within Excel, because they are fundamental to the application’s structure.

Excel horror stories

But just how dangerous is Excel – how bad can it get? As it turns out, Excel errors can go terribly wrong with serious consequences.

Perhaps one of the worst stories, as well as the most recent, occurred in the UK this year. A spreadsheet used for coronavirus contact tracing lost nearly 16,000 cases, resulting in people who came in contact with infected patients not being informed. Most certainly this led to further spreading of the virus, putting lives at risk. And the culprit? A file exceeded the file size limit, throwing out records.

Spreadsheet errors can directly affect a company’s bottom line. For instance a copy-paste error, where a user misaligned the rows in a spreadsheet, cost Canadian power company TransAlta $24 million, or 10% of the company’s profit. Millions more in losses are attributed to mistakes such as a missing negative sign, a misplaced decimal point or faulty formulas.

Errors can also cause compliance and data protection headaches, like when a templating error caused pharmaceutical company AstraZeneca to accidentally release confidential company information to analysts.

Even government policy decisions are not immune. A highly-regarded paper from two Harvard professors was a driving force behind years of austerity measures in the UK that led to drastic increases in homelessness, childhood poverty, crime and unemployment. Unfortunately the paper was heavily flawed due to a formula that accidentally missed several cells; the correct calculation would have favored a very different policy.

Why Excel isn’t the right tool

Excel and other spreadsheets are great for individual employees to analyze data. They become dangerous when they get used in ways they weren’t designed. And they are almost never the right tool for any mission-critical business processes. There are three main reasons why Excel cannot beat a purpose-built solution.

Excel is not a database

Excel is most commonly used to store data, but it is not a database. For starters, the data isn’t really structured; you can input text into a numeric field or put non-dates into a date field.

More critically, the data model is two-dimensional, yet in real life most data has many more dimensions. There are work-arounds: pivot tables, multiple linked tabs, grouped cells, cascading drop-down lists in data validation, just to name a few… but none of these get around the fact that in the end, it’s all crammed into a 2D package.

You also can’t easily define different views of your data because it’s implicitly linked to a cell, and thus to a certain visualization. And finally, while search functionality has improved it doesn’t compare to that of a proper database. Microsoft has spent years shoehorning database-like features into Excel, but it will never be a real database.

Excel is error-prone

The very thing that makes Excel so powerful – its great flexibility – is also one of its greatest weaknesses. You can do everything, but everything is manual. So you can very easily make mistakes:

  • Faulty formulas can easily slip through, especially when they get complicated or include lots of nested IF statements
  • Data formats are not validated by default, but if used they are applied manually to cells and are subject to the same errors as everything else in Excel
  • Cell ranges are selected by hand, making it easy to pick the wrong cells
  • Copy-paste errors are rampant, including misalignment, duplication and accidental overwrites
  • Manual data entry: typos, typos, typos!

Excel is file-based

A spreadsheet is not a standalone program running live on a server and connected with standard interfaces. It is a static, uncontrolled file, which presents serious risks. This is probably the most compelling reason why Excel should not be trusted to manage core business processes.

The most obvious problem is collaboration. Although there are alternatives, Excel files still get emailed around, leading to multiple local versions. Which is the master and how does it get updated? Comparing versions is challenging. If the file is in a shared location, different forms of collaboration are available. Co-authoring and shared workbooks are two options, but each has significant trade-offs in file storage, conflict management, change logs and more. And no matter how the file is shared the potential for mistakes is multiplied, especially since other users probably don’t have the author’s intimate understanding of the sheet’s structure.

Another major issue is the static nature of data connections. The data import/export model means that in many applications the data is outdated almost immediately. It’s not truly linked and there is no real-time update. Even with third-party add-ons, data connections are fragile at best.

Last but not least, security is a serious problem with Excel’s file-based model. IS or compliance teams have little or no insight into what an Excel file contains; security and compliance is virtually non-existent. There is nothing to prevent a file from being emailed or copied to insecure private computers or shared with unauthorized users. Confidential data can also be accidentally leaked, for instance due to improper use of hidden cells. And because Excel is so flexible and lacks transparency, it’s easy to manipulate the results with hidden values or complex formulas.

Breaking the addiction

What’s a better alternative, then, to spreadsheet solutions? As full of holes as Excel is, people keep coming back to it because it’s easy. With no knowledge of data structure, integration, workflow or anything else, anyone can quickly get started building a halfway workable solution in Excel that will more or less do what they need. And the makers of such solutions are often quite  proud of their creations.

People often turn to Excel when two factors combine: when standard, off-the-shelf solutions don’t fulfill their needs, and when IT lacks the resources to develop a suitable custom business application. But there is an alternative.

Low-code development platforms enable business users with no programming experience to quickly and easily create beautiful, functional business applications in a visual environment. And we’re not just talking about simple little tools; highly scalable enterprise-grade solutions are possible, including proper data structure and validation, real-time integration with external data, customized masks to meet the data entry and visualization needs of different users, workflow and business logic, user and role management, multi-language support, and all the other features we expect of software today.

 

Ready to break your company’s addiction to Excel? Get a demo and find out how low-code development can save time and provide more reliable, robust solutions.

 

Get a Personal Demo

0 Comments

Leave a reply