ELI5: is a spreadsheet a database?
There’s an old adage in the software industry that, for any kind of data-related service, your biggest competitor is a home-grown spreadsheet. And one of the more prevalent ways in which custom spreadsheets compete against specialist services is in the housing of company data.
UK readers might remember the Department of Health and Social Care coming under fire for a costly COVD-19 track-and-trace system that depended on an Excel spreadsheet. But beyond the headlines, some 51% of surveyed professionals still use spreadsheets for most of their processes.
But is a spreadsheet a database? Like databases, spreadsheets make use of a tabular structure. And like databases, they power the collection, management, analysis, and general use or manipulation of data.
But are the two technologies really interchangeable?
What is a spreadsheet?
Is a spreadsheet a database? Let’s start by looking at what a spreadsheet does.
A spreadsheet is a computer application that enables users to organise and analyse data. Think Microsoft Excel or Google Sheets, for example.
Spreadsheets consist of multiple ‘cells’ organised into a system of rows and columns. (i.e. table form.)
Spreadsheets are simple to understand. They’re great for numeric or text values, and offer functionality such as performing mathematical calculations.
What is a database?
A database is a computerised system designed to store large amounts of raw data. They also facilitate the retrieval, maintenance, and manipulation of this data.
Databases store data in tables. Each row in these tables is a ‘record’. Databases can then enforce (store and show) the relationship between different records and tables. Spreadsheets cannot do this.
Databases can handle more types of data than a spreadsheet. Where spreadsheets stop at numeric and text values, databases can also handle the storage of data in the form of images and documents.
To use a database, you need a DBMS – that is, a database management system. They often require the use of structured query language (SQL) to execute queries, retrieve and edit data.
There are many different types of database, the two main categories of which are relational and non-relational.
See also: What is a database? a 500 word overview
Spreadsheets vs databases: the differences
So, is a spreadsheet a database? While there are clear similarities, the differences are core-deep. Here’s a summary of some of these differences.
- Volume of data
Databases can handle a much larger volume of data than spreadsheets. When you start to get to thousands of entries, spreadsheets can become too unwieldy.
Accessing a database generally requires the use of SQL or another query language. Spreadsheets, meanwhile, can be accessed directly by human users.
Databases can show relationships between data points and records.
The complexity of spreadsheets and databases — and the data they can respectively store — is another key difference.
See also: What is a database administrator (DBA)?
Why would you use a spreadsheet?
So, why might you choose to use a spreadsheet to store and represent your data?
Spreadsheets are accessible and understandable — making them easy to share with a team. Unlike databases, they aren’t able to have multiple editors at once, but they’re much easier to use.
- Limited amount of data
If you don’t have a lot of data to store, you don’t need a super complex way to store it — a spreadsheet is a much more accessible way to store smaller amounts of data.
- Formatting freedom
You’ll look directly at a spreadsheet to read it and gain understanding from your data. So, you’ll want it to look nice. Spreadsheets give you the freedom to format your data to your preferences. That is, you can present your data appealingly.
Why would you use a database?
If spreadsheets are simpler, then why would you choose to use a database?
Databases are more secure than spreadsheets. They’re harder to access, and they often keep records of changes. So, if someone does get into your system, you can see what they have done.
Databases are far more scalable than spreadsheets. That is, they can handle much more data, and save you from becoming overwhelmed. So, if you have a lot of data, a database is the way to go.
Is a spreadsheet a database?
In short, no. Spreadsheets and databases are complementary technologies, but not synonymous ones. Often, if you work with one, you’ll be familiar with the other.
A very basic way to think of it is to view databases as the more complex upgraded form of the spreadsheet. If you have a lot of complex data, you’ll need the more complex storage solution.