How to Merge Multiple Sheets in Excel (3 Easy Ways)

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.

How to Merge Multiple Sheets in Excel


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.

Using Consolidate Tool to Merge Multiple Sheets

  • 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.

Using Consolidate Tool to Merge Multiple Sheets

  • Later, go to the first source sheet and select the data range.
  • Then hit the ENTER button.

Selecting a Range

  • 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.

Using Excel Power Query to Merge Multiple Sheets

  • Select the Excel file from where you want to merge sheets.
  • Then click Open.

File Selection

  • 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.

Selecting Multiple Sheets to Merge

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.

Using Excel Power Query to Merge Multiple Sheets

  • it’s only showing the sheet’s names. To merge them, click on the two-directional icon from the Data header.

Using Excel Power Query to Merge Multiple Sheets

  • Soon after, you will get a drop-down list like this. Just click OK

Using Excel Power Query to Merge Multiple Sheets

Now the tables are expanded and merged into one sheet to the Power Query.

Using Excel Power Query to Merge Multiple Sheets

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.

Using VBA to Merge Multiple Sheets in Excel

Steps:

  • Firstly, press ALT + F11 to open the VBA window.
  • Next, click as follows to insert a module: Insert > Module.

Using VBA to Merge Multiple Sheets in Excel

  • 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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo