How to Merge Tables in Excel in 3 Easy Methods
Jun 12, 2023
Do you want to learn how to merge tables in Excel?
Merging tables can be tricky and time-consuming if done incorrectly. Whether you need to merge data from multiple sources into one format, our step-by-step guide will help you understand a seemingly difficult process.
Read on as we cover the following:
-
Merge Tables in Excel
-
Merge Tables by Using the Copy-and-Paste Method
-
Merge Tables by Using the VLOOKUP Function
-
Merge Tables by Using the INDEX and MATCH Functions
-
Final Thoughts on How to Merge Tables in Excel
-
Frequently Asked Questions on How to Merge Tables in Excel
Merge Tables in Excel
Microsoft Excel has become a fundamental tool for organizing data, and the ability to merge tables can save time and streamline the process.
Multiple methods exist to merge Excel tables, each with unique advantages.
Important details to remember before merging Excel tables.
-
Before merging using VLOOKUP, checking for duplicate entries in the lookup value is important to avoid errors.
-
After applying a merging tables formula, use the Paste special feature to remove it. Otherwise, it may affect your merged data table if there are other dependent tables.
-
In case the VLOOKUP formula generates an error, you can use the IFERROR function to remove it.
-
If you have data spread across multiple worksheets with identical headings, you can search for VBA code to merge the worksheets.
Merge Tables by Using the Copy-and-Paste Method
Merging two tables by using the copy-and-paste method is the easiest to do. It does not involve any formula or rocket science. Moreover, you can use the copy-and-paste method to combine the two.
In the example below, we have two months of sales tables. One is Jan, and the other is the Feb Month sales data.
There are tables with identical column headers that don't merge. We can perform two simple merges: creating a new table or adding data to the existing table.
Create a new table to merge tables.
-
Select the table with a heading named Feb.
-
Copy the table.
-
Select a cell to paste your table.
After pasting the table, you now have one merged table.
Adding data to the existing table to merge tables.
You can modify the data structure as indicated in the table below to increase the data's flexibility for analysis.
In the example, we added one table below the other and inserted a new column called Month.
Adding data to the existing table lets you look at the information differently. Moreover, we can use this new table to make a pivot table. Remember that the way we arrange the tables is important.
Merge Tables by Using the VLOOKUP Function
If you know how to use the VLOOKUP function, it will be easier to put tables together.
In the example below, we have three tables of employee information. The first table shows their ID number and name; the second table contains their ID number and department. Meanwhile, the third table shows their ID number and salary.
By comparing the headings in all three tables, we can notice that they all share the heading "EMP ID."
Therefore, we can merge the tables using this heading. The format for merging should be as follows.
To combine the Department and Salary columns from Table 2 and Table 3, you can use the VLOOKUP function. If you're familiar with this formula, the process should be straightforward.
-
Use the VLOOKUP formula for the department table from Table 2 for the matching column.
-
Use the Auto-fill feature in Excel to fill the empty cells in the table.
-
Apply the same steps from 1 and 2 except for the VLOOKUP formula because it is based on Table 3 to find the exact match.
We have merged all the data from multiple tables using the VLOOKUP method.
Merge Tables by Using the INDEX and MATCH Functions
The INDEX & MATCH is just another formula like VLOOKUP that works similarly.
-
To merge the Department column in Table 2, apply the INDEX and MATCH formula on cell L3. Use the following formula:
-
Use the Auto-fill feature to fill the empty cells.
-
Apply the same steps for the Sales column.
Final Thoughts on How to Merge Tables in Excel
The ability to merge tables in Excel can be a helpful skill for any user. After all, it simplifies the data by consolidating multiple information cells into one. By merging, you can apply formatting across many tabs at once.
Visit Simple Sheets for more easy-to-follow guides and examples, and remember to read the related articles section of this blog post.
For the most straightforward Excel video tutorials, subscribe to Simple Sheets on Youtube!
Frequently Asked Questions on How to Merge Tables in Excel
What functions can I use when merging tables in Excel?
Different functions can merge Excel tables, such as VLOOKUP, INDEX, MATCH, and CONCATENATE.
Can the Excel power query function merge two or more tables?
Yes, Excel's Power Query tool from the data tab can easily merge multiple tables to create a single table. The Power Query function can also merge tables from multiple worksheets.
Is it possible to automate the merging of tables in Excel?
You can automate the merging of tables in Excel using VBA (Visual Basic for Applications). By writing VBA macros, you can define custom merging rules and automate the process.
Related Articles:
Excel Workbook vs. Worksheet: What's the Difference?
Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
