Merge Tables In Excel How To Merge Two Tables By Matching A Column

Merge Tables in Excel

While analyzing the data, we might gather all the necessary information in a single worksheet. Unfortunately, it is a common problem when data is divided across many worksheets or workbooks. There are many ways to merge the data from multiple tables into one table in excelTable In ExcelIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more.

How to Merge 2 Tables in Excel?

We have given customer data city-wise in two tables. We have taken 20 records.

Sheet 1: Table 1: CustomerInfo

Sheet 2: Table 2: ProductDetails

In both the tables, “Order No.” is the common information on which basis we will create a relationship between them.

Follow the same steps for another table, “ProductDetails.” We have given the name “Products” to another table. Refer to the below screenshot.
Click on somewhere on the “Customer_Info” table then, Then go to the “Insert” tab, and click on the “PivotTable” option under the “Tables” section.
A dialog box for “Create PivotTable” will appear. Next, tick the checkbox “Add this data to the Data Model,” as shown in the below screenshot.
Click on “OK.” Then, it will open a new sheet with a new Pivot Table FieldsPivot Table FieldsPivot table calculated fields are formulas with reference to other fields, and calculated values refer to other values within a specific pivot field.read more section on the right side, as shown in the below screenshot.
Click on the “ALL” tab in the “PivotTable Fields” section. It will display all the tables created by us. Refer to the below screenshot.
Now, click on the “Relationships” option under the “Calculations” section, as shown in the below screenshot.
It will open a dialog box for creating a relationship between these tables. First, click on the “New” button. Then, refer to the below screenshot.
Again, it will open a dialog box as shown below, and created tables are listed here.
As there is one field, “Order No.” is common in both tables. Hence we will create a relationship between these tables using this common field/column.Select “Customer_Info” under the “Table” section and the “Order No.” field under the “Column (Foreign)” section. Then, refer to the below screenshot.
Select another table, “Products,” under the “Related Table” section and select the “Order No.” field under the “Related Column” section. Then, refer to the below screenshot.
The primary key is the unique values that appear once in the table. Then, click on “OK.” It will display the relationship, as shown in the below screenshot.
Now, we can drag and drop the field accordingly to see the result. Next, click on the “Customer_Info” table, as shown in the below screenshot.
Under the “ROWS” box, drag fields “Order No.”, “Customer Name,” and the “City.”Drag the “Age” field under the “FILTERS” box.Drag the “Product” field under the “COLUMNS” box, and the “VALUES” box for the products count.

The final result is below:

Click on any cell in the “Customer Info” table. Go to the “INSERT” tab and click on the “Table” option under the “Tables” section. You may refer to the below screenshot. Then, the “Create Table” dialog box will appear. Our table “CustomerInfo” has column headers. Hence, the checkbox “My table has headers” should be checked. Refer to the below screenshot. It will convert our data into a table format. Now, click on the “Table Name” field under the “Properties” section and give the name of this table as “Customer_Info.”

Accordingly, as per the requirement, we can drag and drop the fields.

Things to Remember About Merge 2 Tables in Excel

We can merge more than two tables using this process.There should be one column common in each table.That one common column will work as a primary key in this process. Hence, this field should have unique values.