Do you live and breathe spreadsheets, 24/7?
You may be limiting yourself. If you’re handling large quantities of data, switching to a relational database could bring some advantages.
How Are They Different?
Spreadsheets store data values in cells represented in a system of rows and columns, and these cells can carry out processing on the cell values.
Databases usually store data values in tables, with each table having a name and one or more columns and rows. A record is the data value for each column in a table, and databases can enforce relationships in records between tables.
Relational databases basically equate to a server that stores your data and a mechanism for querying it. SQL, which stands for Structured Query Language, is a programming language used to create and modify relational databases, and is one way of querying data.
Excel is useful for a lot of reasons – it’s easy to use, it’s structurally flexible, it’s easy to manually enter and edit information, it’s easy to browse data, it integrates with other office software, you can use formulas, and has helpful built-in functions like spell-check.
According to Neil Pepi, Business Intelligence Developer and co-author of Real SQL Queries: 50 Challenges: “The most basic reason for using SQL Server over Excel is that SQL Server doesn’t have software limitations on how much data you can use. The only limitation is the size and performance capability of your server(s). Whereas, given the average computer, you might find Excel crashes with datasets past 500,000 rows.”
There are other pros and cons of each. Let’s take a look.
Structure vs. Freedom
With Excel, you can put any data type in any cell. While this offers complete freedom, it also means that an Excel table is not inherently well-structured. For example, the column header might say “Transaction Date” and it might look like that column is full of numerical dates, but you actually have a value of “December the 17th” on row 5,000.
The benefit of Excel’s freedom is that you can present data in a way that is less structured, but useful for the person who’s receiving it. For example:
SQL Server offers no formatting capabilities. In SQL Server, every table is made up of columns (or fields) and every field has a data type. So, if you have a field with the datatype of DATETIME, you know that it’s impossible to have “December the 17th” hidden somewhere in that field.
Relational databases are focused on the relationship between items, and force you to store things in a consistent, logical manner.
Databases, like SQL Server, therefore promote greater data integrity and prevent data corruption.
Analysis and Processing
SQL Server has more robust tools for analyzing and processing data. For example, testing the relationship between two data sources. While it’s technically possible in Excel, it’s a lot easier in SQL Server. And, once you’ve tested that relationship, SQL is more capable of joining together data sources, especially of large volumes. Excel is not very useful in working with multiple datasets at the same time, or supplying detailed answers to questions about your data.
The only advantage Excel has for analyzing data is the Pivot Table. The Pivot Table is a powerful tool where you can aggregate data quickly with a drag-and-drop interface. In SQL Server, every task requires some amount of writing code. So in some situations, the Excel Pivot Table can be a faster way to analyze data.
SQL Server has no data visualization capability. Excel allows you to visualize and present your data in a variety of formats.
If you have data that requires multiple people being able to access it, using SQL Server can help you avoid that pesky “this file is locked for editing” message.
Unlike Excel, everything in SQL Server is consolidated and searchable. You can write a script to search through the text of all the procedures on the server. While you can write macros in Excel that serve a similar function to SQL procedures, you can only search through the macros housed in Excel files that you currently have open.
SQL Server also allows you to search by table names, field names, datatypes, basically anything you can imagine. And, SQL provides easier and more secure access for multiple people to modify the data.