Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In Excel, we often work with large datasets. While working with these datasets, we frequently need to merge tables from multiple sheets to analyze them properly. In this article, I will explain 5 ways in Excel to merge tables from different sheets.
Download Practice Workbook
Download this workbook and practice while going through the article.
5 Easy Methods to Merge Tables from Different Sheets in Excel
This is the worksheet I am going to use to explain the methods on how to merge tables from different sheets.in Excel. We have several students along with their Student ID and their Marks. 1st sheet contains marks for Physics and the 2nd one is for Math. I am going to merge the tables for different subjects to describe the methods.
1. Apply VLOOKUP Function to Merge Tables from Different Sheets
Now, I will show how to use the VLOOKUP function to merge tables from different sheets. I will add the marks for the math column using this method. Let’s do it step by step.
- Go to E5 and write down the following formula
- B5 is the lookup_value.
- Excel will look for the value in B5 (11001) in the range B5:D14 from the Dataset (Math)
- Then, 3 states that Excel will find 3rd column from the range.
- Finally, FALSE is for Exact Match.
- Now, press ENTER to get the output.
- Finally, use the Fill Handle to AutoFill up to E14.
2. Use a Combination of INDEX and MATCH Functions to Merge Tables
In this section, I will show how to merge tables from different sheets using a combination of INDEX and MATCH functions. I will add the column of marks for math using this method.
- Go to E5 and write down the following formula.
=INDEX('Dataset (Math)'!$D$5:$D$14,MATCH('Dataset (Math)'!D5,'Dataset (Math)'!$D$5:$D$14,0))
- MATCH(‘Dataset (Math)’!D5,’Dataset (Math)’!$D$5:$D$14,0)
- Output: 1
- INDEX(‘Dataset (Math)’!$D$5:$D$14,MATCH(‘Dataset (Math)’!D5,’Dataset (Math)’!$D$5:$D$14,0)) → This becomes,
- INDEX(‘Dataset (Math)’!$D$5:$D$14,1
- Output: 76
- Then, press ENTER to get the output.
- AutoFill up to E14.
3. Merge Tables from Different Sheets Using Consolidate Feature
You can also use the Consolidate Feature to merge tables. The difference here is that you cannot directly merge the columns. However, you can apply some operations in this feature. For example, I am going to get the summation of the marks for physics and math using this method.
- Select D5:D14.
- Then, go to the Data
- After that, select Consolidate feature (see image).
- Choose the function Sum.
- Select the reference for the marks of Physics.
- Then, click Add.
- Similarly, add the marks for Math.
- Then, click OK.
- Excel will add the marks.
4. Use PowerQuery to Merge Tables from Different Sheets
The first method that I am going to discuss is the use of the PowerQuery feature to merge tables. Let’s do it step by step.
- First of all, you must convert data ranges to tables. To do so,
- Select B4:D14.
- Then, press CTRL+T.
- Create Table box will appear. Check the My table has headers
- Then, click OK.
- Excel will create a table.
- Rename the table as Physics.
- Similarly, create a table and name it Math.
- After that, go to the Data
- Then, select Get Data.
- After that, choose From Other Sources.
- Finally, select Blank Query.
- Power Query Editor will appear.
- Write down the following formula,
- Press ENTER.
- Then, select the icon shown in the image.
- Next, uncheck the ‘Use original column name as prefix’ box.
- Then, click OK.
- Excel will merge the tables.
- Then, click Close & Load.
- Excel will load the combination in a new worksheet.
- There is a new row Query1!ExternalData_1. To get rid of this, select the drop-down.
- Then, uncheck the box for Query1!ExternalData_1.
- After that, click OK.
- You will get the tables merged. The final output will be like this
Read More: How to Combine Two Tables Using Power Query
5. Use VBA Code to Merge Tables from Different Sheets
Now, I will use a VBA code to merge tables. I will combine the marks of physics and math in a new sheet.
- Press ALT+F11 to open Visual Basic Editor.
- Go to the Insert
- Select Module.
- A new module will appear. Write down the following code.
Sub MergeTable() Worksheets("VBA").Range("B4:D14").Value = Worksheets("Dataset (Physics)").Range("B4:D14").Value Worksheets("VBA").Range("E4:E14").Value = Worksheets("Dataset (Math)").Range("D4:D14").Value End Sub
- Press F5 to run the code. Excel will merge the columns from those tables.
- Format as you wish.
Things to Remember
- Use absolute reference to lock a cell.
- The VLOOKUP function can only look to the right to get data.
In this article, I have explained 5 easy ways in Excel to merge tables from different sheets. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.