Which of the following allows user to organize and manipulate data arranged in rows and columns?

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:

• Budgeting
• Data visualization via graphs and charts
• Storing and sorting data
• Data modelling 
• Finance planning 
• Business accounting
• Inventory management
• Time tracking
• Invoicing
• Surveying
• Payroll
• 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
• Smartsheet
• LibreOffice
• Polaris Office
• OfficeSuite
• 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:

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

Data volume

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

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. 

Data formatting

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.

Data shareability

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.  

Domain knowledge 

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.

Databases

Spreadsheets

Database vs. spreadsheet: comparison table

And here is another table, which accumulates the key points of comparing the terms spreadsheet and database.

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 to Google Sheets on a schedule. You can variate data sources (Pipedrive, Shopify, etc.) and data destinations (Google Sheets, Excel, BigQuery) to meet your project requirements.

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