How To Use SQL Statements In Excel

SQL statements can be used to retrieve information from records that may be held in an Excel spreadsheet or a database.

An advantage of using a SQL statement is that you can identify which records to return by stating which of the columns you want to include and the values you want. A benefit of this approach is that you don’t have to re-type existing data, as you can create a SQL statement to get or pull the data for you.

To create a SQL statement, you will need a spreadsheet or database with records that you wish to use as your source, and a new spreadsheet.

For the purposes of this guide, sample data has been created for you to use. In this scenario, the manager of a car dealership wants to pull records for those months where the forecasted sales exceeded the actual sales so he can drill down and examine the reasons why.

Steps to create a SQL statement

The steps below will take you through the process using sample data. A quick reference guide is available at the end of this document.

Identify your source document

1. For this scenario, we will use How to use SQL statements in Excel – sample data but you do not need to open it. There are several records where the variance is negative. That is, the forecasted sales were higher than the number of cars actually sold in a month.

Note: though you don’t need to open the source document during this process, it is useful to view the original database while deciding which records to include in the query. As the query will be accessing the source document, you can open it as Read Only.

2. Open a new Excel spreadsheet.

3. On the menu, select Data, GetData, From Other Sources, From Microsoft Query.

Two windows open

4. Microsoft Query and the Choose Data Source. In the Choose Data Source window the Databases tab is active, select the relevant data source type. For our example, click Excel Files.

5. Ensure the Use the Query Wizard to create/edit queries checkbox is active/selected.

Note: Excel lists any previously saved SQL statements in the Queries tab.

6. Click OK.

7. The Select Workbook window opens while Excel is connecting to the data source.

8. In Database Name, select the source document. Select the file location in the Directories list if necessary. Click How To Use SQL Statements In Excel – sample data. Click OK.

9. The Query Wizard – Choose Column window opens.

What To Do If Available Tables And Columns Field Is Empty

• The Available tables and columns area may be empty, if so, click Options.
• In Table Options click System Tables click OK.

The database name should appear.

10. Click the + sign to expand the list of columns in the source document.

11. For each column you want to include in your query, select the column name under Available tables and columns, click the right arrow button (>), the column name moves into the Columns in your query list. Repeat until all columns are listed on the right.

Note: you don’t need to include all the columns from the database, just include the columns you wish to see in your query.

12. Click Next.

The Query Wizard

13. The Filter Data window opens. This is where you decide how you want to filter the records from the database.

14. In Column to filter, select a column name, click Variance, under Only include rows where, select the relevant logical operation.

15. In our example, the manager only wants to pull records where the variance is less than zero. Select less than, type zero (0) in the right field.

Note: You have the option of adding more than one filter. If you wish to filter to specific values from the column, click the dropdown arrow.

16. Click Next.

17. The Query Wizard – Sort Order window opens. If you wish to sort your query, select the relevant column and whether you want to sort in Ascending or Descending order. You can sort by more than just column headings.

18. Click Next.

Saving a SQL statement/query

19. In the Query Wizard – Finish window, click Save Query if you’d like to save this query to run again later.

20. Excel displays the query and opens the Save As window. Enter a name for your query. Click Save.

21. Return Data to Microsoft Excel is selected, click Finish.

22. In the Import Data window that opens, select in which cell you wish your query to appear. Click OK.

23. The query appears in the cell selected and is filtered to the records indicated in our scenario, which was to display the records with negative variances.

How to Edit a SQL Statement in Microsoft Query

You have the option of editing an existing SQL statement; Excel will generate a new table with the results and post it in either the existing location or a new location.

1. On the Data tab, click From Table/Range.

The Power Query Editor

2. Window opens. From here you can use the options in the menu to adjust the data presented in the table.  You can also filter or sort records using the arrows over each column.

Click Select All, then select December and February.

3. Once you have made your edits you can choose to Close & Load to view the results in the original location of your table, or Close & Load To.. to select a new location.

Select Close & Load.

4. Excel displays the updated SQL statement. The Queries & Connections pane opens by default.

Refreshing Source Data

As the source database is updated, you can automate the SQL query to automatically refresh, or you can refresh the data manually.

1. On the Data tab, click the Refresh All button, you will have the option to refresh all data from the source or can click Refresh to update the data in a selected cell.

2. You can set a SQL statement to automatically refresh. Under Refresh Al, select Connection Properties.

3. In Query Properties select Refresh every XX Enter 10 minutes. Click OK.

Quick reference guide

Steps to create a SQL statement

The steps below will take you through the process using sample data.  A quick reference guide is available at the end of this document.

Identify your source document.

Note: though you don’t need to open the source document during this process, it is useful to view the original database while deciding which records to include in the query.  As the query will be accessing the source document you can open it as Read Only.

Open a new Excel spreadsheet.

On the menu select Data, GetData, From Other Sources, From Microsoft Query. Two windows open: Microsoft Query and the Choose Data Source. In the Choose Data Source window the Databases tab is active, select the relevant data source type.

Ensure the Use the Query Wizard to create/edit queries checkbox is active/selected.

Note: Excel lists any previously saved SQL statements in the Queries tab.

Click OK.

The Select Workbook window opens while Excel is connecting to the data source. In Database Name, select the source document. Select the file location in the Directories list if necessary. Click OK.

The Query Wizard – Choose Column window opens.

What To Do If Available Tables And Columns Field Is Empty

1. The Available tables and columns area may be empty, if so, click Options.
2. In Table Options click System Tables click OK.
3. The database name should appear.

Click the + sign to expand the list of columns in the source document.

For each column you want to include in your query, select the column name under Available tables and columns, click the right arrow button (>), the column name moves into the Columns in your query list.

Note: you don’t need to include all the columns from the database, just include the columns you wish to see in your query.

Click Next. The Query Wizard – Filter Data window opens.

In Column to filter, select a column name. Under Only include rows where, select the relevant logical operation. Enter or select the relevant value in the field to the right.

Note: You have the option of adding more than one filter. If you wish to filter to specific values from the column, click the dropdown arrow.

Click Next. The Query Wizard – Sort Order window opens. If you wish to sort your query, select the relevant column and whether you want to sort in Ascending or Descending order. You can sort by more than column heading. Click Next.

Saving a SQL statement/query

1. In the Query Wizard – Finish window, click Save Query if you’d like to save this query to run again later.
2. Excel displays the query and opens the Save As window, enter a name for your query. Click Save.
3. Return Data to Microsoft Excel is selected, click Finish.
4. In the Import Data window that opens, select in which cell you wish your query to appear.  Click OK.
5. The query appears in the cell selected and is filtered to the records indicated.

How To Edit a SQL Statement In Microsoft Query

You have the option of editing an existing SQL statement; Excel will generate a new table with the results and post it in either the existing location or a new location.

1. On the Data tab, click From Table/Range
2. The Power Query Editor window opens. From here you can use the options in the menu to adjust the data presented in the table. You can also filter or sort records using the arrows over each column.
3. Once you have made your edits you can choose to Close & Load to view the results in the original location of your table, or Close & Load To.. to select a new location. Select Close & Load.
4. Excel displays the updated SQL statement. The Queries & Connections pane opens by default.

Refreshing Source Data

As the source database is updated, you can automate the SQL query to automatically refresh, or you can refresh the data manually.

1. On the Data tab, click the Refresh All button, you will have the option to refresh all data from the source or can click Refresh to update the data in a selected cell.
2. You can set a SQL statement to automatically refresh. Under Refresh Al, select Connection Properties.
3. In Query Properties select Refresh every XX Select the minute interval. Click OK.