If you store data sets of your small or medium business activities in Google Sheets, can you consider it as a database? It’s a tricky question because Google Sheets is a spreadsheet app. Meanwhile, a database is usually associated with MySQL, MongoDB, BigQuery, and so on. However, many users don’t see the fundamental difference between a spreadsheet and a database. In this article, we’ll dot all the “i”s for you so you are not confused by these notions and can make the best choice for your needs.
What is a spreadsheet?
Let’s go from simple to complicated. A spreadsheet is a file that contains tabular data – textual and numeric data arranged in rows and columns.
A spreadsheet app or program is a software that allows you to manipulate the data in a spreadsheet. The most famous spreadsheet apps today are Microsoft Excel and Google Sheets. Here is what their competition looks like in Google Trends.
This polarization between the two apps caused numerous confusions about spreadsheet terminology. For example, a spreadsheet in Google Sheets is a Google Sheets doc; a spreadsheet in Excel is a workbook. A spreadsheet may contain multiple sheets (Google Sheets) or worksheets (Excel), which some users also call tabs.
What do you use spreadsheets for?
Spreadsheets have a wide range of uses: from basic mathematical calculations to complex business analysis. Here is a list of the most common use cases:
- Data visualization via graphs and charts
- Storing and sorting data
- Data modelling
- Finance planning
- Business accounting
- Inventory management
- Time tracking
- Predictions and simulations
- Analytical activities (break even analysis, statistical analysis, etc.)
- Accumulation of data from different sources
- And many more
Spreadsheet apps to consider
Google Sheets and MS Excel are not the only spreadsheet apps on the market. Here some other options that you may consider:
- Apple Numbers
- Polaris Office
- WPS Office
- Simple Spreadsheet
- And others
How much data a spreadsheet can contain
Each spreadsheet app has its own data limits. Microsoft Excel has the following limits:
- Up to 1,048,576 rows
- Up to 16,384 columns
In total, an Excel Worksheet can have 17,179,869,184 cells considering other limits. Google Sheets limitations are up to 5 million cells or 18,278 columns (column ZZZ).
If you think that these figures are Brobdingnagian :), then you can probably make do with spreadsheets for your activities. However, more data requires a more performant storage, which a database truly is.
What is a database?
A database is a computer system that stores data arranged in tables with rows and columns, as well as documents or files. However, in some databases, rows can be named records, and columns can be named fields. Here is how the data in a database may look on the example of Google BigQuery:
Database management system (DBMS)
The software to create a database and manipulate data in it is called a database management system (DBMS). It is an interface that allows users to query data, manage tables, and perform other data manipulations within the database.
Do I need a database or a database management system?
These terms go hand in hand. To set up a database and manage it, you’ll need a software – the database management system. So, quite frequently a database means a DBMS and vice versa.
For example, to set up MySQL, one of the most popular open-source databases, you need to download and install the MySQL software, which will let you configure the database server.
When do you use a database?
A database is designed to store vast volumes of configured data. This is why large companies, organizations, municipal departments and other entities opt for databases. So, the volume of data is the major criterion. Others can include the following needs:
- Easier control and access to your data (it’s easier to navigate one database rather than a number of spreadsheets)
- Logical organization and/or separation of different types of data
- Calculating and manipulation of large sets of data
- Access to data for concurrent users
- Building a dynamic data-driven app
- Enhanced data protection
How much data you can store in a database
A database is composed of files, which store the data. So, there is no limit on the number of tables, but databases may have a limit on the number of files that represent tables. A database can grow as large as the available memory that stores it.
In terms of performance of a database, you should take into account a number of criteria, such as memory usage, CPU usage, disk space, server resource limits, type of hosting, etc.
Types and examples of databases
You can classify databases by data model, number of users, types of access path, database distribution, and other criteria. Let’s introduce the most frequent types of DBMSs and their examples:
|Relational DBMS||The data is stored in the form of tables||MySQL|
|Object-oriented DBMS||The data is stored in the form of objects||ObjectStore|
Versant Object Database
|Object-relational DBMS||Hybrid of relational and object-oriented DBMS||Oracle|
Microsoft SQL Server
|Hierarchical DBMS||The data is organized into a tree-like structure||IBM Information Management System|
|Data warehouse||Data from multiple sources is combined into one comprehensive database||BigQuery|
Amazon Web Services
|NoSQL document/JSON DBMS||The is stored as document collections, usually using the XML, JSON, BSON formats||MongoDB|
Now, let’s compare spreadsheets and databases.
Similarities between databases and spreadsheets
The only similarity between these two notions is that both spreadsheets and databases can store data in a tabular format. Numeric and textual values are arranged across columns/fields and rows/records.
A spreadsheet is a table or a collection of tables, so is the database. However, databases can store a huge number of tables compared to spreadsheets. This is the main difference out of many more, which we’re going to discuss below.
Database vs. spreadsheet: key differences
You can use spreadsheets to store and manage large quantities of raw data. However, the processing time goes down when the data volume goes up. Besides, you can bump into the spreadsheet limit.
With databases, your main issues may relate mostly to the performance restrictions rather than data volume limitations.
Data storage and manipulation
Spreadsheets are tables consisting of rows and columns. A cell is the confluence of a row and a column. Spreadsheets store data values in cells that can refer to other cells.
Databases store data values in tables, which are the simplest objects (structures) for storing data. Basically, a database is a collection of tables. All the data has to be stored in a standardized manner. Rows in databases are called records. A single record includes a value for each field in a table.
Data integrity as a process denotes the rules that ensure validity and accuracy of the data in the database. These rules may include unique column names, primary key values, and other logical integrity. For example, you can’t delete a row that has any references in other tables. This is one of the main differences between databases and spreadsheets.
Databases validate the data you’re going to enter and can reject the value if it doesn’t correspond to the type of the data in the column. This saves you from typos and errors.
In Google Sheets, you can set up data validation as well. For example, you can assign textual values only to a specific column. However, you will still be able to enter wrong data (although you’ll get a warning about this). Document databases can also allow you to enter any type of value. At the same time, you can set up the database structure in a way that will make it impossible to input wrong values.
Data filtering and querying
Contemporary spreadsheet apps let you filter and even query data, for example using the QUERY function in Google Sheets. However. this is implemented in a rather cut-down way compared to the querying in databases.
In databases, you can make specific and fast queries without affecting the data using SQL (Structured Query Language). Basic SQL for structured databases is mostly the same (clauses SELECT, FROM, WHERE, etc.), but there are differences in functions and their variety. For example, the LAST_DAY() function, which returns the last day of the month for a selected date, isn’t supported everywhere. In NoSQL databases, you can fetch data using the query format of this specific DBMS.
Databases store and output data in a tabular format. You can’t change the format or somehow visualize the data.
Spreadsheet apps, on the other hand, provide you with lots of options for determining the formatting, appearance, and structure of the data.
So, if you want to make a report of the data from your database, you’ll need to either use data visualization tools, which are connected to the database, or import a chunk to a spreadsheet app. For example, here is how you can import data from BigQuery to Google Sheets.
Database management systems let multiple users access and edit data. However, two or more users cannot alter the same data values at the same time. DBMSs keep logs of every change, which maintains the integrity of the data.
Google Sheets can do this as well. However, not all spreadsheet apps can boast such a level of shareability.
Spreadsheet apps are indisputably beginner friendly. They are designed with user focus in mind. The basic features are clear and won’t require additional learning.
Databases are more difficult to use and require a higher level of technological expertise, including the SQL.
Database vs. spreadsheet: advantages and disadvantages
Let’s sum up the differences between databases and spreadsheets to reveal their pros and cons.
|Large volume of data|
Consistency and integrity of data
Data redundancy control
Shareability and multi-access to data
Filtering and querying
Backup and recovery of data
|Steep learning curve|
Cost of setup and maintenance
Cost of staff training
Cost of converting data files into a database
Cost of querying
|Free or low cost|
Shallow to medium learning curve
Data formatting and visualization
Preset function for data manipulation
Poor performance with large volumes of data
Database vs. spreadsheet: comparison table
And here is another table, which accumulates the key points of comparing the terms spreadsheet and database.
|System that stores data arranged in tables||What it is||File that contains tabular data|
|Storage of large volumes of data||What is it for?||Different data manipulations such as reporting, analysis, etc.|
|Requires technological expertise||Learning curve||User friendly|
|Limited by the number of files with tabular data||How much can it store?||Limited by the number of cells|
|Advanced querying and filtering options thanks to SQL||Query||Limited querying and filtering options|
|Data is stored and output in tabular format||Formatting||Multiple formatting and data visualization features|
|Pretty expensive to set up and maintain||Budget||Free or low-budget solution|
|Organizations, departments, companies, and other entities with huge volumes of data||Target user||Accountants, marketers, teachers, small business owners, and many other categories of users|
Examples of when to use spreadsheet vs. database
Episode #1 – Spreadsheets as a database for your business
New business, new start. You’re dreaming about a long list of customers and the day when your small company turns into a huge corporation. First sales, profit and loss, feedback from customers, and other data go into a spreadsheet. This will probably be a free solution like Google Sheets, and here is how to use Google Sheets as a database. However, you may also pick an alternative spreadsheet app, which won’t cost much of your budget. This is a time when spreadsheets are the best choice for your data, and here is why:
- Your business has a flexible structure that will change from time to time. Unlike a database, a spreadsheet app is a flexible solution that you will benefit from.
- Cost saving is what most startup founders and green business owners focus on. A spreadsheet app can be free or not too expensive.
- Spreadsheets provide built-in capabilities that you need. You’re unlikely to need to perform any complex manipulations with your data. Spreadsheet software can satisfy your calculation and filtering needs perfectly.
You can store historical data, track metrics, make calculations, and perform other data manipulations that are required for your business to grow. Spreadsheets can do the job.
What issues you can face with spreadsheets
Although your experience with spreadsheets has been great, you begin to spot minor issues that could snowball:
- Data quality issues: your relatively historical data can get error information such as duplicated entries, mistakes in values, and others. It’s not a big deal to fix them when the data set is small. However, you may experience problems when you work with larger volumes of data (in order to mitigate such risks, we suggest you think about the needed information structure and apply data validation).
- Spreadsheets performance issues: filtering or querying data doesn’t go smoothly anymore since you have to work with much more data than before (tens/hundreds of thousands or millions of rows).
Are you ready for a database?
Historical data increases in volume, and you have to use more and more separate sheets to organize the data properly. One day, you will understand that spreadsheets are no longer what you need. Some SMBs decide to upgrade, but they are not ready to use a database yet. So, they opt for a spreadsheet-database hybrid, such as Airtable. It provides features of a database, but applied to a spreadsheet. Meanwhile, they use it along with spreadsheets:
- Airtable or an alternative for storing historical data.
- Spreadsheet app for reporting, calculating, tracking, or anything else.
To query data in Airtable and import it into a spreadsheet, they use a dedicated software. For example, Coupler.io lets you automatically import data from Airtable and other sources into Google Sheets on a schedule.
Such a combination of apps can work well for quite a long time. But, eventually, you’ll come to the point where a database is inevitable.
Episode #2 – Time to switch from spreadsheet to database
There is no need to switch from spreadsheets or Airtable-like software if your business is running well with them. However, when your company becomes data-driven, this requires you to scale up your data processing. In this case, you should consider either a simple database, such as MS Access or BigQuery, or a customized one. This will provide you with the following benefits:
- Most data quality and performance issues will be minimized due to the database’s built-in controls
- You will fully control the access to your data
- You’ll be able to manipulate much larger volumes of data
Don’t say goodbye to spreadsheets
The fact that you switched to a database doesn’t mean that you should get rid of spreadsheets in your flow. On the contrary, you can benefit from both solutions, but for different needs. For example, you can query specific chunks of data in the database and export it to spreadsheets for reporting or data visualization. Databases and spreadsheets can provide a synergistic effect.
To sum up: spreadsheet or database?
Spreadsheets are an option for starting businesses and companies that are not data driven yet. They are still at the beginning of the journey and data plays a secondary role.
When the role of data grows and begins to affect the entire scope, it means that it’s time to go beyond spreadsheets and consider setting up a database. This will let you organize data with advanced options for data querying, sorting, and manipulation.Back to Blog