SQL Queries against Excel tables

The add-in allows you to run SQL queries on Excel data

Often Excel tables gradually grow into large inconvenient workbooks. Finding duplicates, filtering, grouping, complex sorting, joining tables into one – become a real challenge. Potentially, these tasks could be easily executed with SQL (Structured Query Language)… if only it was possible to run SQL queries on Excel data.

XLTools SQL Queries add-in makes it possible:

  • Run queries in Excel and directly against Excel tables
  • Create queries in the intuitive editor with syntax highlighting
  • All available Excel tables are listed in a convenient tree view
  • Generate SELECT and JOIN statements automatically
  • Use JOIN, ORDER BY, DISTINCT, GROUP BY, SUM and other SQLite operators

Main features of XLTools SQL Queries


Add XLTools SQL Queries to Excel 2016-2007

Works with: Microsoft Excel 2016, 2013, 2010, 2007, and desktop version of Office 365.

Download XLTools Add-in


Learn your way around:


How to prepare Excel data for SQL Queries

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 named tables:

  1. Select the data range > On the ‘Home’ tab click ‘Format as Table’ > Select a table style.
  2. Select the table > Open the ‘Design’ tab > Type in the Table Name.
    E.g. ‘ProductID’.
  3. Repeat these steps for each data range that you plan to query.
    E.g. tables ‘RetailPrice’, ‘SalesVolume’, etc.

Format as Table before running SQL Queries


How to run SQL SELECT on Excel tables

  1. Click the ‘Execute SQL’ button on the XLTools tab > The editor window will open.
  2. On the left-hand side find a tree view of all tables in the workbook.
  3. Click on the nodes to expand/collapse table fields (columns) > Select whole tables or specific fields.
  4. As you tick the fields, SELECT statement is generated automatically in the right-hand side area.
    Note: XLTools SQL Queries editor also highlights syntax.
  5. Select the query output location: an existing worksheet or a new one > Click ‘Run’.

Generate SELECT statement with XLTools SQL Queries


How to run SQL queries on multiple Excel workbooks

  1. Open the workbooks you want to address with the query.
  2. Make sure the data ranges are formatted as named tables.
  3. Click the ‘Execute SQL’ button on the XLTools tab > The editor window will open.
  4. On the left-hand side find the list of all tables in the open workbooks > Select the tables for the query.
  5. As you tick the fields, SELECT and JOIN statements are generated automatically.
  6. Select the query output location > Click ‘Run’.

Join, Order, Group, Distinct, and other SQL statements in Excel

Note, that XLTools uses SQLite standard – learn more about SQLite syntax (external link). Users who are familiar with SQL can execute virtually any query in Excel:

  • JOIN – join two or more tables by key columns
  • ORDER BY – sort query results
  • DISTINCT – remove duplicates from the query result
  • GROUP BY – group query results
  • SUM, COUNT, MIN, MAX, AVG and other tasks

Tip: instead of typing, simply drag and drop table names from the tree area to the SQL query area.


How to join two or more Excel tables with SQL Queries add-in

You can merge several Excel tables into one if they have a common field. For example:

  1. Suppose you want to join several tables by the common column ‘ProductID’.
  2. Click ‘Execute SQL’ on the XLTools tab > Select the fields you want to see in the output.
  3. As you tick the fields, SELECT and LEFT JOIN get generated automatically.
  4. Choose to place the query result on the existing sheet > Click ‘Run’.
  5. Done! XLTools SQL Query generates the result in seconds.

LEFT JOIN multiple Excel tables with SQL Queries add-in

 

Any questions or suggestions? Contact us or leave your comment below.

 

10 Responses to SQL Queries against Excel tables

  1. Baba Andhale says:

    Dear sir,
    I have downloaded XLTools and created a query as I need. But I have to save its name and have to run with macro. How it will be done, Pls guide

    • Maria Balobanova says:

      Hi Baba,
      Thank you for your interest in XLTools! You can copy the query text in the SQL Queries editor and use it in your macro, if that’s what you mean. Or you can try our Automation feature to run SQL queries against Excel tables without VBA scripting.

  2. Deepak R. Rechwad says:

    Hello sir,

    as we are sql programing developer, can we use your XLTools PRO for deployment to our client? direct generate their report in excel? pls guide me

    Thanks
    Deepak R. Rechwad
    (India, Mumbia)

    • Maria Balobanova says:

      Dear Deepak,
      Thank your for your interest in XLTools! You can create and run reports with XLTools SQL Queries for your customers. Please note, that if you plan to deploy XLTools at your customer’s computers as well, they will also need the PRO license. Number of computers = Number of Licenses. Before buying the PRO license, I strongly advise that you take the free trial and see if XLTools SQL Queries will be suitable for the reports you want to develop. Once you download the XLTools add-in, you will automatically receive the free trial license to your email. Let me know if any questions.

  3. Tuomo says:

    What if anything does this tool have that Microsoft’s free Power Query add-on doesn’t?

    • Maria Balobanova says:

      Hi Tuomo, these are two different tools for different purposes. Microsoft Power Query add-in allows working with external data. SQL Queries by XLTools is the add-in that enables data analysis directly Excel. Basically, you can create and run queries directly against Excel tables using SQL language. You can have both add-ins on you computer.

  4. Mor Sagmon says:

    Is the resulting table “live”? meaning, linked to the data sources and automatically calculated upon changes to the source tables (like Excel calculates dependent cells automatically)?

  5. Dana says:

    SQL Query confusion …. FREE …. Buy 19.95 ….. Free Trial or 6.99 month. Is there a difference between 19.95 version and the 6.99 per month version …. I am running Excel 2013 …. Price? — It’s FREE! .. really misleading. Please advise … Thank you, Dana Masters

  6. Julian says:

    I assume this allows you to query worksheets in the SAME workbook? If so and I distribute that workbook to members of my team will the queries work if the members don’t have SQL query add on installed?

    • Peter Liapin says:

      Hello Julian,

      On this page we have information about our COM add-in which can not be embedded in a single workbook, so all your team members who need SQL Queries will need to install XLTools Add-In too. By the way it allows to run SQL query across multiple tables in a multiple workbooks if you open them all at the same time.

      But, there is a solution I can propose. You can use our SQL Queries App for Excel 2013. It is a new kind of add-in which can be embedded in a workbook if you save it with the app panel opened.

Leave a Reply

Your email address will not be published. Required fields are marked *