How to Merge Tables from Different Sheets in Excel (5 Easy Ways)

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.

How to Merge Tables from Different Sheets in Excel: 5 Easy Methods

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.

Steps:

• Go to E5 and write down the following formula

• Now, press ENTER to get the output.

• Finally, use the Fill Handle to AutoFill up to E14.

Read More: How to Merge Two Tables in Excel Using VLOOKUP

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.

Steps:

• Go to E5 and write down the following formula.

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

Steps:

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

Read More: How to Merge Two Tables in Excel

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.

Steps:

• 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

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.

Steps:

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

Download Practice Workbook

Download this workbook and practice while going through the article.

Conclusion

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.

<< Go Back to Merge Tables in Excel | Merge in Excel | Learn Excel