Use the power of SQL to run queries in Excel and directly on Excel tables
![Screenshot: main features of XLTools SQL Queries](/static/034b49a3fe1af7c188780380d670377e/10861/sql_select_statement-e1433760651803.png)
Often Excel tables gradually grow into large inconvenient workbooks. Finding duplicates, filtering, grouping, complex sorting, joining tables into one, etc. – become a real challenge. Potentially, these tasks could be easily executed with SQL… if only it were possible to run SQL queries against Excel data.
XLTools SQL Queries add-in enhances Excel with the power of structured query language:
- Run SQL queries in Excel interface and directly on Excel tables
- Generate SELECT and JOIN statements automatically
- Use JOIN, ORDER BY, DISTINCT, GROUP BY, SUM and other SQLite operators
- Write queries in the intuitive editor with syntax highlighting
- Address any Excel tables from a tree list view
Before you begin, add SQL Queries to Excel
SQL Queries is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.
![Get started with XLTools ribbon](/static/75ac5fb276b36f709bde811d2e2a22c1/24bc3/xltools-ribbon-overview.png)
How to turn your Excel data into a relational database and prepare for SQL querying
By default, Excel presents data as simple data ranges. But SQL works only with relational data sources. So before you begin, make sure to format Excel ranges as tables (named ranges with table style applied):
- Select the data range
On the Home tab click Format as Table
Select a table style.
- Select the table
Open the Design tab
Type in the Table Name.
E.g. "ProductID"
Repeat these steps for each data range that you plan to use in SQL queries.
E.g. tables "RetailPrice", "SalesVolume", etc.
Done, now these tables will serve as a relational database and are prepared for querying.
![How to turn Excel data into relational database before running sql queries](/static/c97e707f292fa7e37a499ad7ceb6a9ee/a23f3/sql_format_as_table-e1433760453193.png)
How to create and run SQL SELECT on Excel tables
SQL Queries add-in allows you to execute queries on Excel tables located in different worksheets and different workbooks. Please make sure that these workbooks are open and the necessary data are formatted as named tables.
- Click the Execute SQL button on the XLTools tab
The editor window will open.
On the left-hand side find a tree view of all available tables.
Click on the nodes to expand/collapse table fields (columns).
Choose whether to place the query output on a new or an existing worksheet.
- Click Run
Done!
Select entire tables or specific fields.
![How to generate SELECT statement with XLTools SQL Queries](/static/034b49a3fe1af7c188780380d670377e/10861/sql_select_statement-e1433760651803.png)
Operators Left Join, Order By, Group By, Distinct, and other SQLite commands in Excel
- LEFT JOIN – join two or more tables by shared key columns
- ORDER BY – sort data in query results
- DISTINCT – remove duplicates from the query output
- GROUP BY – group data in query results
- SUM, COUNT, MIN, MAX, AVG and other operators
How to join two or more Excel tables with SQL Queries add-in
You can merge multiple Excel tables into one if they have a shared key field. Suppose you have to join a few tables that share a column "ProductID":
- Click Execute SQL on XLTools tab
Select the fields you want to see in the merged table.
As you tick the fields, SELECT and LEFT JOIN get generated automatically.
Choose whether to place the query output on a new or an existing worksheet.
- Click Run
Done! A merged table appears in seconds.
![How to left join Excel tables with XLTools SQL Queries add-in](/static/bb800c7072ee12dedcbfc84a25ed8c6e/536ff/sql_join-e1433760772578.png)
Certificate errors during install, would love to try. Name: XLTools Add-In for Microsoft Excel From: https ://xltools.net/clickonce/pro/en/XLTools.vsto ************** Exception Text ************** System.Security.SecurityException: Customized functionality in this application will not work because the certificate used to sign the deployment manifest for XLTools Add-In for Microsoft Excel or its location is not trusted.
Hello Matt, thank you for reporting the error! We will look into it.
In the meanwhile, please try this: download XLTools again > Save the file > Run ‘XLTools.exe’ and follow the setup wizard > If you are prompted to install Microsoft prerequisites along the way, please do so.