Merge Two or More Tables in Excel finalizes your journey towards achieving seamless data consolidation and comprehensive analysis. This pivotal process not only streamlines the integration of diverse datasets but also ensures that your insights are derived from a holistic view of the information. Embrace the power of merging tables in Excel to unlock new dimensions of data exploration, where clarity meets completeness. Let this skill be the cornerstone of your data management, paving the way for informed decisions, impactful reports, and a narrative told through unified data excellence.
In Excel, merging tables is a wonderful method to combine related tables, add data to an existing Excel table, or alter the range of data in a single table. It enables users to arrange their data sets and identify information. In this article, we will learn about a few simple techniques for merging tables.
This Content Covers:
- Why Merge Tables in Excel?
- How to Merge Two or More Tables in Excel?
- Merge with Copy Paste Method
- Using VLOOKUP Function
- Merge with INDEX & MATCH Function
- Using Power Query
1. Why Merge Two or More Tables in Excel?
While working in Microsoft Excel often we don’t have all the necessary information or data gathered in one single worksheet or in a single table. Without the complete set of information, the report we create will be incomplete. For example, in the picture below we have three different tables that can be in different worksheets or different workbooks. The first table has Seller, Product, and Amount information but it doesn’t have Seller ID or Region information in it. So, the report is incomplete here. This is why merging tables is necessary to combine these three tables into one to have all the information based on this dataset in a single data table.
2. How to Merge Two or More Tables in Excel?
If merging multiple tables becomes necessary then there are some easy techniques you can use to achieve this goal. Some of the useful method of merging tables are given below.
2.1 Merge with Copy Paste Method
Here, we Merge Two or More Tables in Excel using the Copy Paste Method. Consider the following spreadsheet data, which has two tables named Seller and Product. To combine tables, follow the steps below.
Step 1: Select the whole range of Product table and click CTRL+C to copy.
Step 2: Select cell B1 and press CTRL+V to paste the table in this column which will merge both the tables. You can keep copying and pasting tables one by one from same worksheet or from different worksheet or different workbook using this method if you have multiples tables in your worksheet.
2.2 Using VLOOKUP Function
Now here we have two tables in two different worksheets. Both of them have seller names and amount. We could simply use the copy and paste method here too to merge the data from the tables but the problem here is that the names of the sellers are in different order in two of the tables. So, copying the whole table or data of Amount table won’t merge the tables correctly as the account of amount value for these sellers in the table of Sheet1 won’t be in correct order. To merge these two tables, we can use VLOOKUP formula.
Step 1: Select cell C2 of Sheet 1 and insert the formula given below in that cell. Press Enter.
=VLOOKUP($A2,Sheet2!$A$1:$B$7,2,FALSE)
Step 2: Click and drag cell C2 to C7 to apply the formula in those cells too and the tables have been merged precisely. You can merge more than two tables from multiple worksheets using this function.
2.3 Merge with INDEX & MATCH Function
Now we have these two tables which contains data that we want to merge. We could think of using the VLOOKUP function to merge these two tables here to but the restriction that VLOOKUP has is it can’t do lookup from right to left. To merge these sorts of tables we can use INDEX and MATCH function in the below given manner.
Step 1: Select cell B2 and insert the formula given below.
=INDEX($E$2:$E$7,MATCH($A2,$F$2:$F$7,0))
Step 2: Drag B2 to B7 to merge the whole data of that table with this one.
2.4 Using Power Query
We have these three tables here Table 1, Table 2 and Table 3. We can merge Table 2 and Table 3 with Table 1 using Power Query.
Create Connection
To merge these tables together the first thing we need is to create connections among the tables in power query. Once the connection is created, we can easily merge the tables.
Step 1: Select any cell from Table 1 and click on Data>>From Table.
Step 2: From Power Query Editor, select File>>Close & Load To…
Step 3: Select Only Create Connection and click on Load. A connection has been created in Workbook Queries.
Step 4: Now repeat these steps in Table 2 and Table 3 also to create connections for them in the Workbook Queries.
Merge Tables
We can merge two tables at a time. Follow the steps below to merge these tables together.
Step 1: Go to Data>>New Query>>Combine Queries>>Merge.
Step 2: In the Merge dialogue box select Table 1 from the first drop-down box and Table 2 from the second drop-down box. Select Left Outer from the Join Kind box.
Step 3: Click on Seller column from both of the tables as this will work as a connector for merging these two tables. Click Ok.
Step 4: In the Power Query Editor dialogue box a new column has been added named Table2. Click the header of Table 2, drag and position it next to Seller column.
Step 5: After you position the new column after Seller column just click on the double pointed arrow from the header of Table2. From the prompt uncheck Seller as u already have that in Table 1 and also uncheck Use Original Column Name as Prefix. Click OK.
Step 6: Go to Close & Load>>Close & Load To..>>Only Create Connection and click on Load. A new connection has been created. We could have used Close and Load option and this would instantly merge Table1 and Table2. As we also have Table3 to merge with it so we just created a connection with which we can merge Table 3.
Step 7: Again, go to Data>>New Query>>Combine Queries>>Merge. Select Merge1 from the first drop-down and Table3 from the second drop-down. Click Product column in both of the tables. Choose Left Outer from Join Kind box and click OK.
Step 8: Click on the double pointed arrow from the header of Table3. From the prompt uncheck Product and uncheck Use Original Column Name as Prefix. Click OK.
Step 9: Drag and position Region column next to Product column.
Step 10: Select Close & Load.
Step 11: Three of the columns have been merged.
Application of Merge Two or More Tables in Excel
- Comprehensive Data Analysis:
- Combine tables containing related data to create a comprehensive dataset, facilitating in-depth analysis, and ensuring that all relevant factors are considered in decision-making processes.
- Consolidating Data from Multiple Sources:
- Merge data from different sources or departments (e.g., sales, finance, customer service) into a single table, providing a unified view for a holistic analysis of business operations.
- Simplifying Report Generation:
- Integrate tables with different aspects of information to generate detailed and comprehensive reports, reducing the need to cross-reference multiple sources and simplifying the reporting process.
- Streamlining Data Updates and Maintenance:
- Update and maintain large datasets more efficiently by merging new or updated data into existing tables, ensuring that all records are current and in sync.
- Enhancing Data Visualization and Presentation:
- Create more impactful charts and visual representations by merging tables to include all relevant data points, offering a richer, more detailed narrative of the data.
- Facilitating Data Cleaning and Preprocessing:
- Combine tables to align, clean, and preprocess data before analysis, ensuring consistency, accuracy, and readiness of data for advanced analytical tasks or machine learning models.
Merging tables in Excel is essential for maximizing the potential of data, enabling clearer insights, more efficient operations, and more informed decision-making by providing a unified, comprehensive view of the information.
For ready-to-use Dashboard Templates:
