Back to Basics: Is It Time to Break Up With Spreadsheets?

Spreadsheets are ubiquitous. Businesses use them to track accounts and spending, store and analyze data, and do forecasting for future planning. Individuals use them to create to-do lists, manage household budgets, and even to choose babies’ names. Spreadsheets are not only ubiquitous, they’re also powerful, inspiring both dread and devotion.

Spreadsheets have an interesting history that can help explain their uses and limitations. For product and quality work, spreadsheets can be a boon, a Band-Aid, or debacle.

A Brief History of Spreadsheets

 The origins of today’s spreadsheets can be traced back to paper ledgers, batch compilers, and IBM mainframes. But Dan Bricklin and Bob Frankston were the first to create a PC-based spreadsheet program in 1979.[1] Inspired by a business school assignment in which he and other students were asked to forecast the financial consequences of an acquisition, Bricklin asked Frankston, a fellow MIT graduate, to help him build an electronic ledger. That year, VisiCalc became available on the Apple II and then on the IBM PC in 1981. In fact, it became so popular that people purchased Apple IIs and IMB PCs just to get VisiCalc.

Other spreadsheet programs quickly began to be developed and released, with Lotus 1-2-3 displacing VisiCalc by 1983. The first version of Microsoft Excel became available on Macintosh in 1985 and then on Windows in 1987. A decade later, Excel dominated the market. It’s estimated Excel now has more than 750 million users worldwide.

Today, collaborative, cloud-based spreadsheet programs like Microsoft Excel Online and Google Sheets allow multiple users to access the same data and work asynchronously, tracking each other’s changes, and leaving comments. Other collaborative, cloud-based programs like Airtable, GRID (still in Beta), and Smartsheet use the grid and tabular formats made popular by spreadsheets, but offer additional features ranging from automation to notifications and real-time reporting to administrative access controls to extensive app integration to rich information visualizations. While these programs can still be used as electronic ledgers, they are much, much more than that.

The Benefits of Spreadsheets

As Hjalmar Gislason, founder and CEO of GRID, observes, spreadsheets are programs.[2] What this means is that spreadsheets were designed for users to create declarative and reactive relationships between data. Yes, I can use Google Sheets to create a to-do list that I can then sort and filter, but I’m only scratching the surface.

The benefits of using spreadsheets include:

• Familiarity and ease of use, especially for basic functions
• Linking for some kinds of related data
• Free or almost free
• Powerful enough to support multiple and advanced calculation features that many of us with advanced degrees can’t do by hand
• Categorization and display options that make data consumable
• Universal file formats that promote sharing
• Flexible open structure

The Limitations of Spreadsheets

There are, of course, limitations to using spreadsheets, particularly when it comes to managing products across dispersed teams. Some of these limitations are inherent in spreadsheets and not specific to their use for product management—most notably, “Garbage in, garbage out.” As Levy writes, “People tend to forget that even the most elegantly crafted spreadsheet is a house of cards, ready to collapse at the first erroneous assumption.” Because spreadsheets are often difficult to test and troubleshoot, human error is likely to go undetected until the consequences are significant. For example, “a cut-and-paste error in an Excel spreadsheet” cost Canadian electricity and utility company TransAlta US$24 million in 2003.[3]

Additional limitations of using spreadsheets include:

• History and traceability—a lack of traceability, which is problematic when it comes to audits
• Context—the inability to include all product design information, especially the latest versions
• Collaboration—limited sharing and permissions options
• Security—simplistic options that most teams quickly outgrow
• Logic-checking—minimal built-in logic checking available
• Open—no boundaries

Put simply, there are many jobs spreadsheets were not designed to do.

Breaking up With Spreadsheets for Product Development and Quality Management

For people who have spent years lovingly creating and maintaining their spreadsheets, the idea of breaking up with them might be too much to bear. There are very real switching costs. In addition to the time and effort required to migrate data from spreadsheets to a new software program, team members must also overcome emotional and mental hurdles—letting go of old work practices and learning new ones.

However, you shouldn’t let sunk costs determine your next steps. Remember that sunk costs are just that—the past—and cannot be recovered. Prospective costs are the future impact your current system of managing the product will have.

Ask yourself:

• Do your spreadsheets prepare you for audits?
• Are you able to share your spreadsheets with internal team members, partners, and suppliers, controlling what they can see and edit?
• How do you manage changes to your product record in the spreadsheets? Is this connected to the spreadsheets or using other tools?
• How do you know your spreadsheets contain the latest product information? What happens if they don’t?
• Can you track environmental compliance in the spreadsheets?
• How do your spreadsheets connect to quality actions against the product record?
• Do your spreadsheets help identify issues or visualize the next opportunity for your products?

When Will Your Spreadsheets Fail?

Studies show 90% of spreadsheets have a minimum 1% cell error rate.[4] While it is difficult to predict which errors will cost your business in what ways and how much, we can use an analogy to consider the risks.

This year, 2020, is the year we’ve all become more educated in virality and R₀ (pronounced R naught). While spreadsheets and pandemics do not seem related, we can use the same math. While an error in one spreadsheet might have minimal impact, you probably have many more than one spreadsheet. Your spreadsheets may be linked or contain duplicate data across product assemblies and lines. Every day, you make changes to those spreadsheets due to issues, quality actions, engineering change orders. Your spreadsheets are an ecosystem, a population of data that is interconnected. When any part of the data is incorrect (or unhealthy), the larger population can be affected.

R₀, or the reproduction number, is a mathematical term indicating how contagious an infectious disease is. For diseases, R₀ represents the number of people who will contract the disease from one person who has the disease. For example, measles is highly infectious with a R₀ of 18, meaning that for every 1 person infected, 18 more will be infected. And, of course, each infected person can infect 18 more.

Let’s look at some simple product development examples applied to our world of spreadsheets.

• R₀ of 1: Engineering change for a shared common (and critical) part across a prototype of a new product and an existing product line. The existing product line spreadsheet properly updated; prototype product spreadsheet not updated. Prototype built with the wrong part, which causes prototype failure and loss of 10 days in new product development timeline.
• R₀ of 3: Corrective action requires an update to multiple product spreadsheets for product line variants with a rework plan for current inventory and new supplier part cut in. The team misses updates to three of the spreadsheets. Consequently, the team orders the old part at volume for one of the product line variants (instead of all three, which would be unrealistic timing), increasing rework costs, and delivery time.
• R₀ of 10: If you are doing cost calculations for changes or new product development in spreadsheets, a copy-and-paste error for a common part across multiple product lines can be magnified quickly. In this example, the error gets perpetuated to 10 product assemblies, resulting in an undercalculation on cost of goods at volume.

We use these examples because the costs of failure are individual to your company, product details (e.g., complexity, volume, number of product lines), rate of product changes, team sizes, and, of course, how your spreadsheets are set up and managed. As you can see, even a 1% cell error rate can have substantial impacts when a spreadsheet ecosystem is “unhealthy.”

In summary, spreadsheets have evolved significantly from paper-based ledgers to collaborative, cloud-based programs that offer features ranging from database integration to mobile apps. Although the latter provide ways for teams to seamlessly work asynchronously and remotely, desktop-based spreadsheet programs like Excel reinforce silos and make your organization vulnerable to mistakes from within and threats from without (check out our Cost of Mistakes calculator to see the impact of common mistakes). And, managing this critical data in a way that cannot be verified can lead to a toxic mistrust that spreads across your organization, resulting in bad attitudes toward processes and, therefore, lower-quality work. So, if you’re currently exclusive with spreadsheets, this may be the time to consider other options.

[1] Levy, S. (2014, October 24). A spreadsheet way of knowing. Wired.

[2] Gislason, H. (2017, October 20). 3 things you don’t understand about spreadsheets. Medium.

[3] Brethour, P. (2003, June 4). Human error costs TransAlta $24-million on contract bids. The Globe and Mail.

[4] See the following more discussions on spreadsheet error rates and types of errors.