ASM Arman
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 query, Data Consolidate, and with VBA script.
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.
-
- 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.
- 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
-
- 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.
- You will now see that all the sheets from April-June.xlsx have been merged into the January-March.xlsx
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.
- 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.
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.
- Now you will see the workbook query after this on the right side of your worksheet.
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.
- 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.
- 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.
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.
- Now, click on Click on Close & Load drop-down list from the upper-left corner of the box and select Close & Load.
- 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.
- 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.
- Now all the information of 6 sheets containing the sales information of the 6 months of the year is merged in a single sheet.
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.
- 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.
- 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.
- We will do the same for the rest of the sheets. And then click OK.
- We will now get the total sales volume for every product in the Total Sales.
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.
- Then Click Insert →Module.
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- Then click the Run button to execute the code.
- You will now see all the 6 Excel worksheets from the two workbooks are merged into the Merge.
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!!!
