Join tables in Excel | Merge table in Excel

When we work on multiple tables in excel, sometimes we need to join two tables based on some values common to both tables. There are tables with Header and Line relationships like sales header and sales line, invoice header and invoice line, we need or want to join these table so we can view data from line and header in the same table. To get holistic data from one table we merge two tables and start analyzing data. In this post, we are going to discuss joining of two tables in excel by using Microsoft Query.

Let me start by first why not relationship?

A relationship is a way to link two tables using some value common on both tables like joining two tables using a primary key.  I personally prefer this approach when working with multiple tables. But sometimes you want to be sure you are whether you are using correct data or not, and unless you see all your data in one table you can't rely on yourself. In that case, you should do the table merge.

How to merge two tables?

STEP 1 : Go to Data Tab
STEP 2 : Select Get Data
STEP 3 : Select From Other Sources
STEP 4 : Select From Microsoft Query

STEP 4 : In Choose Data Source window select Excel Files

STEP 5 : Select OK to confirm the sources 

STEP 7 : In select workbook window first select the directory where your excel file is located
STEP 8 : Select the excel file
STEP 9 : Click OK to continue

STEP 10 : Sometime no data is shown in available sheet window, to fix this select options
STEP 11 : Select System Tables
STEP 12 : Click OK to continue

STEP 13 : Select the sheet you want to merge then press forward arrow to move all fields from the sheet to column in your query window.
STEP 14 : Click NEXT to continue.

 STEP 15 : Select OK to continue during warning.

STEP 16 : Select field you want to join in one table then drag it to the field in other table. This will create a line.

 STEP 17 : We don't need to save it. Best approach is to select Return Data to Microsoft Excel.

STEP 18 : Windows will change to excel, here select table as first option and in second option you can put your data on new worksheet or Existing worksheet(select if you start working in new worksheet in beginning).

This is final view of joined table.

This is the way I use for joining tables in excel. If you have some other way of doing it easily or more efficiently please let us know on comment below.