Spreadsheets Vs Databases

Spreadsheets Vs Databases

share:

Spreadsheets aren’t databases, so we should stop using them like one.

There are limits to spreadsheets, that’s why databases work better.

When a Spreadsheet isn’t quite enough?

Spreadsheets are quick and easy to create, but they have limits, sometimes these 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 example is extreme and your business may not be working on anything this wide-reaching.  But spreadsheets have inherent limits, and that is worth thinking about – especially if you are using a spreadsheet as a sort of improvised database.

1.    The limits of spreadsheets – and why databases work better

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 five million cells, total, and you cannot import cells with more than 50,000 characters.

Most spreadsheets you create 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.

2.    Spreadsheets show you everything, all the time

When you open a spreadsheet, you will see everything on the current worksheet. This is great until your dataset grows too much, and your computer grinds to a halt. You may have a bunch of formulas and scripts running, and unless you have a super-charged computer, it is likely to grind to a halt long before hitting the limits mentioned. 

With Databases, it all happens behind the scenes as this is where the data is stored. There is no listening to your laptop fan’s whirring, as Databases only load your specific request. At the point that you struggle to valuable data from your spreadsheet this is the time you should probably set up a database.

3.    Spreadsheets aren’t great at filtering or querying

Spreadsheets offer some filtering and querying, but there are limitations. Seeing data in a particular order, for example, typically requires making changes to the sheet itself.

However, Databases, allow for specific and fast querying and reporting without actually affecting the data. If you need to arrange data in different ways, on a regular basis, a database is a far better choice.

4.    Typos are a thing!

When a column is first added to a spreadsheet, you can type whatever you want wherever you want.  Spreadsheets do not offer data validation by default, therefore leaving room for error.

For example, if you are recording someone’s age, unless you’ve anticipated this and set that column up with validation, you could type anything in the column, and nothing would stop you.

Databases, meanwhile, allow for data integrity; the system checks whether data you have entered makes, and flag potential issues; 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 – avoiding lot of headaches in the future.

5.    Databases are better at making connections

Spreadsheets are static meaning that every spreadsheet, and each worksheet, are more-or-less separate from one another.  You can change this with formulas or by drawing information from other places, but that doesn’t change the fact that these are separate entities.

Databases are usually relational by default; if you were to update one piece of data in one area, it  will automatically update everywhere else.

6.    The right tool for the right job

Please don’t presume spreadsheets are bad; they’re not!  They just aren’t an alternative to an actual database. If the UK government had used a database instead of a spreadsheet to track COVID cases, they probably would not have run into the problems. Don’t let the same happen to your business.

Do you work with a lot of data and need to make regular connections?  If so, it’s a good idea to look into setting up a database.

Date: February 22, 2021

Author: Morris

Inspired to improve your IT? Get in Touch!

Contact Us

Check out our social media: