Merging files in Excel is a pretty common need for many users and a challenging one at that. Luckily, Excel provides several features that allow you to merge Excel files, simplifying data analysis and organization.
In this guide, we’ll show you how to merge multiple Excel files effectively, saving you time and effort while ensuring data accuracy and completeness. If you follow our step-by-step tutorial, you’ll be able to merge Excel files into one workbook or multiple sheets too. Read on to learn more.
Table of Contents
How to Merge Multiple Excel Files into One
There are different ways you can merge Excel files into one workbook. You can either combine them all into one sheet in the workbook, or combine them into one workbook but separate sheets.
We’ll be showing you the following methods to combine your Excel files into one workbook:
- Copy Pasting
- Using Move and Copy
- Using the Power Query
- Using the MergExcelFiles Macro
- Using Third-party tools
We’ll show you each method using examples and a step-by-step tutorial in this article and how to merge multiple sheets in an Excel workbook.
Method 1: Copy and Paste
One way to merge Excel files is by manually copy pasting the data from the different files into the main workbook. You can follow the steps below to merge Excel files into one using copy and paste:
- Open the main Excel file where you want to merge the others into.
- Open the second Excel file you want to merge, and navigate to the sheet or range of cells you want to copy.
- Select the range of cells you want to copy, right-click, and choose Copy or press Ctrl+C.
- Return to the first Excel file, navigate to the sheet or location where you want to merge the data, and select the first empty cell (top left) where you want the copied data to start.
- Right-click and choose Paste or press Ctrl+V (Cmd+V on Mac) to paste the copied data into the first Excel file.
You can repeat these steps for as many additional Excel files as you want to merge, pasting the data into subsequent rows or sheets in the main Excel file.
This method lets you control where you want your data to go, but it can be stressful if you have a large amount of data or too many files.
Method 2: Using Move and Copy
You can also merge sheets from different workbooks into one file using the move and copy feature.
- Open the Excel workbook with the data and a new blank workbook.
- Select all the sheets you want to copy. You can do this using the Ctrl for Windows or the Cmd key for Mac. Simply hold the key and select all the sheets.
- Right-click and select Move or Copy. A dialog box will pop up.
- Click on the dropdown for To book and choose the destination workbook.
- Specify where you want the sheets to appear in the before sheets section and check the box for Create a Copy.
- Click Ok.
- You can go to the other workbooks and do the same to the sheets making sure to select the same destination workbook.
This method will let you move or copy your sheets into one workbook, but the sheets will remain separate. It’s a much better method compared to copy-pasting but it’s still lacking in terms of convenience, especially if you have too many workbooks.
Method 3: Using Power Query
This method involves using the Power Query feature to combine your sheets and works for the 2016 version and later. By using Power Query in Excel, you can easily combine data from multiple Excel files into a single consolidated table.
This process saves time, eliminates manual copy-pasting, and ensures accuracy when dealing with large datasets spread across multiple files.
Before you start this method, make sure that your Excel Files are in the same folder.
Here’s how to combine Excel files using the power query:
- Put all your Excel Files into one folder.
- Open a new Excel file or an existing one where you want to merge the data.
- Go to the Data tab > Get Data or New Query depending on your Excel version.
- Go to From File > From Folder.
- Click Browse to find the folder location, then click OK.
A new window with a list of your Excel files will open.
- Click on the drop-down for Combine.
- Choose the option Combine & Load if your files are ready to merge. On the other hand, if you still want to make edits before combining the files, choose the Transform Data option. The Query Editor will open up, where you can make edits to your rows and columns or add filters and more.
One thing to note is that the files need to have either the same sheet name or the same table name.
If you have your files in different folders or they have different sheet names, then you can use From Workbook instead. Here’s how:
- Go to the Data > Get Data or New Query depending on your Excel version.
- Go to From File > From Workbook.
- Choose the file from the folder and click Import.
- Select the Sheet or sheets.
- Click Load > Load to
- Choose Only Create Connection.
- Repeat the process for the other workbooks.
- Go to Get Data again and choose Combine Queries.
- Choose Append.
- Go to File > Close & Load
Your sheets will be merged into one sheet, but the headers will be side by side.
Using the Power Query is probably the best method for merging Excel files, although a bit tricky to do for beginners. It is faster and safer than the other methods above.
Method 4: Using the MergeExcelFiles Macro
VBA is the equivalent of App Scripts in Google Sheets and is a useful tool for automation in Excel. Using VBA, you can create a MergeExcelFiles macro. Here’s how to combine multiple Excel files into one using the MergeFiles Macro:
- Go to File > Options, and select Customize Ribbon.
- In the Customize Ribbon section, check the box next to Developer and click OK.
- Open Excel and the Workbook with the macro you want to use. If you don’t have a workbook with the macro, you’ll need to create one, see step 6.
- Click on the Developer tab on the ribbon, and then click on the Visual Basic button. This will open the Visual Basic Editor.
- In the Visual Basic Editor, go to the Insert menu and click on Module. This will insert a new module into the workbook.
- Copy and Paste the Macro Code
Sub mergeFiles() 'Merges all files in a folder to a main file. 'Define variables: Dim numberOfFilesChosen, i As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbook Dim tempWorkSheet As Worksheet Set mainWorkbook = Application.ActiveWorkbook Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker) 'Allow the user to select multiple workbooks tempFileDialog.AllowMultiSelect = True numberOfFilesChosen = tempFileDialog.Show 'Loop through all selected workbooks For i = 1 To tempFileDialog.SelectedItems.Count 'Open each workbook Workbooks.Open tempFileDialog.SelectedItems(i) Set sourceWorkbook = ActiveWorkbook 'Copy each worksheet to the end of the main workbook For Each tempWorkSheet In sourceWorkbook.Worksheets tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count) Next tempWorkSheet 'Close the source workbook sourceWorkbook.Close Next i End Sub
- Go to File > Save As to save the workbook and change the format to Excel Macro-Enabled Workbook (*.xlsm).
- Use the keyboard shortcut Alt + F8 to open the Macro dialog box.
- Select the MergeFiles macro from the list and click Run.
- In the file window that pops up, go to the folder with your Excel files and select all the files you want to merge, then click OK.
This will open all your files in the active workbook.
You don’t really need to know any scripting language to follow this method since all you need to do is copy and paste the macro code.
Method 5: Using a Third-Party Tool
If the methods so far seem too strenuous for you, then you can also use a third-party tool to combine Excel files. You can download an Excel file merger add-in like RBD merge, or for a much easier method, use an online merger.
In this example, we’ll be using Docsoso
- Go to Docsoso
- Click on Select files.
- Go to the folder with your Excel files and select all the files you want to merge. The files must be in the same folder.
- Click Merge Excel and wait for the tool to merge the file
- Download your merged Excel file.
This method is pretty simple, and you get your file merged seamlessly and exactly as they are with the format intact.
Related: Merge Cells in Google Sheets
How to Merge Multiple Excel Sheets into One
If you have multiple sheets in one that you want to merge in Excel, there are many ways you can do this, like copy-pasting the data. However, the best way to do this is by using the Power Query. Here’s how to merge multiple worksheets into one in Excel:
- Select your data and click CTRL + T to convert them to tables. Do this for all the sheets.
- Go to Design tab and name your Tables.
- Go to the Data tab
- Click Get Data > From Other Sources.
- Choose Blank Query.
The Power Query editor will open up.
- Click on Advanced Editor.
- Type in Excel.CurrentWorkbook in the Source and click Done. Click Invoke if prompted to.
Your tables will be listed in the query window.
- Click on the double-headed arrow to expand the tables.
- Uncheck “Use original column name as prefix” then click Ok.
- Go to Close & Load.
The tables from the different sheets will be merged and opened in a new sheet.
How to Combine Versions of a Shared Excel Workbook
If you have a shared workbook and you want to merge the changes, here’s how you can do it:
- Go to File > Options
- The Excel Options dialog box will appear. Select Quick Access toolbar.
- Go to the drop-down for Choose commands from and choose the option All commands.
- In the Commands, scroll to find Compare and Merge Workbooks command and click Add.
- Click Ok.
The icon for compare and Merge will appear in the Quick Access toolbar.
- Click the Compare and Merge Workbooks icon.
- You may be prompted to save the workbook.
- A window will pop up to open files. Select the copies of the workbook to merge into the open copy or original shared workbook.
- Click Ok.
Your changes will be merged into one workbook, and the cells with changes will be highlighted in different colors.
Related: Convert Excel to Google Sheets
Frequently Asked Questions
How Do I Get Data from Multiple Workbooks in Excel?
You can get data from multiple workbooks in Excel by merging them into one workbook. There are several ways you can do this, but the best way is to Use the Power Query. In this article, we’ve shown you how to use the power query to combine multiple Excel files.
What is the Difference Between Append and Merge?
Appending in Excel lets you combine data vertically by stacking one data set below another. When you append data, you add rows from one table or range to another. Your data will therefore expand downwards.
On the other hand, Merging data in Excel refers to combining data horizontally, merging columns from different tables based on common values or keys. The columns from multiple tables are brought together into a single table based on a shared identifier or relationship.
Merging Excel files can be convenient when consolidating data, but many users find it challenging. In this article, we’ve shown you 5 ways you can use to merge Excel Files. You can use the method that works best for you.
If you found this article helpful, you can also have a look at our premium templates, remember to use the code SSP at checkout to save 50%.
- How to Import Excel to Google Sheets
- How to Use HLOOKUP in Excel
- How to Remove Rows in Microsoft Excel
- How to Remove Duplicates in Excel
- How to Pull Data From Another Sheet in Excel
- The Ultimate Guide to Excel Version History
- How to Track Changes in Excel
- Master the Excel Consolidate Features
- A Comprehensive List of the Best Excel Courses