How to Combine Data from Multiple Sheets in one File?

Last Updated : 03 Mar, 2022

Merging data into a single sheet is a crucial and significant skill to know. For example, you have the monthly sales of a company in the 12 worksheets of excel. Now you want to calculate the net revenue made in the entire year. So, we need to merge all the data in the 12 excel sheets, this task could be easily achieved by power query in excel. Let's learn how to merge data from all sheets in a selected file using a power query.

Power Query

Power Query is one of the very powerful tools in excel. Some important points of power query are:

Power query can save more than 50% of your time.
Power query comes with triple power. It has functionalities of Advance Excel, SQL, and VBA.
VLOOKUP function can be used in just a few mouse clicks.
If you are using an excel version greater than 2016 then you will get a built-in power query. For versions older than this, the power query needs to be added separately.

Power query is already built-in in excel. Go to Data Tab, Get & Transform Data, and Queries & Connections section is the power query for users having the 2019 excel version.

Merging data from all sheets in a file

The best way to understand merging sheets in a file is by an example, given a data set of three worksheets sheet1, sheet2, and sheet3. Each sheet contains a table name mid_sem_1, mid_sem_2 and end_sem respectively. The table in sheet1 has columns as Roll No. , Name, and Mid Sem 1 marks. The table in sheet2 has columns as Roll No., Mid Sem 2 marks. The table in sheet3 has columns as Name and End Sem marks. The name of this workbook is geeks_for_geeks.xlsx.

Following are the steps to merge all the sheets in the selected file:

Step 1: Press Ctrl + N to create a new file. Go to the Data tab, and click on Get Data.

Step 2: Go to From File and click on From Workbook.

Step 3: Open file geeks_for_geeks.xlsx. A navigator dialogue box appears.

Step 4: Select the tables you want to transform and merge data from different excel sheets.

Step 5: A power query editor appears. Go to the mid_sem_1 table.

Step 6: In the home tab, click on Merge Queries. A dialogue-box name Merge appears.

Step 7: Select the table you want to merge with mid_sem_1. For example, mid_sem_2.

Step 8: Select the column that is common in both tables. For example, Roll No. is the attribute that is common in both tables. Click Ok.

Step 9: A new column in table mid_sem_1 is added. The name of the attribute is mid_sem_2.

Step 10: Click on the double-sided arrow and select the columns you want to add to this table. Uncheck the box Use the original column name as a prefix to have the original column names in your table.

Step 11: The mid sem 2 marks get appended in your table name mid_sem_1.

Step 12: Repeat step 6. Go to the home tab and click on Merge Queries. A Merge dialogue box appears. Select the table name end_sem. Now, select the column which is common in the mid_sem_1 and end_sem table. Click Ok.

Step 13: A column name end_sem appears in mid_sem_1 table. Click on the double-sided arrow. Select the column you want to add to the merged table. For example, End sem marks. Uncheck the box Use the original column name as a prefix. Click Ok.

Step 14: A new column name End sem Marks is appended in the table name mid_sem_1.

Step 15: Sort the Roll No. in ascending order.

Step 16: The table is ready. In the home tab, click on Close & Load.

Step 17: The merged table is created in sheet name mid_sem_1. We can see that there is a total of 4 worksheets. The 3 worksheets are redundant. Delete all the worksheets except the sheet name mid_sem_1. Rename the sheet name from mid_sem_1 to final_result.