When you need to store data, should you use a Spreadsheet or database?
Easy to learn – minimal skillset required
Data is very visual and easy to understand
You can just start typing data straight away
Excellent for “What If” modelling
When you have thousands of rows you will start to struggle.
You cannot share and distribute data easily
Minimal data integrity – anyone can change validation rules on the fly
Data is non-relational
Every user needs a licensed copy of the spreadsheet software
Data can be stolen via email or USB stick
Data can be lost if file becomes corrupted
Built to handle large volumes of data at speed
High level of data integrity
Multi-user access, hundreds/thousands of users can update data in sync
User defined interfaces to guide users into correct data processing
Data is structured and can be linked into relationships
Data updates automated streamlined and if required batched
You can create a full Audit trail of all user actions
Data can be locked into a secure server and can’t be stolen at desktop level
Flexible connectivity options for querying, reporting and analyzing
Clients can update database from PC, Web Browser or App
Specialist skillset required, or a lot of time invested for self-training
Structured data sets take time to set up
Common issues attributed to spreadsheet use.
Some formats have a row limitation of 65k rows, this came to light recently when the UK government tries to record COVID cases in a spreadsheet and got it drastically wrong.
As people can’t amend the same spreadsheet at the same time, clone copies are created and often confusion then ensues as one cannot tell where the latest data is or a master spreadsheet has been consolidated without error.
Sorting errors – it is very easy to sort a single column with our sorting the whole rows, this effectively mixes up data randomly.
As spreadsheets get bigger they also get slower and if you save your data and overwrite the same file you will lose your data if that save is interrupted for any reason.
People see Spreadsheets as Database alternatives as Spreadsheets contain tables and rows, with each column being a separate data field. However that is where the similarity ends. Databases normally have multiple tables all related together. The data is manipulated and controlled by the database client software where as spreadsheet data is manipulated simply by the users keyboard.
Databases are more common than most people realize. Nearly every system, even a company accounts system uses a database as its data engine. Such systems use tables with relationships, for example items ordered belong to a master order record. Each order is then linked to a single customer record.
To set up such a structure of related data a database is required. This cannot be done robustly within the spreadsheet environment.
Spreadsheets can be easily set up for low volume casual datasets. However, these are single user (two people can’t change the same dataset easily at the same time) datasets and are only suitable for several hundred records. If you need to store thousands of records and share them with simultaneous users, a database is recommended.
If you have thousands of records and/or the data is critical to your business use a database.