Link
Microsoft Excel Useful Procedures
Search

Merging two tables

This instruction will guide you on how to merge information scattered in two or more tables into a single table.

Table of contents

  1. Purpose of this instruction
  2. Instructions
    1. Saving a Table as a Connection
    2. Merging Connections into a Single Table
  3. Well Done!

Purpose of this instruction

When structuring information within tables it is necessary to keep related data together. Sometimes it is necessary to combine this information into a Power Query. This will provide an insightful summary of data scattered in several tables. This instruction set will follow the procedure using Power Query.

Instructions

Saving a Table as a Connection

Open Excel spreadsheet with the tables you wish to merge.

  1. Select [Data] from the large toolbar at the top.

  2. Click on a cell in any of tables you wish to merge.

  3. Select From Table/Range in the Get & Transform section of the [Data] menu. Step 1-3

    A popup called Power Query Editor will appear

  4. Click [File] from the large toolbar of the Power Query Editor. Step 5

  5. Select Save & Load To from the dropdown menu

    Step 6

    A box with several save options will appear on your screen

  6. Select the Only Create Connection option.

  7. Click OK

    Step 7

    Progress Check 1: A menu should pop out on the right side of your screen called Queries & Connections and you should see your table there.

  8. Repeat Steps 3 through 8 for all tables you wish to merge.

Merging Connections into a Single Table

  1. Select [Get Data] from the [Data] menu.

  2. Select Combine Queries from the dropdown.

  3. Click Merge.

Steps 1,2,3

  1. Select the two tables you wish to merge from the dropdown bars.

Step 4

  1. Click on the single column you wish to match the tables by on both tables.

  2. Select Left Join from the Join Kind dropdown menu.

  3. Click OK

Steps 5,6,7

A Power Query editor should appear on your screen.

  1. Click on the two arrows pointing in opposite directions above the table of labels.

Steps 8

A Box of options should appear.

  1. Unselect Select all columns.

  2. Click the column you want to display.

  3. Click OK

Step 9

  1. Click [File] from the menu at the top.

  2. Save in the format you wish.

Well Done!

If you followed this guide you can now make queries for individual tables and merge them. This will allow you to quickly create powerful and informative tables while keeping your source information in separate tables.