SQL vs. Excel: How Can SQL Server and Excel Work Together?

Are you a seasoned Excel user? In this article, we’ll look at why you would want to learn SQL if you already are an Excel user.

I was once in the same place as you. I started programming in the late 1970s and early 1980s. In the 1990s, I first started creating spreadsheets and used Microsoft Access (another type of database). However, it was over a decade later before I began to investigate SQL Server. 

Isn’t that just for big businesses? Why should you learn it? Let’s find out.

Similarities between Excel and SQL Server

What do Excel and SQL Server have in common?

• They can both store data.
• They both have a vast array of functions that you can use in formulas to manipulate the data or join multiple sets of data together.
• You can then use the finished results in your analysis.

However, the similarities stop there.

Differences between Excel and SQL Server

Adding Data in Excel

Excel is excellent as a presenting tool, but the spreadsheet application does have its disadvantages when it comes to data entry. It holds data in an unstructured format, meaning that users can put information anywhere they want in the spreadsheet. 

In the image below, data analysis would only be accurate if you store the dates in column A. Because Excel has no limitations, the user could insert dates into columns A, B, and F without Excel raising any errors.

Also, the data in a single column doesn’t need to be of the same type. For example, users could insert dates, text, and numbers in the same column, making analysis hard to do. You can also merge cells together, which may be suitable when creating presentations, but it makes analyzing data much harder. Of course, this is especially true if you are using more structured analysis techniques such as PivotTables.

Fortunately, you can use the data validation tool to stop this from happening. Data validation ensures that users are inputting the correct type of data in each column. You could also protect the spreadsheet to prevent users from making structural changes, such as Merge Cells. However, in my experience, these types of safeguards are never used.

Adding Data in SQL Server

SQL Server has strongly typed data storage. This means that each column has a single data type such as text, date, or number. It is not possible to put anything in a particular column which is not compatible with its type. For example, you could insert “1” into a text column because it can convert “1” into text. However, you cannot insert “hello” into a number column because it cannot be converted. If you attempt to do this, you will encounter an error. Additionally, SQL Server cannot merge individual cells in tables together.

Your data is immediately validated in SQL, increasing your data integrity and allowing for more reliable analysis.

Adding functions into data in Excel

In Excel, you might have 100,000 rows of data, including FirstName and LastName columns, and you want to create a FullName column. That is a very easy formula to create using a formula like the following:

= A2 & ” ” & B2

However, you’ll have to create this formula for each data row. This increases the time required to calculate the values and increases the file size, as Excel has to store these formulas.

There are ways around this in Excel. You could:

• Use more advanced array formulas
• Create calculated columns in an Excel Table
• Use Get and Transform or Power Pivot to create the extra columns

However, the default is that you have to make a formula for each of these 100,000 rows.

Adding functions into data in SQL Server

In SQL Server, you only need to add one formula. SQL stands for “structured query language,” and you can use the programming language to add this formula. For example, you could add a computed column in your table or create a SELECT statement which concatenates these columns:

SELECT FirstName, LastName, FirstName + ' ' + LastName AS FullName
FROM myTable

You can save the results of these SQL queries as Views or Stored Procedures and regenerate the results whenever you want. You could also use it in more advanced fields like data science.

Security in Excel

Excel has not got many security features. This can be a concern if you have sensitive data, such as personal or salary data.

When saving your spreadsheet, you could password protect it. This means that you need that password to open the Excel file.

However, someone can bypass this protection using password crackers, which are readily available on the Internet.

Another way of locking down Excel is by protecting locked cells in a spreadsheet. This could stop people from looking at a particular formula to see how it is calculated, or changing some of the styles or content (such as merging cells together).

While useful, this again can be turned off using password crackers.

Security in SQL Server

As a Database Management System, SQL Server has security at its core.

• To access an SQL Server instance, you first need a set of credentials. This could be your Windows login or a separate username and password.
• In order to access a database within that instance, you also need to have a valid SQL user associated with that login.

This user account will also determine what sort of access you will have to the data. For instance, you may only be able to read a certain set of tables or queries. Alternatively, you may be able to only insert data without the ability to read it.

SQL Server is also good for version control. Suppose you added some data, altered it, then deleted it. In an Excel spreadsheet, you would only have the end result (namely, that nothing had changed). In SQL Server, you could have the entire history if you wanted to track it.

Can you use Excel and SQL Server together?

If you store data in SQL Server, can you use it in Excel? Absolutely, and it is very easy to do. In Excel, you can go to the Data tab and link data from SQL Server by going to Data – Get Data – From Database – From SQL Server Database.

This can be the original data or processed data. In this way, SQL Server can process the data, and Excel can then make the visualizations you want from the analysis.

Excel can have up to around a million rows in a single spreadsheet, and tables in SQL Server can store enormous amounts of data. Therefore, if you are importing big data sets into Excel, you have two options:

• Pre-process the data in SQL Server, which allows you to bring in the analysis.
• Import the data into Get and Transform first, which can then do the analysis.

The data can be refreshed by right-clicking on the table and choosing Refresh.

This means that:

• Your data analysis can be up to date whenever you want.
• Multiple people can access the same data at the same time.
• Multiple people can insert data at the same time into a central database.

You can’t do this easily in Excel. It is much simpler when you use SQL and Excel together.

How can I learn SQL?

If you are interested in learning how SQL Server works, why not look at my course “Database Fundamentals,” which is exclusive to Udemy.

In just over 8 hours, you will learn the key concepts of SQL Server:

• Learn the six principal clauses of the SELECT statement
• Create tables, views, and stored procedures
• Learn about security and backups

For a more in-depth look at the SELECT statement and related objects, please go to my “Querying Microsoft SQL Server with Transact-SQL” course.

However, maybe you want to know more about Get, Transform, and Power Pivot? If so, please have a look at my “Analyzing and Visualizing Data with Microsoft Excel” course. It also shows how to connect Excel with SQL Server.

I hope that you enjoyed this article and that you found it useful. Maybe you will consider looking at learning SQL Server. You can then connect it to your Excel visualizations.If you’re ready to learn more about SQL or Excel, please continue on to Udemy’s other blog articles. “What is Excel and how it can work for you?” will give you an overview of how the spreadsheet software became an essential skill for professionals. “What is SQL?” and “Best way to learn SQL,” will give you a deeper understanding of the database language. In my next article I will explain how to use SQL in Excel in more detail.