Let's think about when you should not be using spreadsheets (because they aren't well suited to every task and situation).

Firstly, let’s discuss some of the disadvantages of using spreadsheets.

Errors

It is estimated that over 90% of spreadsheets have an error (I’m unsure of the source of this tidbit but I read it often). Due to the skill of users, human error, difficulty checking every cell in a massive spreadsheet and many other reasons, errors are common. These could be errors with the formulas, errors where the data has accidentally been overwritten or errors with the logic being used.

Difficult to test

One reason for the large number of errors could be how difficult it is to test spreadsheets. The testing is largely manual in most cases, and again depends on humans setting the logic and testing the tests have worked properly.

Not secure

Spreadsheets can be password protected, locked for editing to certain users and set to view or comment only as a way to secure the spreadsheet. They can also be shared attached to an email or in a messaging app, and if it is an online version, they can be shared with a link.

(Non-online version) not collaborative

Unless you are using Google Sheets, or Excel Online, spreadsheets are not made for multiple people to be editing them at once. Saving many files ends up with something like this:

my_spreadsheet_version5.6.xlsx
my_spreadsheet_version5.7_MOST_RECENT.xlsx
my_spreadsheet_MOST_RECENT.xlsx

I’m vaguely joking, but some sort of system is required. Still people will work on the wrong version from time to time, and work will be lost or need repeated. Online versions definitely allow for collaborative working.

Limited charting abilities

Last week I discussed how easy it is to make charts in spreadsheets. I stand by that. However, the charts which can be made are limited — both in the type of chart and how much you can edit them. Despite making charts in Google Sheets being very easy, it bugs me (probably more than it should) that I cannot easily make the bar thicker on bar and column charts. I hate those skinny lines… In a previous job, I often built dashboards in Excel. I spent hours perfecting the charts, the tables, the interactivity (e.g. changing the company or the currency or some other setting). While I got the desired results, the effort and overthinking required is not something I would wish on anyone (the end results was so satisfying though). There are other options — if you can code, Plotly in both JavaScript and Python, is a great choice to make charts and dashboards. Most business intelligence and data platforms have built in dashboarding abilities — Looker, Google Data Studio, Quickview to name a few.

This is not to say spreadsheets cannot be error-free, tested, secure, collaborative and have beautiful charts, it just takes a lot of work, know-how and usually some backend coding to achieve. The effort to achieve and maintain a spreadsheet (or sheets) eventually outweighs the effort to maintain a database with a pretty frontend portal. Spreadsheets can then be used as intended, and your data (and people) will be a lot happier.

Now, let’s look at situation when you definitely should not use spreadsheets (I’m sure there are exceptions to the rules but using spreadsheets in the following ways contributes to the bad reputation spreadsheets have gained):

BIG data

When you have a lot of data, it should not be in a spreadsheet. It needs somewhere better suited for the data to be sorted and processed.

To replace a database

Spreadsheets are not a database. Many try to make it so. It is frustrating, and potentially costly (errors, time to fix) to use a spreadsheet as a database.

When the users aren’t competent

If the users of the spreadsheet do not have the skills to use spreadsheets effectively, you are much better using another tool. One with different user permissions to keep the data safe, and provide a nice, not-so-frustrating way to interact with the data.

Too much complexity

As soon as your formulas (or macros) get complicated, it’s time to look at using something other than spreadsheets. If they are one off formulas or pieces of work, fine continue with spreadsheets. Otherwise, start thinking about having an automated data pipeline to try and minimise human errors.

Data type doesn’t make sense

Last time we talked about CSV (comma-separated variable) files lending themselves nicely to spreadsheets. There are some common file types (JSON files spring to mind) which do not make sense to be opened in spreadsheets. The amount of work to make the data useable is not worth it.

Hopefully, this has made you think about when to use and not to use spreadsheets (and it hasn’t put you off using spreadsheets forever). Spreadsheets can be great, useful and easy but only when used right. They are also an amazing way to learn about data and logic — skills needed in any data job. Let’s make sure we’re using spreadsheets properly, so they get a chance to redeem themselves from their current bad reputation.

When Should You NOT Use Spreadsheets?