SQL and Excel: Why you need both

SQL vs. Excel: which is better?  As you can see from the title of this article, it’s not the case that you should choose one over the other; you actually need SQL and Excel.

Introduction

Excel is a really great application.  There are not many applications that are used by such a wide variety of users (from beginner to expert) for such diverse uses across so many different sectors.  A few years ago, Microsoft estimated that 1 in 5 working adults worldwide use Excel!  People often talk of its demise, and that it will get overtaken by newer, better solutions, but this has yet to happen.  Although 35 years old, Excel is still going strong and with new improvements being added regularly, it is likely to retain its important role in the workplace for years to come.

However, even Excel’s biggest fans (myself included) acknowledge that it’s not always the right tool for the job – just because you can do something in Excel, it’s not always right that you do.

The areas that Excel generally falls short are those where relational databases excel (pun intended!).  There are several relational database management systems (RDBMS) out there, and most of them use SQL (Structured Query Language) to manage them.

In this article, we’re going to look at some of the shortcomings of Excel and discuss how SQL (and RDBMS) can fill the gap.  By the end of the article, you’ll see that using both SQL and Excel for your data management solutions is the optimum approach.

Excel: what are the disadvantages?

The table below shows some of the pros and cons of Excel.  This is not an exhaustive list, and I’m sure you could come up with a few additional items.

For those that use Excel, the pros are well recognized and are the reasons that it is so widely used.  Not all users will be familiar with the cons, so let’s look through these in more detail.

Not inherently secure

Excel gives you the ability to protect your workbooks and worksheets.  This could be to prevent someone from opening a workbook without a password, granting Read-Only access to a workbook, or even just protecting a worksheet so you don’t inadvertently delete any formulas.  But these are not intended as security features and you should not assume that the information in your spreadsheet is secure.

There is also the question of more granular access to information in spreadsheets.  Let’s say I have a spreadsheet that is used to prepare my organizations budget, and there is some sensitive information that should only be visible to some.  I’d need to create separate spreadsheets and then merge them later.  From a security and privacy perspective, the correct way to approach this is through a roles-based and data content set of rules

Susceptible to trivial human errors

We’re all familiar with this one, and there are enough horror stories out there highlighting human error for some costly mistake.  Although there are a number of spreadsheet best practices that you can employ to minimize them, the risk of human error is high with spreadsheets.

Difficult to troubleshoot & test

In just about every workplace there is some application, system, or more commonly, a huge complex spreadsheet that only one person knows how to fix.  The powerful features in Excel make it possible to create some complex spreadsheet applications that can do amazing things.  I think we’re all guilty of not always structuring and documenting these kinds of spreadsheets to make it easy for someone else to troubleshoot.

Not designed for collaborative work

Although Office 365 makes it easier for users to access shared documents, it doesn’t necessarily solve the problems of multiple people updating a single spreadsheet.  Let’s take a sales pipeline document as an example.  Do all the users know what they’re supposed to be filling in for each field?  Has data validation been set up (what if a new prospective customer needs to be added)?  Are there formulas in the spreadsheet; have they been protected from accidental change?  What if certain users need to complete their sections before other users can fill in theirs?  Put simply; Excel was never intended to be used for this kind of collaborative working.

Data and Analysis are in the same file

This example contains two main parts: a list of inventory with current prices, and a quote sheet.  This is a fairly common scenario where salespeople prepare quotes for customers from a list of items.  Often each sales rep will have a local copy of the workbook which they use.  The problems arise when the inventory needs to get updated (new items, price changes) – you’d need to get each of the reps to update their spreadsheets with the new data.  This can be problematic.  An alternative option would be to have a centrally stored Excel workbook that contains the inventory table.  The sales reps can then use the ‘Get Data’ function in Excel to retrieve the data for use in their quotes.

This example highlights what is both a strength and a weakness in Excel: the data and the analysis are in the same file.  This is very handy for small simple stand-alone solutions, but as soon as we need to work on bigger solutions with multiple users, it becomes a problem.

Can’t handle large datasets

Here’s another common example: your manager needs a summary of product sales by category by month for the last year.  Easy! Get an export from the sales financials system, open it in Excel and create a pivot table/chart.  Yes, it is that easy, and we’ve all done it lots of times, and it works very well.  But what if the volume of sales data is really big?  Excel could probably handle 1 million records, but with not much to spare; and the file size would be large.  Bigger than that, however, and Excel can’t be used; it is unable to handle large datasets.  It’s worth pointing out that Excel was never really designed for handling large datasets, although the addition of PowerQuery/PowerPivot makes it possible through some pre-processing functions.

Not built with Business Continuity in mind

If disaster struck your business and you lost access to your office through fire, flood, or some other disaster, how long would it take you to be back up and running from an alternative site?  Would you have access to all of your data and files?  According to a survey last year 51% of businesses don’t have a business continuity plan.  Even organizations with plans in place have not necessarily tested them thoroughly and the plans often highlight the importance of getting big systems back up and running quickly.  The myriad of spreadsheets that turn out to be critical once you don’t have access to them is often overlooked by the business continuity planners.  Of course, file cloud storage (and Office 365) greatly help in this aspect but is still reliant on people and processes to make sure that all the important files are stored there, and that a cloud backup solution is in place.

What is SQL?

SQL (Structured Query Language) is used to access and manage data in a relational database.  Relational database management systems (RDBMS) are the pre-eminent database technology.  Almost all of the different RDBMS flavors use SQL.  Some commonly used RDBMS are Microsoft SQL Server, MySQL, Oracle.

Data is stored in tables in a relational database. Those tables usually look like one sheet in Excel, with rows and columns.  We can use a set of instructions (written in SQL) to retrieve data and perform analysis.

Generally, when most people say, “use SQL”, they are referring to the underlying RDBMS and the language as one.

Advantages of using SQL

So why is SQL the de facto standard for databases?  The following are some of the main reasons why it’s still going strong after so many years and has 7 million users worldwide.

Built for data (volume and integrity)

SQL was specifically designed for managing data in relational databases.  It is therefore very good at handling large volumes of data.  It is also robust and data integrity is at its core.

Large knowledgebase and community

Because it has been around so long and is so ubiquitous, there are large user communities and a wealth of resources relating to SQL.

Battle-tested in many situations

There aren’t many industries or use cases where SQL hasn’t been used as a solution.  It’s also usually the case that you’d need to have a pretty good reason not to use SQL for the job.

Simple to learn

SQL is one of the few languages that non-developers can pick up quickly.  By learning a relatively small number of commands, you can get pretty far with SQL.  Of course, queries can get large and complex, and understanding the inner workings of the RDBMS takes some work.  But, learning the basics is within reach of a lot of people.

Range of security, backup, and auditing features

SQL has a wealth of features and well-established best practices to help keep your data secure, private and safe from data loss.

Conclusion: SQL and Excel are better together

Both Excel and SQL are widely used in the workplace.  In the previous sections, we’ve identified the main shortcomings of Excel and highlighted the main strengths of SQL.  Each of Excel’s main weaknesses is met by one of SQL’s strengths.

The strengths of Excel that we listed earlier and the fact that it’s so widely used mean that it’s not going anywhere any time soon.  Hopefully, by now you can see that we need SQL to work alongside Excel in the workplace.  Using SQL in conjunction with Excel will do more than just fill the gaps; it can form the foundation of your data management system and greatly enhance the way you do business.

Based on what we know about the strengths and weaknesses of SQL and Excel, we can provide the following summary.

You should use Excel if you need to:

• perform simple data entry and storage;
• quickly analyze smaller datasets;
• create simple models (eg a financial plan);
• quickly create visualizations of data;

and you should use SQL if you need to:

• store a large amount of data;
• preserve data integrity;
• process data quickly;
• store data securely;
• have an audit trail on the database.

Of course, it’s often the case that you’d need to do most or all of these things for a given need, so you need to use SQL and Excel together.  What is the best way to work with both SQL and Excel?  What are some of the best practices to make sure you get the best out of both applications?  Check out some of our recent articles to get the answers, including:

• Introduction to SQL for Excel Users
• How to Insert Data from Excel to SQL Server
• How to Update an SQL Table from Excel
• Simple Data Validation in SQL

Finally, to learn more about how SQL Spreads can be the all-important go-between for your SQL and Excel environments, download a trial.

*This article was originally published on June 17, 2021 and was updated on March 12, 2022 to include references to additional articles.

Article by

Andy McDonald

Andy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI.

Writes for SQL Spreads about Excel and SQL Server and how to tie those two together.