
- #How to compare and merge workbooks excel 2016 how to#
- #How to compare and merge workbooks excel 2016 install#
- #How to compare and merge workbooks excel 2016 update#
- #How to compare and merge workbooks excel 2016 full#
The file must be saved with data that can be used to query from another workbook. To test these features, you also need a second workbook stored on your computer. This external workbook can be one that is located on your local drive, on a network drive, or any location where you have permissions to access it. This is the tab that also contains the power query feature to connect to another workbook. The "Data" tab contains every feature that incorporates importing, querying and working with external data. It's also referred to as the "Get and Transform" feature in most of Microsoft's documentation. You can manually type references to another workbook, but newer versions of Excel offer a much more convenient way to query and consolidate data from external resources including other workbooks. These features let you calculate data in different workbooks and worksheets without the need to copy and paste data to the same location. It’s now easy to compare the data in table A and B and see where the differences are.Excel 2019 has functions that let you retrieve and consolidate data across multiple sheets and multiple workbooks. Go to the Home tab and press the Close & Load button to create a table of the results in a new sheet.
#How to compare and merge workbooks excel 2016 full#
#How to compare and merge workbooks excel 2016 how to#
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.
#How to compare and merge workbooks excel 2016 update#
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.
#How to compare and merge workbooks excel 2016 install#
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.

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.
