Md. Sourov Hossain Mithun
As an Excel user, it’s a common necessity to merge multiple sheets into one sheet or multiple sheets from multiple files. If are you searching for easy ways to do it then you have visited the right place we think. We hope this article will show you 3 easy methods to merge multiple sheets in Excel.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
Files that were used to merge:
3 Ways to Merge Multiple Sheets in Excel
Get introduced to our dataset first which has three sheets, containing the sales for three consecutive years.
1. Using Consolidate Tool to Merge Multiple Sheets
In our very first method, we won’t directly merge the sheets, we’ll merge the sales using a function to get every salesmen’s total sales over three years.
Steps:
- Activate any cell.
- Then click as follows: Data > Data Tools > Consolidate.
- Select the function from the Function drop-down box that you want to apply.
- Next, click on the up arrow icon from the Reference If you have sheets in other files then you will have to click Browse.
- Later, go to the first source sheet and select the data range.
- Then hit the ENTER button.
- After that, click the Add button to add that reference.
- Following the same procedures, select the data range from the other two sheets.
- Finally, just mark the options from the Use labels in section to set the labels and link to the source data and hit the ENTER button.
Now see, the sales from the three sheets are merged using the Sum function.
Read More: How to Merge All Sheets into One in Excel (6 Quick Ways)
2. Using Excel Power Query to Merge Multiple Sheets
To merge data from different sheets into one sheet, Excel Power Query is the most useful tool. It takes a large number of steps but is quite easy.
Steps:
- First, click as follows to add the sheets to the Power Query: Data > Get Data > From File > From Excel Workbook.
- Select the Excel file from where you want to merge sheets.
- Then click Open.
- Soon the Navigator dialog box will open up to select the sheets from the file. Mark the Select multiple items option and then mark the sheet name.
- Later, click on Transform Data.
Then you will get a Power Query window like the image below.
- Close the marked options from the Query Settings.
Now see, it’s showing the sheet names.
- After that, if the unnecessary sheets still exist then click on the drop-down icon from the Name
- Then mark the sheet names and press OK.
- We don’t need the other columns except for the Name and Data column. So to remove them, select the Name and Data columns and right-click your mouse.
- Next, select Remove Other Columns from the context menu.
- it’s only showing the sheet’s names. To merge them, click on the two-directional icon from the Data header.
- Soon after, you will get a drop-down list like this. Just click OK
Now the tables are expanded and merged into one sheet to the Power Query.
To get it in the regular Excel sheet, press the Close & Load option.
In a while, you will get the merged sheet in your Excel file as Excel Table.
Read More: How to Merge Two Excel Sheets Based on One Column (3 Ways)
3. Using VBA to Merge Multiple Sheets in Excel
In our last method, we’ll embed VBA to merge multiple sheets from multiple Excel files into another file. For that, the files should be placed in a specific folder. So I modified the dataset, and kept the sales report for two years in two different files. We’ll have to use the file path of these files in the codes.
Steps:
- Firstly, press ALT + F11 to open the VBA window.
- Next, click as follows to insert a module: Insert > Module.
- Then insert the following codes in the module-
Sub Merge_Sheets()
FilePath = "C:\Users\USER\Desktop\ExcelDemy\"
FileAddress = Dir(FilePath & "*.xlsx")
Do While FileAddress <> ""
Workbooks.Open Filename:=FilePath & FileAddress, ReadOnly:=True
For Each FileSheet In ActiveWorkbook.Sheets
FileSheet.Copy After:=ThisWorkbook.Sheets(1)
Next FileSheet
Workbooks(FileAddress).Close
FileAddress = Dir()
Loop
End Sub- Later, click on the Run button to run the codes.
Note: Place the file path in the marked position of the codes. But keep in mind, we’ll have to type an extra “\” at the end of the file path.
Code Breakdown:
- First, I created a Sub procedure- Merge_Sheets.
- Then mentioned the file path using the variable FilePath.
- Later, used Do While and For Each loop to copy the sheets from the file location.
Now take a look, the files added to our current workbook.
Read More: How to Merge Excel Sheets into One Workbook (4 Suitable Ways)
Conclusion
That’s all for the article. I hope the above procedures will be good enough to merge multiple sheets in Excel. Feel free to ask any questions in the comment section and give me feedback.
