How to run a SQL query on an Excel table?

📝 Blog Post: How to Run a SQL Query on an Excel Table?

Are you struggling to run a SQL query on an Excel table? Do you find yourself wishing for a simpler way to extract specific data from your spreadsheet? Well, you're in luck! In this blog post, we will address the common issue of running a SQL query on an Excel table and provide you with easy solutions to overcome this obstacle.

The Challenge: Running a SQL Query within Excel

A user in need recently approached us with a question that many Excel users can relate to. They wanted to create a sub-table from another table with specific criteria. Specifically, they wanted to sort last names alphabetically (A-Z) and include only those records with a non-null phone number. The user was familiar with SQL and knew that a simple query like SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname would solve their problem. However, they were unsure how to execute this query within Excel.

The Solution: Using Power Query to Run SQL Queries

While Excel does not have native support for running SQL queries, we can leverage a powerful tool called Power Query to achieve our goal. Power Query is a feature available in Excel that enables data extraction, transformation, and loading operations. By using Power Query, we can connect to various data sources, including Excel tables, and apply transformations similar to those in SQL.

Let's walk through the steps to run a SQL query on an Excel table using Power Query:

Step 1: Enable Power Query in Excel

Before we proceed, ensure that Power Query is enabled in your Excel installation. If you don't see the Power Query tab in the Excel ribbon, you may need to enable it by going to File > Options > Add-Ins > COM Add-ins and selecting the Power Query checkbox.

Step 2: Connect to the Excel Data

1. Open Excel and navigate to the Data tab.

2. Click on the Get Data dropdown and select From File > From Workbook.

3. Choose the workbook that contains your table and click Open.

4. In the Navigator window that appears, select the sheet that contains your table and click Load.

Step 3: Transform Data with Power Query

1. With the Excel table loaded in the Power Query Editor window, click on the Transform Data button.

2. In the Power Query Editor, you will see a series of options and buttons similar to those in SQL.

3. Apply the transformations required to achieve your desired result. In our case, we want to sort the table by last name and filter out records with a null phone number.

   • To sort the table by last name, click on the Sort Ascending button for the last name column.

   • To filter out records with a null phone number, click on the Filter dropdown for the phone number column and select Remove Rows > Remove All Null Values.

4. Once you're satisfied with your transformations, click on the Close & Load button to load the modified data back into Excel.

Step 4: Run and Refresh Your Query

1. With your transformed data in Excel, you can now easily rerun and refresh your query whenever needed.

2. The Workbook Queries pane, located in the Data tab, displays a list of your queries.

3. To rerun a query, simply right-click on it and select Refresh from the context menu.

4. Your table will be updated with the latest data based on your query.

The Call to Action: Share Your Experience!

Congratulations! You have learned how to run a SQL query on an Excel table using Power Query. We hope this guide has been helpful to you.

Now, it's your turn to join the conversation! Have you ever encountered a situation where running a SQL query within Excel would have saved you time and effort? Share your experience, insights, and any additional tips in the comments section below. Let's help each other become more proficient with Excel and SQL!