5 Ways You Can Merge Excel Files [10 Min Easy Guide]

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.

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:

  1. Copy Pasting
  2. Using Move and Copy
  3. Using the Power Query
  4. Using the MergExcelFiles Macro
  5. 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:

  1. Open the main Excel file where you want to merge the others into.
Open a new workbook
  1. Open the second Excel file you want to merge, and navigate to the sheet or range of cells you want to copy.
Example workbook 1
Example workbook 2
  1. Select the range of cellsย you want to copy, right-click, and choose Copyย or pressย Ctrl+C.
Copy the cells
  1. 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.
Paste option
  1. Right-click and choose Paste or press Ctrl+V (Cmd+V on Mac)ย to paste the copied data into the first Excel file.
Merged 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.

  1. Open the Excel workbook with the data and a new blank workbook.
  2. 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.
Select all the sheets
  1. Right-click and select Move or Copy. A dialog box will pop up.
Move and copy option
  1. Click on the dropdown for To book and choose the destination workbook.
Image37
  1. Specify where you want the sheets to appear in the before sheets section and check the box for Create a Copy.
Image10
  1. Click Ok.
  2. 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:

  1. Put all your Excel Files into one folder.
Put all your Excel Files into one folder.
  1. Open a new Excel file or an existing one where you want to merge the data.
  2. Go to the Data tab >ย Get Dataย or New Queryย depending on your Excel version.
Go to the Data tab > Get Data or New Query
  1. Go to From File >ย From Folder.
Go to From File > From Folder.
  1. Click Browse to find the folder location, then click OK.
Click Browse to find the folder location then click OK.

A new window with a list of your Excel files will open.

  1. Click on the drop-down for Combine.
  2. 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:

  1. Go to the Data >ย Get Dataย or New Queryย depending on your Excel version.
Go to the Data tab > Get Data or New Query
  1. Go to From Fileย > From Workbook.
Image16
  1. Choose the file from the folder and click Import.
Image5
  1. Select the Sheet or sheets.
Image24
  1. Click Load > Load to
Image30
  1. Choose Only Create Connection.
Image7
  1. Repeat the process for the other workbooks.
Image6
  1. Go toย Get Data again and choose Combine Queries.
Image20
  1. Choose Append.
  2. Go to File > Close & Load

Your sheets will be merged into one sheet, but the headers will be side by side.

Image32

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:

  1. Go to File >ย Options, and select Customize Ribbon.
Image1
  1. In the Customize Ribbon section, check the box next to Developer and click OK.
Image31
  1. 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.
  1. Click on the Developerย tab on the ribbon, and then click on the Visual Basic button. This will open the Visual Basic Editor.
Image34
  1. In the Visual Basic Editor, go to the Insertย menu and click on Module. This will insert a new module into the workbook.
Image14
  1. 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
  1. Go to ย File >ย Save Asย to save the workbook and change the format to Excel Macro-Enabled Workbook (*.xlsm).
  1. Use the keyboard shortcut Alt + F8ย to open the Macro dialog box.
Image2
  1. Select the MergeFilesย macro from the list and click Run.
  2. 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.
Image38

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

  1. Go to Docsoso
  2. Click on Select files.
Image27
  1. 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.
  2. Click Merge Excelย and wait for the tool to merge the file
  3. Download your merged Excel file.
Image19

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:

  1. Select your data and click CTRL + Tย to convert them to tables. Do this for all the sheets.
  2. Go to Design tab and name your Tables.
Image40
  1. Go to the Data tab
  2. Click Get Data >ย From Other Sources.
Image36
  1. Choose Blank Query.
Image41

The Power Query editor will open up.

  1. Click onย Advanced Editor.
Image35
  1. Type inย Excel.CurrentWorkbookย in the Source and click Done. Click Invoke if prompted to.
Image4

Your tables will be listed in the query window.

Image17
  1. Click on the double-headed arrow to expand the tables.
Image21
  1. Uncheck โ€œUse original column name as prefixโ€ then click Ok.
Image33
  1. Go to Close & Load.
Image13

The tables from the different sheets will be merged and opened in a new sheet.

Related:ย How to Convert Google Sheets to Excel

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:

  1. Go to File >ย Options
  2. The Excel Options dialog box will appear. Select Quick Access toolbar.
Image1
  1. Go to the drop-down for Choose commands from and choose the option All commands.
Image28
  1. In the Commands, scroll to find Compare and Merge Workbooksย command and click Add.
Image23
  1. Click Ok.

The icon for compare and Merge will appear in the Quick Access toolbar.

compare and Merge icon
  1. Click the Compare and Merge Workbooks icon.
  2. You may be prompted to save the workbook.
  3. A window will pop up to open files. Select the copies of the workbook to merge into the open copy or original shared workbook.
  4. 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

Video Guide: 5 Ways to Merge Excel Files

Here’s a six-minute video I made for YouTube. It explains each method in more detail. Check it out on my YouTube channel.

Video Summary

The video from explains five methods for merging Excel files:

  1. Copy and Paste: A simple method where data is copied from one file and pasted into another, often in a new tab.
  2. Move or Copy Entire Tabs: Right-click the tab, select “Move or Copy,” and move it to the desired workbook.
  3. Power Query: Suitable for large datasets, it allows automatic data refresh from the source file when updated.
  4. VBA Macro: Involves coding to automate merging, recommended for advanced users with coding knowledge.
  5. Data Consolidate: A function to combine data from multiple sheets into one.

The video emphasizes the usefulness of each method, with Power Query being particularly powerful for dynamic data, and advises against using VBA or Data Consolidate for basic users.

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.

Wrapping Up

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.

Related:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!