Merge Two Tables in Excel (Table of Contents)
Merge Two Tables in Excel
If you are a finance professional merger and acquisition is not a strange topic to you. Merging is nothing but combining two or more things. The concept is the same in excel as well, bringing together two or more tables from the same worksheet or from the same workbook or from different workbook is called Merging Tables in Excel. When we are conducting analysis we may see the data in different tables or different worksheets. With the help of identical columns or heading, we can merge two tables in excel. Having all the data in the single table itself is almost a dream task for anybody in the office. However, we have some techniques to merge two or more tables in excel. In excel we have one of the Add-in called Power Query for Excel 2010 and 2013 versions and for Excel 2016 it is a built-in add-in. But in this article, I am not going to touch Power Query but I will discuss a few other techniques to merge tables in excel.
Examples of Merge Two Tables in Excel
Example #1: Merge with Copy Paste Method
This is the easiest method to do. It does not involve any kind of formula or rocket science. We just simply do the copy & paste method to combine two. I have two months of sales tables. One is Jan and another one is Feb month sales data.
I have two identical tables here but not together. We can do two simple merges here. One is creating a new table altogether or add data to the existing table. If you observe the headings of both the tables are same.
Alternative 1
Since I have the region as the same heading I can merge table like the below one.
In the above image, I have excluded Region heading because it is common for both the tables.
Alternative 2
To make the data more flexible for analysis I can just modify the data structure as shown in the below table.
Now look at the above table, I have merged the one below the other but I have added one more column as Month. This is to analyze the data by applying the pivot table, the above data structure is important. We can easily apply pivot table for the above table.
Example #2: Merge with VLOOKUP Method
Who does not know VLOOKUP? All most all the excel users are aware of it, I hope you are also aware of it. If you the full usage of VLOOKUP, merging tables will be easier for you. I have three tables of employee’s information. The first table shows the Emp ID and their name, the second table shows the Emp ID and their department, the third table shows the Emp ID and their salary information.
If you look at all the three tables EMP ID is the common heading in all the three tables. Based on this common heading we can merge all the three tables together. Merging should be in the below format.
I need to merge Department and Salary columns from Table 2 and Table 3. If you are aware of the VLOOKUP formula then this will be a walk in the park.
- Apply VLOOKUP formula for Department from table 2 :
- Apply VLOOKUP formula on cell N2 of department column :
- Apply the formula as shown below :
- The result is shown in the below image :
- Now drag down the formula to all the empty cells :
- Apply VLOOKUP formula for Salary from table 3:
- Apply VLOOKUP formula on cell O2 of Salary column :
- Apply the formula as shown below :
- The result is shown in the below image :
- Now drag down the formula to all the empty cells :
Now we have merged all the data from different table together by applying the VLOOKUP method.
Example #3: Merge with INDEX & MATCH Method
INDEX & MATCH would be a new formula for you. But nothing to worry it is just an alternative formula for VLOOKUP formula. It works exactly the same as the VLOOKUP formula.
- Use below formula for merging Department column from Table 2:
- Apply VLOOKUP formula on cell L3 of department column :
- Apply the formula as shown below :
- The result is shown in the below image :
- Now drag down the formula to all the empty cells :
- Use below formula for merging Salary column from Table 3 :
- Apply VLOOKUP formula on cell N2 of department column :
- Apply the formula as shown below :
- The result is shown in the below image :
- Now drag down the formula to all the empty cells :
Things to Remember about Merge Two Tables in Excel
- POWER QUERY can merge many tables together. In Excel 2010 and in 2013 it is an add-in and in excel 2016 it is a built-in function.
- If you are merging by using VLOOKUP you need to be careful of duplicate entries in lookup value. Check for duplicate values before you proceed.
- Once the formula is applied remove the formula by using paste special method. Because it will hamper your merged data table if something happens to other dependent tables.
- In case of errors, you can use IFERROR function to get rid of errors generated by VLOOKUP formula.
- If the data is in many worksheets with same headings you can search for VBA code to combine worksheets together.
Recommended Articles
This has been a guide to Merge Two Tables in Excel. Here we discuss the Merge Two Tables in Excel and how to Merge Two Tables in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
