No doubt you have read many articles decrying the use of spreadsheets due to the myriad of disasters that have resulted from their use.

We have a long history of ranting about their weaknesses when it comes to complex reporting tasks like generating a set of financial statements/Comprehensive Annual Financial Report. We have even written articles about how to avoid these mistakes, and about how to determine (calculate) if a spreadsheet is the right tool for a given task. What we have never done is present the technical reasons that spreadsheets are ill-suited for the job and why database-driven applications are far superior. That is what we will do in this article.

Before we dive in, we should state a couple of items (before the hate mail comes pouring in):

• There are many spreadsheet applications & database-driven tools. Clearly, we cannot discuss each one and their differences in detail. Instead, we will speak generally about typical, common features found in products like Microsoft Excel/Google Sheets and Microsoft SQL Server.
• Spreadsheet applications have gotten increasingly sophisticated, have remained inexpensive and easy to use, which has naturally lead to their use for more complex tasks.
• If left to choose between using a spreadsheet or paper & pen, we would recommend the spreadsheet.
• Some of the problems people have with spreadsheets occur because they fail to follow best practices. Thus, some of the highly publicized disasters reflect a skill/experience problem, not a software problem.
• We are active proponents that just because you CAN use a tool for a task, does not mean that it is the BEST tool for the job.

Similarities

Spreadsheets provide a rapid, powerful, generic way to manipulate numbers in any form or fashion your heart desires. The intersection of columns and rows allows you to enter data or formulas to generate a wide range of results. 

A database-driven application extends the concept of a spreadsheet.  A very rough comparison is that each “tab” in a spreadsheet workbook is equivalent to a database table, with each table consisting of rows and columns. A database is a collection of these tables and other metadata, just like a spreadsheet is a compilation of sheets. To be clear, we are using the term database here as it is the common expression. 

Differences

Spreadsheet weaknesses

The argument for continuing to use spreadsheets is obvious - they are inexpensive and easy to use at a basic level. 

The problem with them is considerable however and can be seen in a common theme running through the comparison above.

The major technological problem with the spreadsheet is the collapse of many elements into one sheet or even down into one cell. In a spreadsheet, the "data table" IS the report. Entering new "transactions" means being able to edit existing rows. This makes the large, complex spreadsheet very fragile. Make a mistake entering a formula and everything breaks. It is like a tower of Jenga blocks waiting to fall.

Some of you may be typing a furious email to me right now saying "You can solve these issues in a spreadsheet. Just design it like a database and use all of its features, like macros." It is true, some of these issues can be handled IF you design your spreadsheet correctly and have extensive IT and programming skills; essentially, if you build an application inside your spreadsheet.

Thus the argument for continuing use of spreadsheets for complex tasks is not compelling for 3 reasons:

1. Untrained operators - It is great to say "develop the spreadsheet properly" but consider the typical spreadsheet developer. They are often accountants/finance professionals. Because anyone can use spreadsheets & they are so easy, people with no particular development training build incredibly complex documents. Consequently, these documents rarely conform to the best practices that an experienced developer would insist upon. 

   In general, errors seem to occur in a few percent of all [spreadsheet] cells, ...In programming, we have learned to follow strict development disciplines to eliminate most errors. Surveys of spreadsheet developers indicate that spreadsheet creation, in contrast, is informal, and few organizations have comprehensive policies for spreadsheet development. 

   Ray Panko - What We Know About Spreadsheet Errors
2. Too little control - Spreadsheet tools are just not designed to enforce or in some cases even support best practices. One example of this is that many elements are combined/mixed together within each spreadsheet, sometimes even within one cell. Recall, spreadsheets are a generic tool designed to fit as many use-cases as possible. Thus, the user must:
   1. Know they need a given feature (like validation),
   2. Have the skills (which might include programming) to build it,
   3. Spend the time to build it,
3. Technical Inability - Even if you have tremendous IT skills some actions are very difficult bordering on impossible within a spreadsheet:
   1. Managing large data sets - if you need to deal with millions of records, spreadsheets can not help you. 
   2. Logging - It can be very difficult to determine which user added row 7 in our example. Or if you are dealing with thousands of rows, which rows were added and when?
   3. Documentation - if the person who created the spreadsheet leaves the organization, who else in the organization will know how to use what is essentially a custom application? There is no easy way to direct subsequent users where to start etc. other than some notes that you hope are updated and then followed by subsequent users.

Database weaknesses

All the power, validation and speed of a database driven solution comes at a cost. Depending on the exact database selected, the costs can be significantly higher than a spreadsheet and specialized hardware may be required.

Further installing, configuring and maintaining a database takes specialized knowledge.

Finally, one of the benefits of the database-driven application is one of the challenges- it constrains the user. In contrast to the Jenga metaphor of the spreadsheet, the database application can be thought of like a Rubiks Cube. The user can only do certain things in certain ways. Unlike the spreadsheet, there are rules about how the system can be used.

We have previously developed a detailed calculation to determine exactly when to use a spreadsheet but if you want to keep it simple, here is the cheat sheet for choosing between the two tools:

• simple,
• only used by one or two people,
• using a small set of data which
• will only be used once. 

• complex,
• used by multiple people,
• standardized,
• may house a moderate to large data set and 
• reoccurs.