Merge Tables In Excel

When working in Excel, it is not definite that data will be in a single worksheet. It can be in multiple worksheets on multiple tables. If we want to merge tables, there are various methods to do that so that we can have data in a single table. It is known as merging tables in Excel. We can do this by using the VLOOKUP or INDEX and MATCH functions.

Consider the below image showing two tables in two different sheets.

However, we can see that column A is common in both sheets. In this case, we can merge tables such that the data is available in a single sheet.

Let us learn how to merge tables in Excel using detailed examples.

As we already discussed, merging tables in excel is useful to analyze the data effectively. Now, let us learn how to merge tables in excel with the following examples.

Consider the below two tables.

Example #1 - Sheet 1: Table 1: BusinessInfo

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

Example #2 - Sheet 2: Table 2: ProductDetails

Steps To Merge Tables In Excel

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 Fields 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:

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

• As the name suggests, merge tables in excel is used to merge tables in excel from multiple sheets.
• We can merge more than two tables using this process.
• There should be one column common in each table.
• There are many ways to merge the data from multiple tables into one table in excel