Sometimes a spreadsheet isn't enough. Sure, they're quick to create, but they have limits—and those limits have consequences.
In the UK, for example, over 15,000 people infected with COVID-19 were left out of infection counts and contact tracing efforts because the spreadsheet used to track infections ran out of space. The BBC reported:
...developers picked an old file format to do this—known as XLS. As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.
This is extreme, obviously. Your business may not be working on anything this wide-reaching, and you (hopefully) aren't using a file format from the 1990s. But spreadsheets have inherent limits, and that's worth thinking about—especially if you're using a spreadsheet as a sort of improvised database.
If you decide to switch from a spreadsheet to a database, you can still automate a lot of your work in the app. Here are 3 workflows to help you get the most out of your database.
The limits of spreadsheets—and why databases work better
Spreadsheets are no longer limited to 65,000 rows, but there's still a limit. According to Microsoft, Excel limits the total number of rows in a spreadsheet to 1,048,576 and columns to 16,384. And there are a few other maximums to keep in mind, some of them weirdly specific.
Cells can contain up to 32,767 characters.
Each worksheet can include up to 65,530 links.
The earliest date you can set for calculations is January 1, 1900.
The furthest date in the future you can use for calculations is December 31, 9999.
Charts can pull in data from up to 255 worksheets.
Google Sheets also has limits: a spreadsheet can have ten million cells, total, and you can't import cells with more than 50,000 characters.
The vast majority of spreadsheets you make will never come close to these numbers. But some will, and it's probably a good idea to think about switching to a database long before that for all sorts of reasons. Let's go over a few of the differences between databases and spreadsheets.
Spreadsheets show you everything, all the time
Open a spreadsheet, and you will see everything—at least, everything on the current worksheet. This is great, sometimes, until your dataset grows too much and your computer grinds to a halt. Assuming you don't own a supercomputer, this will happen long before you hit the limits outlined above—especially if you have a bunch of formulas and scripts running.
Databases, meanwhile, store data behind the scenes. They will only load what you need when you request it, meaning you won't hear your laptop's fan whirring every time you need to pull up a specific record. If you're past the point of being able to get anything valuable out of looking at your sheet, you should probably set up a database for the job.
Spreadsheets aren't great at filtering or querying
Spreadsheet apps offer some filtering and querying, but there are caveats. Seeing data in a particular order, for example, typically requires making changes to the sheet itself.
Databases, meanwhile, allow for really specific and fast querying and reporting without actually affecting the data. This is a much cleaner way to pull up data. If you need to arrange data in different ways, on a regular basis, a database is the better choice.
Using Airtable as an example, here's why you should stop using spreadsheets as improvised databases.
Typos are a thing
Spreadsheets don't offer data validation by default—that is, when a column is first added, you can type whatever you want wherever you want.
Let's say there's a column in your spreadsheet for recording someone's age. Unless you've anticipated this and set that column up with validation, I could write "blue," and nothing would stop me. You probably don't know anyone who's blue years old—I don't. But the spreadsheet doesn't know that.
Databases, meanwhile, allow for data integrity: the system checks whether data I enter makes sense or not. Unlike spreadsheets, this is built into the setup itself. When you add a column to a database, you include the expected data type and limitations along with the column name. In the case of age, this means verifying the data is an actual number. This can save you a lot of headaches.
Databases are better at making connections
This is a little technical, but spreadsheets are kind of static. Every spreadsheet, and each worksheet, are more-or-less separate from one another. You can kind of change this with formulas, pulling in information from other places, but that doesn't change the fact that these are separate entities.
Databases, meanwhile, are usually relational by default. This means you can update a piece of data in one place, and it will automatically update everywhere else.
The right tool for the right job
None of this is to say that spreadsheets are bad. They're not. They're just not an alternative to an actual database. If the UK government had used a database instead of a spreadsheet to track COVID cases, they probably wouldn't have run into the problems they did. That's a lesson your business can learn from.
So if you're working with a lot of data and need to make regular connections, it's a good idea to look into setting up a proper database. That could mean hiring someone to set up MySQL, or it could mean trying out a user-friendly database like Airtable.
Read more: Airtable vs. Google Sheets: Which should you use?
