Nicholas A. Drouillard

Nicholas Drouillard

SQL in Excel

Connecting to an external database in Excel is often necessary to process large data sets. In Excel 2016, there are several techniques available that allow external data import. In this post, I will describe one nifty way to connect your workbook to an external database and use SQL to produce valuable tables.

The simple example below can be applied to accomplish some amazing tasks. The freedom to run SQL queries in Excel allows users to analyze millions and millions of data points at a time. I often use this technique to “pre-process” enormous data tables before preparing final reporting products.

***Hover your mouse over the images below to enlarge (supported by most browsers)

Instructions

Step One

In a fresh workbook, activate the data tab on the navigation bar. In the “get data” window, select “from other sources” and choose the “from SQL server” option.

Excel Power Query
Step One

Step Two

Enter the server name and your authorized login credentials.

Excel Power Query
Step Two

Step Three

Here you will see a list of tables and views available for query in your database. Since our goal here is to use SQL to pull information we don’t need to worry about selecting the correct tables at this point.

For now, select any table and click next.

Excel Power Query
Step Three

Step Four

Here you can add a recognizable nickname and description of the connection. Adding a few descriptive notes may come in handy when working on this connection in the future.

Also, there is an option here to save your login credentials. This option is convenient but is a security risk when exercised on a shared file.

Excel Power Query
Step Four

Step Five

Next, you have a few options of how to import the data. For now, select the Only Create Connection option.

The main reason to stick with this option is that we have yet to add our SQL query statement. Other options would cause irrelevant information to display.

Excel Power Query
Step Five

Step Six

Now that our connection is created we can apply our SQL statement.

In the Data tab, select Connections.

Excel Power Query
Step Six

Step Seven

In the Workbook Connections window, highlight the new connection and select properties.

Excel Power Query
Step Seven

Step Eight

Go to the Definition tab in the Connection Properties window. Change the Command type to SQL. Cut and paste your SQL statement into the Command text box and click OK.

Excel Power Query
Step Eight

Step Nine

The results of our query can be utilized in a variety of ways. For this exercise, I will assume that our goal is to display the output of the query in a simple table.

In the Data tab, select Existing Connections found in the External Data window.

Excel Power Query
Step Nine

Step Ten

Select the applicable connection in the Existing Connections window and click Open.

Excel Power Query
Step Ten

Step Eleven

In the import Data table, select the Table option and indicate where to insert the table on your workbook.

Excel Power Query
Step Eleven

Step Twelve

After you click OK, your table will appear in the location you selected.

Excel Power Query
Step Twelve