Merge Excel File (Excel Features, Formula, VBA & Power Query)

In this Excel tutorial, you will learn how to merge data in Excel file with manual copying, merge in Excel using the move or copy option from the sheet tab, merge Excel files by pasting links, merge Excel files using a combination of functions, combine multiple Excel files using Power Query, combine all Excel files into a new workbook as individual sheets, combine all Excel files into a single sheet in a new workbook, combine all Excel files into a single worksheet in an active workbook.

While preparing this article, we used Microsoft 365 for applying all operations, but they are also applicable in all Excel versions.

When some people work in a group, they can work on the same workbook in Excel. Additionally, they can work on their part and finally merge all the files together. Therefore, It saves time and helps productivity. Sometimes, we work with numerous data points spread across multiple files, and we can merge all the data into a single master worksheet. This not only helps to organize and visualize the data but also helps to work efficiently. Furthermore, we can use these methods for data analysis, budgeting, financial reporting, data migration, updating databases, etc.

Download Practice Workbook

How Many Ways to Merge Multiple Excel File into One?

There are 5 methods to merge multiple files in Excel. We will discuss them in the following section.

1. Merge Data in Excel File by Copying Manually

You can merge data using the copy-paste feature in Excel. Use the context menu or keyboard shortcut to merge data in Excel.

• First, copy the data using the context menu or the Ctrl+C shortcut.

• Now, paste it on your desired sheet using the menu or Ctrl+V shortcut.

• This is how you can manually copy and merge data.

2. Using the Move or Copy Option from Sheet Tab to Merge in Excel

To move or copy, you can use the menu from the sheet tab in Excel. Follow the steps attentively to learn how to do that.

• First, select all the tabs you want to copy or move. Then right-click on the mouse and select the Move or Copy… option.

• After that, a new dialogue box will appear. Follow the steps to copy or move.

• Finally, the sheets will be added to the workbook.

3. Merge Excel File by Pasting Links

Surely, you can merge Excel files by pasting links. It is a very easy method, and you can learn it from the steps below.

• First, copy the first data-containing cell of your sheet.

• Then, paste it as a link to your desired location.

• Now, in the formula bar, change the reference from absolute to relative.

• Finally, drag the Fill Handle tool to copy the full data.

4. Using Formula to Merge Excel Files

The combined Excel formula to merge Excel files is very simple. You can apply it from the following section.

• First, insert the following formula in cell B13.

• Finally, drag the Fill Handle tool to complete the dataset.

5. Using Power Query to Combine Multiple Excel Files

It needs some steps to use Power Query to combine multiple Excel files. Follow the steps below to learn the method.

• First, open a new Excel file. Then, to open Power Query Editor, go to Data >> Get Data >> From File >> From Folder.

• Next, it will load the files; click on Transform Data.

Click on the image to enlarge.

• After that, select the Content and the Name Then, right-click on the mouse and choose Remove Other Columns option from the Context Menu.

• Then, click on the Add Column tab and choose Custom Column. Next, name the new column and insert the following formula in the Custom column formula box. In the custom column formula box, we used the following formula:

• Now, press OK.

Click on the image to enlarge.

• Later, expand the Objects column and unmark the box in the second step.

• Following, pick the Data and Item columns and select Remove Other Columns from the Context Menu.

• Again, expand the Data column.

• Now, the combined data is shown in the Power Query Editor.

Click on the image to enlarge.

• Next, click on the File tab and choose Close & Load.

• Finally, the data will be on a new sheet in Excel.

How to Merge Excel Files with VBA?

There are two ways to merge Excel files using VBA. We have given the codes below.

1. Combine All Excel Files into a New Workbook as Individual Sheets

Absolutely, you can combine all files into a new workbook as individual sheets. Follow the steps below to be able to do it yourself.

• First, we will open the Developer tab and select the Visual Basic command.

• After that, from the Insert option, we will choose the new Module to write the VBA code.

• Now, insert the below-mentioned VBA code in the module.

Sub MergeMultipleSheetsToNew()
On Error GoTo eh
'declare variables to hold the objects required
Dim wbDestination As Workbook
Dim wbSource As Workbook
Dim wsDestination As Worksheet
Dim wb As Workbook
Dim sh As Worksheet
Dim strSheetName As String
Dim strDestName As String
Dim iRws As Integer
Dim iCols As Integer
Dim totRws As Integer
Dim strEndRng As String
Dim rngSource As Range
'turn off the screen updating to speed things up
Application.ScreenUpdating = False
'first create new destination workbook
Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data
For Each wb In Application.Workbooks
If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
Set wbSource = wb
For Each sh In wbSource.Worksheets
'get the number of rows and columns in the sheet
sh.Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
iRws = ActiveCell.Row
iCols = ActiveCell.Column
'set the range of the last cell in the sheet
strEndRng = sh.Cells(iRws, iCols).Address
'set the source range to copy
Set rngSource = sh.Range("A1:" & strEndRng)
'find the last row in the destination sheet
wbDestination.Activate
Set wsDestination = ActiveSheet
wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
totRws = ActiveCell.Row
'check if there are enough rows to paste the data
If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
GoTo eh
End If
'add a row to paste on the next row down
If totRws <> 1 Then totRws = totRws + 1
rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
Next sh
End If
Next wb
'now close all the open files except the one you want
For Each wb In Application.Workbooks
If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
wb.Close False
End If
Next wb
'clean up the objects to release the memory
Set wbDestination = Nothing
Set wbSource = Nothing
Set wsDestination = Nothing
Set rngSource = Nothing
Set wb = Nothing
'turn on the screen updating when complete
Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

• Run this code, and you will get all the sheets in a new workbook.

Read More: Combine Multiple Workbooks to One Workbook

2. Combine All Excel Files into a Single Sheet in a New Workbook

To combine all Excel files into a single sheet in a new workbook, you need to run the following VBA code.

• First, insert the VBA code in the module.

Sub MergeMultipleSheetsToActive()
On Error GoTo eh
'declare variables to hold the objects required
Dim wbDestination As Workbook
Dim wbSource As Workbook
Dim wsDestination As Worksheet
Dim wb As Workbook
Dim sh As Worksheet
Dim strSheetName As String
Dim strDestName As String
Dim iRws As Integer
Dim iCols As Integer
Dim totRws As Integer
Dim rngEnd As String
Dim rngSource As Range
'set the active workbook object for the destination book
Set wbDestination = ActiveWorkbook
'get the name of the active file
strDestName = wbDestination.Name
'turn off the screen updating to speed things up
Application.ScreenUpdating = False
'first create new destination worksheet in your Active workbook
Application.DisplayAlerts = False
'resume next error in case sheet doesn't exist
On Error Resume Next
ActiveWorkbook.Sheets("Consolidation").Delete
'reset error trap to go to the error trap at the end
On Error GoTo eh
Application.DisplayAlerts = True
'add a new sheet to the workbook
With ActiveWorkbook
Set wsDestination = .Sheets.Add(After:=.Sheets(.Sheets.Count))
wsDestination.Name = "Consolidation"
End With
'now loop through each of the workbooks open to get the data
For Each wb In Application.Workbooks
If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
Set wbSource = wb
For Each sh In wbSource.Worksheets
'get the number of rows in the sheet
sh.Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
iRws = ActiveCell.Row
iCols = ActiveCell.Column
rngEnd = sh.Cells(iRws, iCols).Address
Set rngSource = sh.Range("A1:" & rngEnd)
'find the last row in the destination sheet
wbDestination.Activate
Set wsDestination = ActiveSheet
wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
totRws = ActiveCell.Row
'check if there are enough rows to paste the data
If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
GoTo eh
End If
'add a row to paste on the next row down if you are not in row 1
If totRws <> 1 Then totRws = totRws + 1
rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
Next sh
End If
Next wb
'now close all the open files except the one you want
For Each wb In Application.Workbooks
If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
wb.Close False
End If
Next wb
'clean up the objects to release the memory
Set wbDestination = Nothing
Set wbSource = Nothing
Set wsDestination = Nothing
Set rngSource = Nothing
Set wb = Nothing
'turn on the screen updating when complete
Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

• Then run the code. It will combine all the files into a single sheet.

Read More: Consolidate Data from Multiple Workbooks in a Single Worksheet

3. Combine All Excel Files into a Single Worksheet in an Active Workbook

You can combine all Excel files into a single worksheet in an active workbook with a simple VBA code. Insert the code and run the module.

• First, insert the VBA code in the module.

Sub MergeMultipleFiles()
On Error GoTo eh
'declare variables to hold the objects required
Dim wbDestination As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wb As Workbook
Dim sh As Worksheet
Dim strSheetName As String
Dim strDestName As String
'turn off the screen updating to speed things up
Application.ScreenUpdating = False
'first create new destination workbook
Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook
For Each wb In Application.Workbooks
If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
Set wbSource =
For Each sh In wbSource.Worksheets
sh.Copy After:=Workbooks(strDestName).Sheets(1)
Next sh
End If
Next wb
'now close all the open files except the new file and the Personal macro workbook.
For Each wb In Application.Workbooks
If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
wb.Close False
End If
Next wb
'remove sheet one from the destination workbook
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
'clean up the objects to release the memory
Set wbDestination = Nothing
Set wbSource = Nothing
Set wsSource = Nothing
Set wb = Nothing
'turn on the screen updating when complete
Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

• Now, run the code and you will get the following output.

Which Things You Have to Keep in Mind?

• Always keep a backup of your files before merging.
• Merging files can affect the formatting and formulas of the files.
• Always check the merged data carefully so that you can identify if any data is missing.

Frequently Asked Questions

1. Is there a limit to the number of Excel files I can merge together?

Usually, Excel can merge many files. But depending on the file size, you may need to optimize and use other tools.

2. What happens to the formatting and formulas when merging Excel files?

If you copy and paste data to merge, then the formatting will be intact. In other cases, formatting and formulas can behave differently.

3. How do I handle duplicate data when merging Excel files?

Excel has useful features like Remove Duplicates or formulas like VLOOKUP or INDEX-MATCH to resolve issues related to duplicate data effectively.

Conclusion

We have shown you several methods to merge files in Excel. We can easily merge data from various sources, departments, or time periods using these methods. We can use move/copy option, pasting links, combining Excel function, Power Query or VBA to merge files in Excel. We hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Merge Excel File: Knowledge Hub

<< Go Back To Merge in Excel | Learn Excel