The Problem of Managing Data With Digital Spreadsheets

Using Spreadsheets? You’re Doing It Wrong!

Digital spreadsheets are arguably one of the better productivity tools created since the invention of the modern personal computer. However, there is a big qualifier here: if you know when to use a spreadsheet, how to use a spreadsheet, and for what purpose.

Digital Spreadsheets – A Favorite of Many

I’m a big fan of digital spreadsheets. I remember diving into them when I was first introduced to Lotus 1-2-3 as a grade schooler. One of the first things I did was create stats aggregations using baseball cards of my favorite players. Through the digital spreadsheet, I was able to build my dream baseball team. Though imaginary, it was magical.

Today it seems as though Excel spreadsheets and workbooks have taken over the world. They became a game changer in business operations beyond just bookkeeping and accounting. Just as Word and other programs opened up the world of desktop publishing to every computer user, Excel and other tools opened up the world of graphs and data visualizations. Spreadsheets introduced the power of data cubes through the gateway we now call pivot tables, and of course, they allowed us an easy but detailed way to modify data. In my opinion, digital spreadsheets made data more available and less intimidating for those who were outside the seemingly exclusive world of the database elites.

As digital spreadsheets became part of the common and daily arsenal of the business world, they inevitably found their way into the realms of data management. This isn’t such a bad thing, in my opinion. In fact, the majority of data practitioners I know use Excel on a day-to-day basis. Include me in this pool of users. I use it almost daily.

… And an Enemy of Many

While there are many benefits of the digital spreadsheet, overreliance on it to manage a company’s customer, vendor, and financial master data might do more harm than good. The perils of compounded errors from manual and unsanctioned modifications, unmanaged multiple versions of data, and data management scalability limitations are just some of the (many) shortcomings that come to mind.

A colleague has made his opinion of spreadsheets known, going so far as to say that “anyone who uses spreadsheets for any use case outside of accounting or finance could be guilty of data negligence. They are shortchanging themselves.” But he is quick to point out that “many workers are forced to use the tool due to lack of better solutions being made available. Still, I can’t imagine how many billions of dollars in productivity are wasted each year as people are forced to struggle with using spreadsheets when they should be using other platforms.”

Before we dig into the dangers of using spreadsheets in data management endeavors, let’s try to understand why organizations tend to be overly dependent on digital spreadsheets to manage their data.

1. They are affordable and everywhere – Digital spreadsheets are ubiquitous. Microsoft Office and Google Sheets have made spreadsheet functionality accessible to nearly everyone with a computer. We rely on spreadsheets to share information at work and at home. Like it or not (and there’s certainly more “likes” than “nots”), spreadsheets have carved out a place in our daily lives.
2. They are straightforward to learn – It takes a very short time to learn the fundamentals of digital spreadsheets. A 10-minute online instructional video can help you get started.
3. They use fields and rows – The spreadsheet construction displays basic elements that are similar to a database table: Column headers can act as field names, and each row is a data element.
4. They are expandable – Digital spreadsheets can be collated, connected, and related to a workbook. In some cases, other software applications allow easy connectivity to these spreadsheets.

With these characteristics, Excel and other versions of digital spreadsheets become attractive solutions for administering data. Yes, they can do the job – and that’s the biggest problem.

Easy to Use, But Spreadsheets Have Their Limits

Spreadsheets can help you perform data management tasks, but they have their limits. Think of using spreadsheets as traveling around the world using nothing more than wind power. Yes, for centuries humankind has proven this is possible – wind-powered sailing vessels can get you where you need to go with grit and effort. However, you will be more susceptible to weather changes, not to mention the additional time and effort it will take you to get there. Airplanes are faster, more efficient, and reliable. There’s a wide spectrum of transportation choices between a sailboat and a jumbo jet. Somewhere along that spectrum is the best choice for a specific use case, just as there is a best choice of tool to use when managing data.

Here are four hidden dangers (and costs) of being overly dependent on digital spreadsheets to manage your data that could have detrimental effects on your business:

1. Scope creep and scalability – Overreliance on digital spreadsheets to master your data could lead to scalability issues: Digital spreadsheets can give birth to a “Franken-process” of cobbled-together short-term solutions as time progresses. What starts as a seemingly mundane record-keeping of booking data can very well become an unwieldy master list of your company’s customer master database that only one person has access to and understands its nuances. This now presents a scalability issue. Sound familiar? This happens more often than you’d think.
2. Errors and questionable integrity – In its standing research, PwC found that more than 90% of spreadsheets contain material errors. This doesn’t mean that data in databases are immune from errors; however, with digital spreadsheets, there are few to no rules or policy-based processes to protect data integrity. The spreadsheet’s ease of use – for example, adding and removing columns and rows – can exponentially impact its integrity due to mistakes. Without version protection or user tracing, it might be a challenge to undo changes or restore a prior version. Data can be easily changed without verification. On the flip side, databases can be outfitted with rules to ensure a desired measure of data integrity to reduce data errors. As an example, it was reported a few years ago that JPMorgan Chase endured a trading loss of around $6.5 billion due to a copy-paste error by a user.
3. Time-consuming nature – Digital spreadsheets can provide time savings when used appropriately, but there’s a huge potential for improper use. The process of tracking customer deals could be excessively time-consuming in digital spreadsheets. This cost is only compounded by the challenge posed in in the paragraph above: The lack of audit trails make it challenging if not impossible to track whether anybody made edits or changes to a spreadsheet. This can be compounded by how easy it is to modify and change numbers. You are providing a business process vulnerable to false reconciliation. According to RTInsights.com, the excessive overreliance on digital spreadsheets contributes to a $60 billion productivity burden in the U.S. alone.
4. Lack of metadata – Spreadsheets can add to the problems and frustrations that data practitioners are trying to solve. Allowing data entry in a spreadsheet is just asking for potential errors. Formatting is difficult to control. Rules are difficult to create and enforce. And users are left wondering what they’re even being asked to capture. Databases and other platforms exist to support your metadata needs to ensure that you will be able to find, use, preserve, and reuse data at scale when needed.

Digital Spreadsheets: Double-Edged Swords of Data Management

In order to champion data quality across our organizations, it’s imperative that we use the right tools. Excel and Google Sheets are readily available, easy-to-use tools that appear to the casual user to lend themselves to data management tasks. But there are better choices. Examples of these are Microsoft SQL Server or Microsoft Access to start. These tools are also readily learned via a 10-minute how-to video found on the internet. Explore the limits of tools like digital spreadsheets, and understand that they are just tools. The decision to migrate to a a more capable and robust data management system rests on you, the data practitioner. Use what you've read above to help you decide when it’s time to elevate your data management game.