You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel.
Excel for Mac now supports Power Query refresh for many data sources, as well as query creation through VBA. Authoring in the Power Query Editor is not supported yet. Refresh Power Query queries. You can refresh queries from the following data sources: Local.TXT,.CSV,.XLSX,.XML or.JSON files. Tables and ranges in the current workbook. The Power Query functionality is built into Excel 2016, under Get & Transform. It's not a separate add-in. You can check to see if an Office update is available by navigating to File-Account-Update Options-Update Now. If an update is available, it will include any new 'Power Query' functionality.
Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run Select statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more efficient than Excel formulas or a VBA Macro. A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!
What can I do with MS Query?
Using MS Query in Excel you can extract data from various sources such as:
Our software library provides a free download of doubleTwist 3.2 for Mac. Commonly, this program's installer has the following filename: doubletwist.dmg. The latest installer that can be downloaded is 16.7 MB in size. This free software for Mac OS X is an intellectual property of doubleTwist Corporation. Doubletwist for android. Download the latest version of doubleTwist for Mac. Share multimedia files and delete their restrictions. DoubleTwist was created to make it easier to sync. Music Player for the Internet of Things. Described as the iTunes for Android, doubleTwist brings you music, radio and podcasts everywhere. Free doubleTwist Mac OS X 10.5 Intel/PPC Version 3.0.0 Full Specs. It is good, but it lacks bluetooth functionality and you have to get a barcode scanner to download apps form android market.
- Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
- Access – you can extract data from Access Database files
- MS SQL Server – you can extract data from Microsoft SQL Server Tables
- CSV and Text – you can upload CSV or tabular Text files
Want more SQL Capabilities in Excel? Check-out my Excel SQL AddIn
Step by Step – Microsoft Query in Excel
In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.
I will extract data from an External Excel file called MOCK DATA.xlsx. In this file I have a list of Male/Female mock-up customers. I will want to create a simple query to calculate how many are Male and how many Female.
Open the MS Query (from Other Sources) wizard
![Tutorial Tutorial](/uploads/1/1/8/9/118988796/259494526.png)
Go to the DATA Ribbon Tab and click From Other Sources. Select the last option From Microsoft Query.
Select the Data Source
Next we need to specify the Data Source for our Microsoft Query. Select Excel Files to proceed.
Select Excel Source File
Now we need to select the Excel file that will be the source for our Microsoft Query. In my example I will select my current Workbook, the same from which I am creating my MS Query.
Select Columns for your MS Query
The Wizard now asks you to select Columns for your MS Query. If you plan to modify the MS Query manually later simply click OK. Otherwise select your Columns.
Return Query or Edit Query
Now you have two options:
- Return Data to Microsoft Excel – this will return your query results to Excel and complete the Wizard
- View data or edit query in Microsoft Query – this will open the Microsoft Query window and allow you to modify you Microsoft Query
Optional: Edit Query
If you select the View data or edit query in Microsoft Query option you can now open the SQL Edit Query window by hitting the SQL button. When you are done hit the return button (the one with the open door).
Import Data
When you are done modifying your SQL statement (as I in previous step). How to clean computer ram. Click the Return data button in the Microsoft Query window.
This should open the Import Data window which allows you to select when the data is to be dumped.
Lastly, when you are done click OK on the Import Data window to complete running the query. You should see the result of the query as a new Excel table:
As in the window above I have calculated how many of the records in the original table where Male and how many Female.
This should open the Import Data window which allows you to select when the data is to be dumped.
Lastly, when you are done click OK on the Import Data window to complete running the query. You should see the result of the query as a new Excel table:
As in the window above I have calculated how many of the records in the original table where Male and how many Female.
AS you can see there are quite a lot of steps needed to achieve something potentially pretty simple. Hence there are a couple of alternatives thanks to the power of VBA Macro….
MS Query – Create with VBA
If you don’t want to use the SQL AddIn another way is to create these queries using a VBA Macro. Below is a quick macro that will allow you write your query in a simple VBA InputBox at the selected range in your worksheet.
Just use my VBA Code Snippet:
Just use my VBA Code Snippet:
Just create a New VBA Module and paste the code above. You can run it hitting the CTRL+SHIFT+S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.
Learning SQL with Excel
Creating MS Queries is one thing, but you need to have a pretty good grasp of the SQL language to be able to use it’s true potential. I recommend using a simple Excel database (like Northwind) and practicing various queries with JOINs.
Alternatives in Excel – Power Query
Another way to run queries is to use Microsoft Power Query (also known in Excel 2016 and up as Get and Transform). The AddIn provided by Microsoft does require knowledge of the SQL Language, rather allowing you to click your way through the data you want to tranform.
MS Query vs Power Query Conclusions
MS Query Pros:Power Query is an awesome tool, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:
Brave google drive. Access Google Drive with a free Google account (for personal use) or Google Workspace account (for business use).
- Microsoft Queries are more efficient when you know SQL. While you can click your way through to Transform Data via Power Query someone who knows SQL will likely be much quicker in writing a suitable SELECT query
- You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query
MS Query Cons:Microsoft Query falls short of the Power Query AddIn in some other aspects however:
- Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
- Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations
In short I encourage learning Power Query if you don’t feel comfortable around SQL. If you are advanced in SQL I think you will find using good ole Microsoft Queries more convenient. I would compare this to the Age-Old discussion between Command Line devs vs GUI devs…
At work this week, I needed to compare two tables to see if the they had similar data. The problem was they were aggregated at different levels with different dimensions and some data in table A was not in table B and some data in table B was not in table A. I needed to find and quantify these differences as well as locate the missing data in each table. I was dealing with ad units, orders, key values, impressions and revenue from the world of online advertising and dealing with 50,000 or so rows of data, but for this post we will look at a more simplified example.
In this example we have table A and table B.
- Table A contains data for clothing purchases by customer and product along with the quantity purchased and the total amount paid. This table has two dimensions of customer and product, and two metrics of quantity purchased and amount paid.
- Table B contains data for the same clothing purchases by customer and product with an extra dimension of description and also includes quantity purchased and the total amount paid. This table has three dimensions of customer and product, and two metrics of quantity purchased and amount paid.
We want to compare these two sets of data and find out where the differences are and quantify these differences. If we take a look at the data we can see some differences.
- Table A contains data for Alex but table B is missing Alex
- Geoff has 2 sweaters in table A but has 3 sweaters in table B
There are several other differences in the tables, but spotting them manually will be hard and won’t scale when your tables have more data. Get & Transform will allow us aggregate these tables to the same level of granularity and join Photoshop elements 9 mac download version. the aggregated data by their common dimensions to easily find the differences.
Get & Transform was previously called Power Query in Excel 2010 and 2013, and you will need to install is as an add-in. Find out how to install Power Query here. If you’re running Excel 2016 then it’s already installed and can be found in the Data tab of the ribbon.
Aggregating The Tables
First we will need to aggregate the data to the customer and product level so that we can compare the two tables.
If you look at table A, you will notice that Bob and Jane have rows of data that will need to be aggregated. We ideally want only 1 row of data for Bob and Sweaters and 1 row of data for Jane and T-Shirt.
TIP: I always find it’s a good idea to use Excel Tables with your data. This way, your queries can reference a table name instead of a range. When you add data you won’t need to update the range in your queries as they will reference the name. The data sets in this example have already been turned into tables named Table_A and Table_B, but you can read about how to make a table here.
First let’s select our table and make a query.
- Select a cell in table A or select the whole table.
- Go to the Data tab in the ribbon.
- Press the From Table / Range button in the Get & Transform section.
This will open up the Query Editor.
Then select a Group By transformation.
- Go to the Transform tab.
- Press the Group By button.
Create your Group By query.
- Select Advanced to create a Group By query which groups by more than one dimension.
- Select Customer first and then Product second. You will need to use the Add grouping button to add a second dimension.
- Add a descriptive column name, select Sum as the Operation for both the Quantity and Amount columns. You will need to use the Add aggregation button to add the second metric.
Now save the query.
- Go to the Home tab in the query editor.
- Press the Close & Load button.
- Select Close & Load To.
From the Import Data menu select Only Create Connection. We could load this to another table by selecting Table if we want to see this intermediary step in our spreadsheet, but it’s not necessary. We can also select where to load the table to if we do select Table. Press the OK button to finish.
You should now see the Queries & Connections window pane docked to the right of your spreadsheet and it will contain our new Table_A query.
We can repeat the same process to create a Group By query for Table_B Spartan browser. with the exact same groupings.
You should now see two Connection only queries in the Queries & Connections window pane for Table_A and Table_B.
Join Queries With Merge
Now we will combine our queries.
- Go to the Data tab.
- Press the Get Data button from the Get & Transform Data section.
- Choose Combine Queries then Merge from the menu.
What Is Power Query
![Query Query](/uploads/1/1/8/9/118988796/692879896.jpg)
Now we can setup our merge query.
- Select Table_A for the first query.
- Select Table_B for the second query.
- Select Full Outer (all rows from both) for the Join Kind. This will mean all rows in Table_A and all rows in Table_B will be shown in the resulting table.
- Now we can select which columns our merge query will join on. Hold Ctrl then click on the Customer column and then the Product column. You should see a small 1 next to Customer and a small 2 next to Product.
- Hold Ctrl then click on the Customer column and then the Product column. We should again see a small 1 next to Customer and a small 2 next to Product.
- Press the OK button.
In the editor we will see our Table_A Group By query along with a Table_B column. We will need to expand this column to show the data in our Table_B Group By query.
Access Power Query In Excel 2016
- Right click on the expand icon in the right side of the Table_B column.
- Select Expand.
- Press the OK button.
Go to the Home tab and press the Close & Load button to create a table of the results in a new sheet.
Compare Data
It’s now easy to compare the data in table A and B and see where the differences are.