In many occasion, we need to merge or combine two or more table using just using excel. Since Excel 2016 version, if I’m not mistaken, you can combine it without third party application. In this article I will show you how to do it.
download the data sample here. And open the excel file.
You will see 2 sheets, data 1 and data 2.
As you can see, there are names that buy item from a store. And in the second data, you have the item and price.
Note that I deliberately only put 3 items, while in the buyer side, there are more than 3 items.
Later on, you will see the end result.
you need to select the table.
And then select in the Insert menu, Insert Table
and you will see this the table range
Since in this sample you have header in the data, don’t forget to click on the “my table has headers”. Klik OK
After that, to make your life easier, give a name for your table.
This will automatically show after you do this step. This will open menu Design. Put a name on it, I will buyer as the name.
Repeat this step for the second table.
And the result is gonna be like this
Make a query connection. In this step, you will assign each table into a query connection ready.
Click in one of the cell
And then in the Data menu, click on the From the table/range
and after that you will see this query table
Just close and save this dialogue.
And you will see a new sheet with a green table.
Repeat those step above for the other table.
After creating those queries, you will see on the right side of your excel sheet, a query & connections panel. In this case, we have 2 queries, buyer and item
This is simple to make connection between queries.
From Data menu, choose the Get Data.
And then choose the Combine queries –> merge
And then you will see the empty panel of query, just like below
Just choose the table you would like to combine. Top one and the below also.
After that choose the primary keys, or the table buyer and table item that have similarities in common. Click on the column of it.
Choose one of the join kind,
or you just leave it as the default. And click OK
As you can see above, the second table on the right, with the green color, only shows table on the cell. Click on it
And you can decide what column from the second table to show in the combine table. Choose all for now.
And you will get the end result. Pay attention on line 16, 17. There is no item price. Remember ? I only have 3 items in the data 2.
Now you got the the combine table.
Simple rite ?
Enjoy trying to combine table. Hope this will help.
Drop me a line if you have any questions.