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 also select where to load the table to if we do select Table.

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. 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.įirst let’s select our table and make a query.įrom the Import Data menu select Only Create Connection. When you add data you won’t need to update the range in your queries as they will reference the name. This way, your queries can reference a table name instead of a range. TIP: I always find it’s a good idea to use Excel Tables with your data. We ideally want only 1 row of data for Bob and Sweaters and 1 row of data for Jane and T-Shirt.

If you look at table A, you will notice that Bob and Jane have rows of data that will need to be aggregated. Aggregating The Tables #įirst we will need to aggregate the data to the customer and product level so that we can compare the two tables. If you’re running Excel 2016 then it’s already installed and can be found in the Data tab of the ribbon. Find out how to install Power Query here.

Get & Transform was previously called Power Query in Excel 20, and you will need to install is as an add-in. Get & Transform will allow us aggregate these tables to the same level of granularity and join the aggregated data by their common dimensions to easily find the differences. 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. Table A contains data for Alex but table B is missing AlexGeoff has 2 sweaters in table A but has 3 sweaters in table B If we take a look at the data we can see some differences.

We want to compare these two sets of data and find out where the differences are and quantify these differences. This table has three dimensions of customer and product, and two metrics of quantity purchased and 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. Table A contains data for clothing purchases by customer and product along with the quantity purchased and the total amount paid. In this example we have table A and table B.

How to insert a table in word 2016