How to join tables in Excel: Power Query vs. Merge Tables Wizard

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.

How to join tables with Excel Power Query

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:

  • Power Query is a built-in feature in Excel 2016 - Excel 365, but it can also be downloaded in Excel 2010 and Excel 2013 and used as an add-in. In your version, some windows may look different from the images in this tutorial that were captured in Excel 2016.
  • For the tables to be combined correctly, they should have at least one common column (also referred to as a common id or key column or unique identifier). Also, the common columns should contain only unique values, with no repeats.
  • The source tables can be located on the same sheet or in different worksheets.
  • Unlike formulas, Power Query does not pull data from one table to another. It creates a new table that combines data from the original tables.
  • The resulting table does not update automatically. You should explicitly tell Excel to do this. Please see how to refresh a merged table.

Source data

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.
Three tables to be merged into one

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:
A merged table - the expected result

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:

  • Table 1 is named Orders
  • Table 2 is named Products
  • Table 3 is named Commissions

Create Power Query connections

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:

  1. Select your first table (Orders) or any cell in that table.
  2. Go to the Data tab > Get & Transform group and click From Table/Range.
    Getting data from a source table
  3. In the Power Query Editor that opens, click on the Close & Load drop-down arrow (not the button itself!) and select the Close and Load To… option.
    Load the source table into the Power Query Editor.
  4. In the Import Data dialog box, select the Only Create Connection option and click OK.
    Create a Power Query connection.

    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.

  5. Repeat the above steps for all other tables you want to merge (two more tables, Products and Commissions, in our case).

When finished, you will see all the connections on the pane:
The source tables are saved as Power Query connections.

Merge two connections into one table

With the connections in place, let's see how you can join two tables into one:

  1. On the Data tab, in the Get & Transform Data group, click the Get Data button, choose Combine Queries in the drop-down list, and click Merge:
    Merging Power Query connections
  2. In the Merge dialog box, do the following:
    • Select your 1st table (Orders) from the first drop-down.
    • Select your 2nd table (Products) from the second drop-down.
    • In both previews, click on the matching column (Order ID) to select it. The selected column will get highlighted in green.
    • In the Join Kind drop-down list, leave the default option: Left Outer (all from first, matching from second).
    • Click OK.

    Merging two tables by matching columns in the Power Query Editor

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!

Select the columns to add from the second table

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:

  1. In the added column (Products), click on the two-sided arrow in the header.
    Click on the two-sided arrow in the added column header.
  2. In the box that opens, do this:
    • Keep the Expand radio button selected.
    • Unselect all columns, and then select only the column(s) you want to copy from the second table. In this example, we select only the Product column because our first table already has Seller and Order ID.
    • Uncheck the Use original column name as prefix box (unless you want the column name to be prefixed with the table name from which this column is taken).
    • Click OK.

    Select the columns you want to copy from the second table.

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:
A table merged in the Power Query Editor

If you need to merge only two tables, you may consider the work almost done and go load the resulting table in Excel.

Merge more tables (optional)

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:

  1. Save the table you've got in the previous step (shown in the screenshot above) as a connection:
    • In the Power Query Editor, click Close & Load drop-down arrow and select Close and Load To….
    • In the Import Data dialog box, select Only Create Connection, and click OK.

    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 resulting table saved as a Power Query connection.

  2. Combine Merge1 with your third table (Commissions) by performing these steps (Data tab > Get Data > Combine Queries > Merge).

    The screenshot below shows my settings:
    Combining a third table with the result of the previous merge

  3. Clicking OK in the Merge dialog box opens the Power Query Editor, where you select the columns to be added from table 3.

In this example, we add only the Commission column:
Select the columns to add from the third table.

As the result, you get a merged table that consists of the first table, plus the additional columns copied from the other two tables.

Import the merged table to Excel

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!

  1. On the Home tab of the Power Query Editor, click the Close & Load drop-down arrow, and choose Close and Load To….
  2. In the Import Data dialog box, select Table and New Worksheet options.
  3. Click OK.

Load the merged table in a new worksheet.

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:
A merged table in Excel

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).

How to join tables based on multiple columns with Power Query

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:
Merging tables based on multiple key columns

After that, perform exactly the same steps as described above, and your tables will be merged by matching values in all the key columns.

How to update/refresh the merged table

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:
Updating the merged table

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).

Refresh the resulting table.

Merge Tables Wizard - quick way to join 2 tables in Excel

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:
Two tables to be joined into one

With the Merge Tables Wizard installed in your Excel, here's what you need to do:

  1. Select the first table or any cell in it and click the Merge Two Tables button on the Ablebits Data tab:
    Run the Merge Tables Wizard.
  2. Take a quick look at the selected range to make sure the add-in got it right and click Next.
    Select the first table.
  3. Select the second table and click Next. Please note that our second table contains 26 rows compared to only 10 rows in the first table:
    Select the second table.
  4. Choose one or several matching columns and click Next. Since we are joining two tables by one common column, Order ID, we select only that column:
    Choose one or several matching columns.
    Please notice the Case-sensitive matching box at the top. Select it if you want to treat uppercase and lowercase text in the key columns as different characters. For this example, we don't need that, so we leave the box unselected.
  5. Select the columns to update in the first table. This step is optional, and if you don't want any updates, you can click Next without selecting anything here.

    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:
    Select the columns to update.

  6. Choose the column(s) to be added to the first table, Product in our case, and click Next:
    Choose the columns to add.
  7. This step is very important because it determines how your tables will be merged.
    In this example, we go with the default options shown in the screenshot below. But I'd like to draw your attention to the following 2 boxes that can prevent overwriting your existing data in case you've chosen to update some columns:

    • Empty cells only
    • Only if cells in the lookup table contain data

    Define how your tables should be joined.

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.
Two tables are merged into one.

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 70+ other useful tools.

In case you are looking to join tables in some other way, you may find the following resources useful.

Other ways to combine data in Excel

  • Merge tables by column headers - join two or more tables based on column names. You can choose to combine all the columns or only the ones you select.
  • Combine multiple worksheets into one - copy multiple sheets into one summary worksheet. Of course, it's not manual copy/pasting! You only indicate which worksheets to merge, and our Copy Sheets tool does the rest.
  • Compare two Excel files - how to compare two tables (worksheets) for differences and merge them into a single sheet.

Table of contents

49 comments

  1. Svetlana,

    This is incredibly helpful. Thanks for taking the time to craft this. Bookmarking now...

    Henry
    Minneapolis, MN

  2. Hello,

    I have 10 tables that I'd like to combine. Is there a way to combine all 10 tables with a shared column at one time? Your solution of combining two at a time, thus creating a new connection/table to add one more at a time works, but will create several unnecessary? connections.

    Great article and well written, thanks!

  3. Thank you very much for a clear tutorial!!!!!
    Could you provide me following information:

    What if in "datasource A" and "datasource B" the names are not the same:
    Example:
    Datasource A:
    user1 - 10 - 5
    user2 - 5 - 7
    user3 - 6 - 8
    user4 -5 -9
    user7 - 6 -10

    Datasource B
    user1 - 10 - 5
    user2 - 5 - 7
    user3 - 6 - 8
    user9 -5 -9
    user10 - 6 -10

    I would like to use this merge function as a grading / evaluation tool for my students. So I would also need to have the data that is only available in datasource A and datasource B merged. I successfully merged data from students that are in both datasources, but I was still unsuccessful where a student is only there in one datasource.

    Result would be:

    user1 - 10 - 5 - 10 - 5
    user2 - 5 - 7 - 5 - 7
    user3 - 6 - 8 - 6 - 8
    user4 -5 -9 - 5 - 9
    user7 - 6 -10- null - null
    user9 - null - null - 9 - 10
    user10 - null - null - 6 - 10

    Thank you very much for helping me out.

  4. Dear Experts,

    I have a question:
    I have 70+ tables in dbf with columns: year, product, value. Tables have different years data! To make join.
    EXAMPLE.
    Table 1
    Year product value
    1993 Apple 98.45
    1994 Mushrooms 67.54

    Table 2
    Year product value
    1992 Apple 95.45
    2021 Melon 112.0

    I need a pivot table(to consolidate) all tables in one table.

    My way:
    Let
    DatesList={1994...2021},

    Tbl=Query.odbc("dsn...", "select * from c:\data\1993.dbf"),

    Result=List.Accumulate (DatesList, (state, current) =>Table.Combine(Tbl, "product", Query.odbc("dsn...", "select * from c:\data\" +Text.From(current) +".dbf", "product")

    in
    Result

    Its ok, but results only for the last date. How to save Table between dates

    Please, help

    • Hello!
      If I understand your problem correctly, you can convert all dbf files to xlsx using Excel. Then you can use Ablebits Data - Copy Sheets or Combine Sheets to combine all files into one. Then use a filter to remove unnecessary data.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      Hope you’ll find this information helpful.

  5. Is there a way to update a history table anytime my table that is being updated every 5 minutes changes? I have a table that is updated every 5 minutes from a MySQL database. It consists of 7 columns. Date time is in the first column and the rest are equipment and location of the equipment (Longitude/Latitude)

    So it is only one line, but overwrites itself when it updates. I would like to be able to keep a running history of whenever the data changes. It could be whenever time changes every 5 minutes or whatever update rate I choose, but better would be if it updated whenever the longitude or latitude changed. Latitude is in the 4th column, Longitude is in the 5th column.

    Is there a way to make a history table like this that is linked to the live data table and only updates when data changes by a certain amount and appends the new data, adding a new line of the table so we can look back and see the history of when the equipment moved?

  6. Exactly what I needed.
    Well written and extremely helpful thanks!

  7. I have common field. But content in sheet 2 has some extra rows ,also some rows missing
    I want inter section of these two sheets. Help.

  8. Hi. I'm trying to merge two tables with the following format:

    Table A:
    ID Attribute1
    1 A
    2 B
    3 C
    4 A

    Table B:
    Attribute1 Attribute2
    A XYZ
    B WWC
    C ABC

    The merge is done on "Attribute1". Everything works fine for the first match, but then the second one (ID = 4) is left blank. What I would like is a true join. Is this possible? Thanks!

    • Hello Kevin!
      After Merge Tables Wizard you can sort your new table by the "Attributes1" column. Then use the "Fill blank cells" tool (Fill cells downwards) in the "Attributes2" column to fill the blank cells.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  9. how do i combine tables with information of sales of different days into one table spread on one excel sheet

  10. Thanks. Very helpful and clear. Worked a treat.

  11. Using Excel 2016... any idea why the 'Join Kind' dropdown would be missing from my 'Merge' tab? The ONLY option I have at bottom of this tab is a check box to "only include matching rows".

    • Hello!
      A drop-down list is a control that allows you to enter data in cells of your table. The Merge operation can be performed only with data from an Excel table, controls are not transferred to another table during the merge.

  12. Dear all

    I am having a problem in merging 3 tables.
    The Primary key is product code, one to many situations.
    I use Leftouter join
    Table 1 - code /product
    Table 2 - code/Sales
    Table 3 code/price
    Code /PRODUCT / SALES /PRICE
    AA /Product
    AA /Product / Sales
    BB/ / Sales /Price
    CC/Product / /Price
    Whenever, same code for other table, it repeat itself on the 1st Table
    However, can I make them not to repeat?
    Eric

  13. Thank you very much. This is very helpful.

  14. Very well detailed.
    Is there a way to duplicate what we can do with PBI, hence not to merge any tables but to create instead connections (1 to many) thus allowing us to create a pivot table to extract the data of our choosing?

  15. Power Query not supported on Mac with excel files

  16. I have an (Excel)lent dilemma.

    I'd like to use Merge Queries as New, but it does not show up in Office 365 at work.
    The Merge Queries as New option in Merge Queries drop-down is not available.
    For days, I could not figure out. Even IT guys gave up.
    I would make my Excel work a lot easier.
    Any help is appreciated.

    David

  17. Supercool, I had no idea about the tool. Now it saves me a lot of time whenever I am too lazy to use SQL. Thanks for the post.

  18. I can't see Join Kind in excel 2016, Please.

  19. Hi Svetlana
    Thanks for this great post. I have 3 worksheets (Tables) that I want to join in one worksheet (Table). I tried it as you described using power query, but at final step I get following error:
    "Initialization of the data source failed.
    Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."

    There isn't any external database! All tables in the one file (workbook).
    Could you please advise me.
    Thanks in advance.

  20. Other way:
    Note: I use Power Query (M Language)
    In this example I have two tables and this function (myfunction)
    cuentas(id,label,systype)
    datos(periodo,cuenta,monto)
    (myfunction)=>

    let
    Source = Table.NestedJoin(datos,{"cuenta"},cuentas,{"ID"},"unionall",JoinKind.LeftOuter),
    #"Expanded unionall" = Table.ExpandTableColumn(Source, "unionall", {"LABEL", "SYSTYPE"}, {"LABEL", "SYSTYPE"})
    in
    #"Expanded unionall"

    Then when I use "myfunction", I generate one table whit two additional colums, one with the label and other with account type for every cuenta in the table datos.
    "ID" is unique over the table "cuentas", and cuentas have many entries in the transactional table (datos) in the field "cuenta".

    Regards from Maracaibo, Venezuela

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)