How to Use SQL in Excel: Loading Tables and Queries in Excel

You can do a lot of powerful things with Excel, for example connecting to other data sources. In this article, we are going to look at how we can use SQL within Excel.

What is SQL?

SQL stands for “Structured Query Language.” Microsoft SQL Server is just one of the many databases that use it. In a database:

• you store data in tables
• you can run SQL queries to retrieve data.

The advantages of using a database such as Microsoft SQL Server to store your data include:

• The data is strongly typed, meaning that you cannot store a number in a date field. This makes your data instantly validated.
• It can be a central data repository for your data on multiple projects.
• Multiple people can access the same data at the same time. This reduces duplication and inconsistencies.
• It is also well-protected with built-in security within the Relational Database Management Systems. Microsoft SQL Server offers several layers of security.

For more information, please see my Udemy article “Excel vs SQL Server.”

How do you access an SQL Server? First, you need a data connection. If you are using a work SQL Server, then you will be given details of your server by your IT department. This will include:

• The Server Name: It can also take this from the Connection String if you have it.
• Authentication Method: You will use either:
  • Windows Authentication, using your Windows username and password
  • SQL Server Authentication, using a separate username and password

If you have Microsoft SQL Server on your own computer, then the server name could be “localhost” or “.”, and you will probably use Windows Authentication.

You can use this connection to retrieve the Microsoft SQL Server data.

There are three different places in Excel where you can load SQL data:

• In the main Excel window
• In the Get and Transform window (also known as the Power Query editor)
• In the Power Pivot window (also known as the Data Model)

We will have a look at each of these places.

Connecting SQL to the main Excel window

The main Excel window is the one you use every time you open Excel. To load data from SQL Server, go to Data – Get Data – From Database – From SQL Server Database. This has superseded previously used methods such as Microsoft Query.

You will then have to provide the Server Name.

There are four SQL Server data sources that you could query to return the results.

1. You may want the data from a table. This is the raw data.
2. You may want the query results from a previously created view. This results from an SQL Server data analysis.
3. You may want the results from a stored procedure. This could be a more complex analysis, or one that involves parameters. For example, you may just want all sales from the state of Florida. Here, ‘Florida’ would be a parameter.
4. You may want to run an ad hoc SQL query using the SELECT statement.

If you want to run a Stored Procedure or an ad hoc query, then at this stage, you will need to click on “Advanced options” and write the query in the box provided. You will also need to enter the name of the database as well.

Next, you need to provide the Authentication mode and any credentials required:

If you want to retrieve the results of a table or query, you can select the table or query. If you then click “Load,” it will be loaded into your Excel Workbook. We will look at what happens if you click “Transform Data” in the next section of this article.

Once you have made the link, it will load the data into an Excel Table. You can then use it just like other data stored in a table.

You can refresh the data whenever you want by right-hand clicking inside the table and choosing Refresh, or by going to Table Design – Refresh.

Connecting SQL to Get and Transform

The second way to connect to SQL data is by using the Get and Transform window.

This follows the same process for connecting to SQL Server as mentioned above, except that you press “Transform Data” instead of Load.

Once you have done this, then the data is in the Get and Transform window, also known as the Power Query Editor. 

You can also load data directly from the Power Query Editor. To do this, go to Home – New Source.

You can then perform additional manipulations before the data transfer into Excel. For example, you might want to:

• Hide some columns or rows (by going to Home – Choose/Remove Columns)
• Add additional columns using formulas. (However, Power Query uses a language called M, which differs significantly from Excel.)
• Summarise the data using the Group By function

If you do this in Power Query, it will reduce the amount of data that goes into Excel. Power Query reduces the amount of data that it receives from SQL Server through a process called Query Folding. For example, you could retrieve all the contents of a table into Power Query, limit the number of rows to just 50, and reduce the number of columns used to just two. 

This reduction will be incorporated into the SQL statement so that Excel only retrieves the needed rows and columns from SQL Server. This reduces network traffic and increases the speed of retrieving that data.

When you leave the Power Query window by going to Home – Close & Load, it would then load the data into an Excel Table as before. 

However, if you go to “Home – Close & Load To…” instead, you could then:

• Use it in a Pivot Table or Chart without loading the data in Excel as a Table.
• Save it as a Connection (without loading the data into an Excel Table).

If you save it as a connection, you can use it later as the data source in any new Pivot Tables.

In “Save & Load To…”, there is a checkbox for “Add this data to the Data Model.” If you click on this, Excel will then export the data into Power Pivot, also known as the Data Model. We’ll have a look at the Data Model in the next part of this Article.

Connecting SQL to Power Pivot

The third way of connecting SQL to Excel directly is by using the Data Model, also known as Power Pivot. To open the Data Model, you need to go to Data – Manage Data Model. 

Then you can import the data into Power Pivot by going to Home – Get External Data – From Database – From SQL Server. You then connect to SQL Server in a similar process as before.

Once you have imported the data, you can then create calculation columns or measures. Power Pivot uses a formula language called DAX to build formulas. DAX is an extended version of the Excel formulas.

Once you have finished, you can then create a Power Pivot Table by going to Home – PivotTable – PivotTable.

This allows you to create Pivot Tables or charts from this data.

Where to go for more information

I hope that you have enjoyed this article.

Are you interested in Power Query or Power Pivot? Then why not join me in my “Analyzing and Visualizing Data with Microsoft Excel” course, where we have a look at these topics, together with Pivot Tables.

Do you want to learn SQL statements quickly? Please have a look at my “SQL Server Essentials in an hour” course. We will look at the six principal clauses of the SQL SELECT Statement: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.