WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Merge Tables in Excel

Merge Tables in Excel

When we work in excel it is not definite that data will be in a single worksheet, it can be in multiple worksheets in 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 and this is known as merging tables in excel, this can be done by using VLOOKUP or INDEX and MATCH functions.

Merge Tables in Excel

Sometimes while analyzing the data, we might gather all the necessary information in a single worksheet. It’s a very common problem or situation when data is divided across many worksheets or workbooks. There are many ways to merge the data from multiple tables into one table in excel.

You can download this Merge Table Excel Template here – Merge Table Excel Template

How to Merge 2 Tables in Excel?

We have given Customer data city wise in two tables. We have taken 20 records for this.

Sheet 1: Table 1: CustomerInfo

Merge Tables in Excel CustomerInfo 1

Sheet 2: Table 2: ProductDetails

Merge Tables in Excel ProductDetails 1

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

Below are the steps for merging these two tables:

  • Click on any cell in the Customer Info table. Go to the INSERT tab and click on the Table option under the Tables section. Refer to the below screenshot.

Merge in Excel step 1

  • Then Create a 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.

Merge in Excel step 2

  • 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.”

Merge Table Example 1-3

  • Follow the same steps for another table, “ProductDetails.” We have given the name “Products” to another table. Refer to the below screenshot.

Merge Table Example 1-4

  • Click on somewhere on the Customer_Info table then, Go to the Insert tab, and click on the Pivot Table option under the Tables section.

Merge Table Example 1-5

  • A dialog box for Create Pivot Table will appear. Tick on Checkbox “Add this data to the Data Model,” as shown in the below screenshot.

Merge Table Example 1-6

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

Merge Table Example 1-7

  • Click on the ALL tab in the Pivot Table Field section, and it will display all the tables created by us. Refer to the below screenshot.

Merge Table Example 1-8

  • Now click on the Relationships option under the Calculations section, as shown in the below screenshot.

Merge Table Example 1-9

  • It will open a dialog box for creating a relationship between these tables. Click on the New button. Refer to the below screenshot.

create relationship Example 1-10

  • It will again open a dialog box as shown below, and created tables are listed here.

Merge Table Example 1-11

  • As there is one field, “Order No.” is common in both the tables, hence we will create a relationship between these tables by using this common field/column.
  • Select Customer_Info under Tables section and Order No. field under the Column Section. Refer to the below screenshot.

Merge Table Example 1-12

  • Select another table Products under the Related table section and select Order No. field under the Related column section. Refer to the below screenshot.

create relationship Example 1-13

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

relationship Example 1-14

  • Now we can drag & drop the field accordingly to see the result. Click on the Customer_Info table, as shown in the below screenshot.

drag & drop Example 1-15

  • Drag fields Order No. , Customer Name, and the city under the row box.
  • Drag Age field under Filter box.
  • Drag Product field under Column Box and Values box for the count of products.

drag field Example 1-16

The final result is below:

Accordingly, as per your requirement, you can drag & drop the fields.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

result Example 1-17

Things to Remember About Merge 2 Tables in Excel

  • You 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.

Recommended Articles

This has been a guide to Merge Tables in Excel. Here we discuss how to Merge 2 Tables in Excel by matching a column along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Excel Data Model
  • Create a Pivot Chart in Excel
  • Use Pivot Table Slicer
  • Excel Table Styles
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Merge Table Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More