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.
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.