Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Merge Multiple Excel Files into One Sheet (4 Methods)

While working with multiple Excel files with the same type of data i.e., the number of columns and their orders are the same in all these Excel files, then you might want to merge them into a single file to process the data easily. In this tutorial, I will show you how to merge multiple excel files into one file or sheet so that you do not have to switch between multiple files.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


4 Easy Methods to Merge Multiple Excel Files into One Sheet

Let’s assume a scenario where we are working with the sales volumes of various products for different months of the year of a shop that sells computers and different accessories. We have these sales volumes of different months in individual Excel sheets. Now, we will merge these multiple Excel files into one sheet using various methods. We will merge these files by copying sheet tabs, using power queryData Consolidate, and with VBA script.

Merge Multiple Excel Files into One Sheet


1. Merge Multiple Excel files into One by Copying Sheet

Step 1:

  • Open all the workbooks you wish to combine. That means you have to open the workbooks that you want to merge along with the one where you want the merge to take place. For this example, let’s assume we have two workbooks. One of them contains the sales volume from January to March while the other has the sales volume from April to June. We have named these workbooks January-March.xlsx and April-June.xlsx respectively for ease of understanding. Now, we will open both of these workbooks.
  • We want to merge the sales volume from April to June to the sales volume from January to March. So, we will select the 3 worksheets from the April-June.xlsx workbook that we want to copy to the January-March.xlsx
  • We can use one of the following two easy ways to select multiple worksheets.
    • If we have to select adjacent sheets, we can click on the first sheet tab that we want to copy, press, and hold the SHIFT key, and then click on the last sheet tab. This will select all worksheets in between.

Select All the Sheets to Merged in Excel

    • To select non-adjacent sheets, hold the CTRL key and click on each sheet tab individually.

Step 2:

  • With all worksheets selected, right-click on any of the selected tabs, and then click Move or Copy.

Click on Move or Copy in Excel

  • While we are in the Move or Copy dialog box, we will follow these below steps:
    • From the Move selected sheets to book drop-down list, select the workbook into which you want to merge other files. In our case, we will select the January-March.xlsx

Merge Multiple Excel Files into One Sheet

    • Specify where you want to insert the copied sheet. In our case, we choose the move to end.
    • Select the Create a copy box if you want the original worksheets to remain in the source file.
    • Click OK to complete the merge.

Merge Multiple Sheets in One in Excel

  • You will now see that all the sheets from April-June.xlsx have been merged into the January-March.xlsx

Merged Sheets in Excel

Related Contents: How to Combine Sheets in Excel (6 Easiest Ways)


2. Merge Multiple Excel Files into One Using Power Query

In the previous method, we have merged sheets from two workbooks together. But we do not merge them into a single sheet. We can merge all the sheets of the same workbook using the power query of Excel. To do that:

Step 1:

  • First, from the Data tab, open Power Query in your worksheet like the picture below.

Merge Multiple Excel Files into One Sheet

  • Upon selecting the From Workbook option in the power query, you will be prompted to select your workbook. Select the Excel file which has all the worksheets you need to be merged together. In our case, we have the January-March.xlsx file which has now all the sales volume for the 6 months. We will select the file and click Import like the image below.

Select the Excel File to Merge the Excel Files

Step 2:

  • After selecting the workbook, a new dialogue box titled Navigator will appear. Tick the Select Multiple Item.
  • Now, select all the worksheets. Click on the Load button on the lower-right corner of the dialogue box.

Merge Multiple Excel Files into One Sheet

  • Now you will see the workbook query after this on the right side of your worksheet.

Workbook Queries on the Right Side of the Workbook

Step 3:

  • Now, click on New Query just like we did in Step 1. Select Combine Queries from the drop-down list that will appear.
  • Another drop-down list will appear. Select Append from that list.

Merge Multiple Excel Files into One Sheet

  • Now, a new prompt box will appear titled Append. Select the Three or more tables. Then, select all the sheets from Available tables and click Add.

Select the Sheets from Available Tables

  • You will find that the sheets have been added on Tables to append lists on the right side. But the sheets are not in the right order. You have to re-order the sheets before you can merge them. Use the Up and Down arrow on the right side to reorder the sheets.
  • After reordering the sheets, press OK.

  • Another window will now open. Click on the Remove Row drop-down from there and select Remove Blank Rows.

Remove Blank Rows

Step 4:

  • You will see that we have our title Merge Multiple Excel Files into One multiple time in the dialogue box. Click on the Filter arrow icon just beside Column 1 and unselect the box that contains the title. And then click OK. All the titles will be removed from the list.

Merge Multiple Excel Files into One Sheet

  • Now, click on Click on Close & Load drop-down list from the upper-left corner of the box and select Close & Load.

Click on Close and Load to Merge the Excel Files into One Sheet

  • A new sheet titled Sheet 4 will now appear containing all the data from all of the 6 sheets. Right-click on the empty columns. Click on Delete and then select Table Columns to delete these columns one by one.

All the Excel Sheets Merged into One Single Sheet

  • Now select the existing 3 columns containing the merged information from the 6 sheets. Go to the Data tab and then click on the Filter The filter will be removed from these columns.

Merge Multiple Excel Files into One Sheet

  • Now all the information of 6 sheets containing the sales information of the 6 months of the year is merged in a single sheet.

Excel Sheets are Merged into One

Related Content: How to Combine Columns into One List in Excel (4 Easy Ways)


3. Merge Multiple Excel Files into One Using Data Consolidate

Suppose you want to get the total sales value of each product for 6 months. You can find out that data using the consolidate feature on the data tab. Follow the below steps:

Step 1:

  • First, open a new worksheet. We have opened a worksheet and named it Total Sales.
  • In the new worksheet click on the Consolidate feature under the Data Tools option of the Data ribbon.

Click on the Consolidate Under the Data Ribbon

  • In the Consolidate dialogue box, Select Sum in the Function drop-down option. And in the Reference section write January!$B$4:$D$14. Put tick marks on the Top row and Left column option below and then press Add.

Add the Excel Sheets to be Merged in Reference Box

  • Now in the Reference section write down February!$B$4:$D$14 to insert the data from February and press Add. After this Press OK.

Add all the Sheets in Reference Section

  • We will do the same for the rest of the sheets. And then click OK.

Merge Multiple Excel Files into One Sheet

  • We will now get the total sales volume for every product in the Total Sales.

Consolidated Total Sales Volume in One Single Excel Sheet


4. Merge Multiple Excel Files Using VBA

The best and fastest way to merge Excel files is to use VBA macros. If you perform this task regularly, then the VBA macro will come in handy. Just follow these steps:

Step 1:

  • Open a new workbook. We have opened one and named it Merge.
  • We have put all the Excel files that we want to combine into a folder. For this tutorial, I have created a folder named MergeExcel in my J drive and kept our two workbooks inside it.
  • Then, go to the Visual Basic tab from the Developer You can also press ALT+F11 to open the Visual Basic. A new Microsoft Visual Basic for Applications window will be displayed.

Open the Visual Basic Tab

  • Then Click Insert →Module.

Insert Module in Excel Visual Basic

Step 2:

  • After that enter the following code into the Module.
Sub MergeExcelWorkbooks()

Dim FolderPath As String

Dim Filename As String

Dim Sheet As Worksheet

Application.ScreenUpdating = False

FolderPath = "J:\MergeExcel\"

Filename = Dir(FolderPath & "*.xls*")

Do While Filename <> ""

Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Next Sheet

Workbooks(Filename).Close

Filename = Dir()

Loop

Application.ScreenUpdating = True

End Sub
Note: We Have used J:\MergeExcel\ as the folder path because we have stored our Excel files inside the folder named MergeExcel which is located in J drive. You will use the path to the folder you used to keep your Excel files.
  • Then click the Run button to execute the code.

Click on the Run Button to Execute the Script

  • You will now see all the 6 Excel worksheets from the two workbooks are merged into the Merge.

Merge Multiple Excel Files into One Sheet

Related Contents: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)


Things to Remember

  • While merging Excel worksheets by copying them, all the Excel files should be opened.
  • If you do not have a Developer tab, you can make it visible in File > Option > Customize Ribbon.
  • To open VBA editor Press ALT + F11.
  • You can press ALT + F8 to bring up the Macro window.

Conclusion

In this article, we have learned to merge multiple Excel files into one sheet using different ways. I hope from now on you will find it very easy to merge Excel files. However, If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo