In this tutorial, we will look at how you can join tables in Excel based on one or more common columns by using Power Query and Merge Tables Wizard.
Combining data from multiple tables is one of the most daunting tasks in Excel. If you decide to do it manually, you may spend hours only to find out that you've messed up important information. If you are an experienced Excel pro, then you can possibly rely on VLOOKUP and INDEX MATCH formulas. A macro, you believe, could do the job in no time, if only you knew how. The good news for all Excel users - Power Query or Merge Tables Wizard can be your time-saver. The choice is yours.
In simple terms, Power Query (also known as Get & Transform is a tool to combine, clean and transform data from multiple sources into the format you need such as a table, pivot table or pivot chart.
Among other things, Power Query can join 2 tables into 1 or combine data from multiple tables by matching data in columns, which is the focus of this tutorial.
For the results to meet your expectations, please keep in mind the following things:
As an example, let's join 3 tables based on the common columns Order ID and Seller. Please note that our tables have different numbers of rows, and although table 1 has duplicates in the Seller column, table 3 contains only unique entries.
Our task is to map the data in table 1 with the relevant records from the other two tables, and combine all the data into a new table like this:
Before you start joining, I'd advise you to give some descriptive names to your tables, so it will be easier for you to recognize and manage them later. Also, although we say "tables", you do not actually need to create an Excel table. Your "tables" could be usual ranges or named ranges as in this example:
Not to clutter your workbook with copies of your original tables, we are going to convert them into connections, do the merge within the Power Query Editor, and then load only the resulting table.
To save a table as a connection in Power Query, here's what you do:
This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.
When finished, you will see all the connections on the pane:
With the connections in place, let's see how you can join two tables into one:
Upon completion of the above steps, the Power Query Editor will show your first table (Orders) with one additional column named like your second table (Products) added to the end. This additional column does not have any values yet, just the word "Table" in all the cells. But don't feel discouraged, you did everything right, and we are going to fix that in a moment!
At this point, you have a table resembling the one in the screenshot below. To complete the merging process, perform the following steps within the Power Query Editor:
As the result, you will get a new table that contains every record from your first table and the additional column(s) from the second table:
If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.
In case you have three or more tables to join, there is some more work for you to do. I will outline the steps briefly here, because you have already done all this when joining the first two tables:
This will add one more connection, named Merge1, to the Queries & Connections pane. You can rename this connection if you want (right-click and select Rename in the pop-up menu).
The screenshot below shows my settings:
In this example, we add only the Commission column:
As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.
With the resulting table in the Power Query Editor, there is just one thing left for you to do - load it in your Excel workbook. And it is the easiest part!
A new table combining the data from two or more sources appears in a new worksheet. Congratulations, you did it!
As a finishing touch, you may want to apply the right number format to some columns and maybe change the default table style to your favorite one. After these improvements, my combined table looks very nice:
Tip. If your tables contain numeric data (e.g. sales numbers or quantity) and you want a quick summary, you can load the resulting table as a PivotTable Report or create a pivot table in the usual way (Insert > PivotTable).
In the previous example, we were combining tables by matching data in one key column. But there is nothing that would prevent you from selecting two or more column pairs. Here's how:
In the Merge dialog box, hold the Ctrl key and click on the key columns one-by-one to select them. It is important that you click on the columns in the same order in both previews, so the matching columns have the same numbers. For example, Seller is key column 1 and Product is key column 2. Blank cells or rows that Power Query is unable to match show null:
After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.
The best thing about Power Query is that it is a one-time setup. When you make some changes to a source table, you don't have to repeat the whole process again. Simply, click the Refresh button on the Queries & Connections pane, and the merged table will update at once:
If the pane has disappeared from your Excel, click the Queries & Connections button on the Data tab to get it back.
Alternatively, you can click the Refresh all button on the Data tab tab or the Refresh button on the Query (this tab activates once you select any cell within a merged table).
Now that you are familiar with the inbuilt tool, let me show you our approach to merging tables in Excel.
In this example, we will be combining the same tables that we joined with Power Query a moment ago. I have just added a few more rows to the second table to show you more capabilities of our add-in:
With the Merge Tables Wizard installed in your Excel, here's what you need to do:
We select the Seller column because we have more rows in the second table and we want the new seller names to appear in the existing Seller column:
Make your choices, click Finish, allow the wizard a few seconds for processing, and examine the results.
With the default options, the wizard highlights the newly added rows and adds the Status column. If you don't want any of that, clear the corresponding boxes in the last step.
To join three and more tables, simply repeat the above steps. Just remember to select the result of a previous merge as your main table.
Unlike Power Query, the Merge Tables Wizard does not keep a connection between the resulting and source tables. In some situations, this may be a disadvantage. On the plus side, no matter what you do with the source table - edit, move or even delete - the merged table remains intact.
This example has shown just one scenario that our wizard can handle, but there is much more to it! If you are curious to know other use cases, please check out these examples.
Also, you can download a a trial version of Ultimate Suite for Excel that includes Merge Tables Wizard as well as 60+ other useful tools.
In case you are looking to join tables in some other way, you may find the following resources useful.
Table of contents