Open In App

How to Combine Data from Multiple Sheets in one File?

Improve
Improve
Like Article
Like
Save
Share
Report

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. 

Power-query-section

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.

Creating-table1-mid_sem_1Table2-mid_sem_2Table3-end_sem

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

Clicking-get-data

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

Going-to-from-workbook

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

File-containing-all-three-sheets

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

Merging-all-three-tables

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

Opening-mid_sem_1

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

Clicking-merge-queries

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

Merging

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.

Selecting-the-common-column

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

New-column-added

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. 

Unchecking-box

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

Mid_sem_2-marks-appended

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.

Merging

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.

Unchecking-box

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

End-sem-marks

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

Sorting-roll-no.

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

Clicking-close-and-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.

Worksheets-appears

Step 18: The final merged table appears. 

Final-merged-table


Last Updated : 03 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads