All Options to Merge Excel Files Explained
Which option would you choose: have three Excel files with one worksheet each or one Excel file with three worksheets? The former is not practical and convenient from the user’s perspective. Whereas, the latter allows you to easily navigate and manipulate your records in one place. So, let’s organize your data in an efficient way by merging Excel files into one. This tutorial will help you combine multiple Excel files into one according to your needs.
How to merge Excel files – all options introduced
Most of us love Excel for its functional diversity. This means that you can do a certain action or task in multiple ways depending on your needs. For example, you can merge Excel files in the following ways:
- Manually copy data from multiple workbooks, then paste it into one worksheet.
- Manually copy worksheets from multiple files to a single workbook.
- Link or export data from multiple worksheets in a single workbook.
- Merge multiple Excel files using Power Query.
- Merge Excel files into a single workbook using VBA macros.
If you already know which case is yours, jump right into the required section. However, we advise you read the entire guide to learn the details of merging Excel files.
How to merge two Excel files the easy way
Here we have two Excel files with one worksheet each.
The simplest way to merge two Excel files is to copy a data range from one of them and paste it into another one:
- On one of the files, select a data range, then right-click and select Copy.
- Go to the other file, select a cell where you want to paste the data, then right-click and select Paste.
In our case, we forgot to exclude the first row with column names when we copied the data range and got the following result:
So if you use this method to merge Excel files, make sure to select the proper data range.
Obviously, this method is plain enough. However, it won’t be suitable if your Excel files contain hundreds or thousands of rows you want to merge. Let’s proceed to other options then.
How to combine multiple Excel files into one by copying their sheets
We have three Excel files with one worksheet each. We want to combine multiple excel files into one workbook but keep these separate sheets in one Excel file. So, we just need to copy these sheets.
- Open all the files.
- Right-click on the sheet you want to copy and select Move or Copy…
- Select the file where you want to copy your sheet to, and check the Create a copy checkbox.
- Repeat this process for the rest of the sheets, and there you go:
Now you have all the sheets from separate Excel files merged in one Excel workbook.
Merge Excel files online
If you work in Excel Online and you need to merge your files located in cloud storage such as OneDrive, you can:
- Manually copy and paste data.
- Link Excel sheets using Workbook links.
- Import Excel sheets from your files to a single Excel workbook and even automate the import on a schedule.
The latter option seems to be the most actionable one, so let’s dive in.
Merge multiple Excel files into one sheet
We have three Excel files on OneDrive with one worksheet each.
Let’s merge the data from these files into one sheet in the workbook Excel-File-1. The Excel integration by Coupler.io will do the job with just a few clicks.
Coupler.io is a data integration tool that allows you to schedule imports from multiple sources, such as Excel, CSV, Airtable, and many others to Microsoft Excel, Google Sheets, or Google BigQuery.
Sign up to Coupler.io with your Microsoft account, click Add importer, then select Microsoft Excel both as a source and destination app. Check out other Excel integrations.
Then proceed with the setup.
Source
- Connect your Microsoft account.
- Select an Excel file on OneDrive and a sheet to take data from. You can select multiple sheets from a single file, but the data extracted from them will be merged into one sheet.
Destination
- Connect your Microsoft account.
- Select an Excel file on OneDrive and a sheet where to load the data.
- Select the Append import mode to have your imported data merged not replaced.
You can now click Save and Run to load the data. Here is the result:
However, this merged data is from only two files, while we have three. You need to set up another integration for the third file. You can make a copy of this integration to speed up the setup. Click the three dots on the top-right and select Copy.
Then replace the source file and keep the destination settings unchanged.
Note: Double-check the Sheet in the Destination settings since it may be suggested that you make a copy of it.
Now you can click Save and Run to add the data from the third Excel file to the already merged rows from two Excel files.
How to automatically merge Excel files
Coupler.io allows you to schedule imports of data from the Excel files you want to merge. So, you can combine multiple Excel files into one automatically.
For this, you need to enable the Automatic data refresh feature and configure the desired schedule. You can do this either when setting up your importer:
Or do this at any time later in the admin panel:
Merge Excel files with Power Query
Excel Power Query is a built-in data transformation engine that allows you to perform basic and advanced transformations with your data. Let’s see how it can cope with merging Excel files.
How to merge 3 Excel files into one
Again, we have three Excel files with one worksheet each. Locate all the files in one folder.
- Open a new Excel workbook. Go to New Query=> From File => From Folder
- Browse and select the folder with your Excel files.
- On the next step, you’ll have three options:
- Combine and Transform Data – choose this if you want to manipulate the data before merging it.
- Combine and Load – choose this if you want to merge Excel files right away.
- Combine and Load To… – choose this if you want to merge Excel files into another workbook.
- On the next step, you can optionally select the object to extract from each file. Click OK.
- Eventually, your files are merged into one.
Merge 2 Excel files based on a column
Let’s solve a more complex task of merging Excel files using Power Query. We have two Excel files with one worksheet each, and we need to merge them based on a key column. In our example, let’s merge the rows that have matches based on the first_name value, if any. Here is the flow:
- Open a new Excel workbook. Go to New Query => From File => From Workbook.
- Select a file and a sheet, then click Load. The data will be loaded in the form of a table.
- Repeat these steps to load data from the second file. As a result, you’ll have two queries.
- Go to the Query tab, then select Merge.
- Select tables and matching columns to merge data. Once you do this, you’ll see the number of rows that match based on the selected key column. Click OK to proceed.
- Then you’ll see a preview of the merged queries in the Power Query Editor.
If you’re not going to make any additional manipulations, click Close & Load. Here is the result.
How to merge two Excel files and remove duplicates
To remove duplicates during the process of merging Excel files, you’ll need to repeat the flow we described in the previous section. And on the step of a preview of the merged queries in the Power Query Editor, go to Home => Remove Rows => Remove Duplicates.
And that’s it 🙂
How to combine multiple Excel files into one workbook and compare them
The flow that we described in the section about merging two Excel files based on a column will let you merge and compare files. You’ll need to choose a key column to compare the data from both files, and then you’ll see whether there are any matches.
Combine data from multiple Excel files into one pivot table
Let’s add another condition to our task – combine multiple Excel workbooks into one pivot table. The Power Query will let you do this easily. For this, you’ll need first to consolidate your files as we described above. Once you have the data consolidated in one table/sheet, click the Summarize with PivotTable button in the Design menu.
After that, you can configure the pivot table to your needs.
How to combine multiple Excel files into one worksheet using macro
Excel macros are pieces of code that can implement specific actions including merging Excel files. In our guide on Excel macros, you can learn more about using and creating macros.
And here, we’d like to introduce a few ready-to-use VBA macros to optimize the consolidation of data from multiple Excel files.
- Go to the Developer tab => Visual Basic
- Go to Insert => Module
- In the open window, enter one of the following scripts and run it.
Excel VBA macro to combine multiple Excel files into one workbook
This macro will combine all open Excel files into one new workbook.
Sub MergeFilesNewWorkbook()
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
'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
Excel VBA macro to merge all open Excel files into an existing workbook
This macro will combine all open Excel files into one existing workbook.
Sub MergeFilesExistingWorkbook()
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
'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
Excel VBA macro to combine multiple Excel files into one workbook as separate sheets
This macro will combine all open Excel files into one new workbook and keep them as separate sheets.
Sub MergeFilesSeparateSheets()
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 = wb
For Each sh In wbSource.Worksheets
sh.Copy After:=Workbooks(strDestName).Sheets(1)
Next sh
End If
Next wb
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
Wrap-up: Merge CSV files in Excel
As a wrap-up, we decided to touch on how to merge CSV files in Excel. Actually, there is nothing complex since most of the methods above allow you to do this. Once you open a CSV file in Excel, it will look like a regular workbook, so you can use Power Query or VBA macros to merge them.
If your CSV files are stored online, you can easily merge them using Coupler.io in the same way we did with Excel files. The only difference is that you won’t need to select a sheet. Choose the best option for your needs and optimize your workflow as much as possible. Good luck with your data!
