Run SQL Queries against Excel tables
Working with large Excel files, joining several tables into one, removing duplicates, complex filtering, grouping, sorting, etc. are a challenge. SQL Queries app offers a new approach to accomplish these tasks easily. Now users who are familiar with structured query language can run SQL queries directly against Excel tables.
XLTools.net SQL Queries app helps query Excel data:
- Automatically generate SELECT and JOIN queries
- Join two or more tables by a key column using JOIN statements
- Remove duplicates using DISTINCT or GROUP BY statements
- Apply WHERE, SUM, COUNT, MIN, MAX statements, and much more.
XLTools.net SQL Queries App is built on Microsoft platform. MS Office Add-ins (or Office Apps) work across Excel Online, Excel 2016 and Excel 2013 desktop, Excel for iPad, Excel for Mac.
Video is kindly provided by Webucator as part of their Excel Training.
Learn your way around:
- How to add the SQL Queries App for Excel from the Office Store
- How to join two Excel tables by a key column with SQL Query
- Does the SQL Queries Office Add-in work offline
How to add the SQL Queries App for Excel from the Office Store
Download the add-in from the Office Store website, or directly from Excel interface:
- Open Excel > Insert tab > In the Add-ins group click ‘Store’ or ‘Office Add-ins’.
- Search for XLTools SQL Queries Add-in in the Store > Buy > Free Trial
- The add-in’s task pane will appear on the sheet. Drag and place it as you wish.
Note: you should be singed in to your Microsoft account to manage products and subscriptions:
File > Account > User Information > Sign in.
How to join two Excel tables by a key column with SQL Query
Suppose you need full client information with all orders, yet your data is stored in two different tables – Clients and Orders. With the SQL Queries app you can run SELECT query and JOIN these tables by their common ‘Client ID’ field.
1. Make sure your data are properly formatted as named tables:
Select the range > ‘Home’ tab > Click ‘Format as Table’ > Select a table style.
2. Follow the steps on the SQL Queries panel:
- Step 1. Add data tables for your query by selecting the range of tables one by one.
Note that SELECT and LEFT JOIN queries are generated automatically as you check the added tables.
- Step 2. Adjust the query manually if necessary.
E.g. request to sort data by Client ID – simply type ORDER BY tbl1.[Client ID].
- Step 3. Select any empty cell in the spreadsheet as the starting cell of the query output.
Click ‘Run Query’ > The resulting table appears in seconds.
Does the SQL Queries Office Add-in work offline
Online Office Add-ins require Internet connection.
But check out our desktop SQL Queries (requires installation). You can run DISTINCT, WHERE, SUM, COUNT, MIN, MAX, etc. queries as well, and it also works offline.
Read more on the difference between online Office Add-ins and desktop version.Any questions or suggestions? Please leave your comment below.