How to Merge Data from Two or More Excel Sheets

Sometimes the Excel data you need is saved in multiple sheets or even multiple files. Whether it's referring to the data, or processing the data, it is more convenient to combine all the data in the same file or worksheet. Of course, you can merge them all into the same file or worksheet by copying and pasting. But when it comes to a large amount of data or lots of files, it will take a lot of time and effort. To fix this issue, here we will introduce merging data by using Power Query. Read on and figure out how to merge data from 2 or more Excel sheets by using Power Query.

Merge data from 2 Excel sheets using Power Query

Situation 1. Merge data from 2 sheets into one worksheet

Step 1. Create a new blank workbook, and use Power Query.

In the new workbook, click Data > New Query > From File > From Workbook.

Step 2. Choose an Excel file you want to merge.

In the pop-up window named "Import Data", double-click an Excel files that contain the data you want to merge. Then select the sheet you need in the "Navigator" window. After choosing a sheet, you can view its content in the column on the right side of the window.

Step 3. Load the data of the selected Excel file

Under the Navigator window, click "Load to". In the pop-up window, choose the Table format. The data can be loaded to the new worksheet or the existing one. Choose the location as you like and then click on the "Load" button.

Step 4. Load the data of another Excel spreadsheet.

Use Power Query in the same workbook. Follow the above steps to load the data of another file to the same workbook. Please notice that Query results cannot overlap a table or XML mapping. Therefore, avoid to overlap the existing data when choosing the location of the second table. You can select the initial position of the second file by clicking the icon as is shown.

The picture below is a result after the data is loaded. The data in the two workbooks are successfully placed in the same worksheet and become two separate tables.

Situation 2. Merge data from 2 sheets into one table

In order to process the data more conveniently, it is a better choice to merge the data into one table. Check how to do that below.

Step 1. Create a new blank workbook.

Step 2. Import the data of two Excel files into two separate worksheets of the new workbook.

Follow the first 3 steps in situation 1 to import the data of the first Excel file. Then when you load the data of the second one, remember to choose the file to be loaded to a new worksheet.

Step 3. Use the Append feature.

Click one of the tables, then click the "Query" tab and select "Append".

In the Append window, select Two tables. Then choose two sheets one by one. Click on the "OK" button.

Step 4. Import the data into one table

Preview the merged data in the "Power Query Editor". Then click Close & Load in the upper left corner, and the merged data will appear in the new worksheet.

The picture below is a result after the data is loaded. As you can see, the data in the two workbooks are successfully placed in the same worksheet and become a table. All the data of the two tables will be displayed, and the position where the data does not exist will be displayed in the form of a blank grid.

Merge data from more than 2 Excel sheets using Power Query

Way 1. Combine more than 2 Excel sheets using the Append function.

The Append function can not only combine data of 2 files, but also data of 3 or more files. You can merge the data in the way below:

Step 1. Create a new blank workbook.

Step 2. Import the data of all the Excel spreadsheets into separate sheets of the new workbook.

Step 3. Click one of the tables, then click the "Query" tab and select "Append".

Step 4. In the pop-up window, select Three or more tables. Add all the tables to be merged into the column boxes on the right, and click "OK" to confirm.

The append function will display the data of all worksheets, and the missing data will be displayed in the form of blank cells.

Way 2. Combine more than 2 Excel sheets in a folder.

It's a easier way to combine more than 2 Excel sheets. Follow the procedure below to combine 3 or more sheets in a folder.

Step 1. Organize the data in the folder.

Put the workbooks to be merged into a new folder. The data to be merged in each workbook should be placed on the first worksheet, and the worksheet should be named Sheet1.

Step 2. Choose the folder.

Create a new workbook. In the new workbook, click Data > New Query > From File > From Folder.

In the pop-up window, click on the "Browse" button to choose the folder. Click "OK".

In the next interface, click on Combine > Combine & Transform data.

Step 3. Choose the major worksheet.

In the interface named "combine files", select a sheet to be the major worksheet. Then click on "OK" to confirm.

After the major worksheet is selected, Excel will extract and integrate the data from the folder according to the major worksheet's header. It means that items that are not in the main worksheet will not be shown in the merged table.

Step 4. Load the data from the folder.

Preview the merged data in the "Power Query Editor". Then click Close & Load in the upper left corner, and the merged data will appear in the new worksheet.

Instead of copying and pasting data from Excel sheets, try to merge data of 2 or more Excel files using the Power Query we've described. Then you'll find how much a useful function can save your time and energy.