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!
Back to Blog