5 Easy Ways to Merge Multiple Excel Files

Combining data from various Excel files can sometimes be a challenge for users, particularly those who handle extensive data sets. However, merging multiple Excel files into one workbook can significantly streamline your data management process. Here, we'll explore five straightforward methods to merge Excel files that cater to various needs and skill levels.

Using Power Query

Power Query in Excel is a potent tool for data manipulation, including merging files:

• Open Excel: Start by launching Excel and creating a new blank workbook.
• Navigate to Data Tab: Click on the ‘Data’ tab and select ‘Get Data’ > ‘From File’ > ‘From Folder’.
• Select Folder: Browse to the folder containing your Excel files and hit ‘OK’.
• Preview Data: Excel will show a list of files in the folder; you can preview the contents by clicking on a file name.
• Combine & Load: Select ‘Combine & Load’ or ‘Combine & Transform Data’ to start the merge process.
• Configure Merge: Choose the sheets or tables you wish to combine and adjust the settings accordingly.

🎓 Note: Power Query is available in Excel 2016 and later versions or with a Microsoft 365 subscription.

Using VBA Macro

For users comfortable with VBA, here’s how to merge Excel files using a macro:

• Open Excel: Open a new workbook or an existing one where you want to insert the merged data.
• Insert a Module: Go to the ‘Developer’ tab (if not visible, enable it), click ‘Visual Basic’, and then ‘Insert Module’.
• Paste Code: Copy and paste the following VBA code into the module:

Sub MergeExcelFiles()
    Dim FolderPath As String, FilePath As String, FileName As String
    Dim Wrkbook As Workbook, WrkbookConsolidated As Workbook
    Dim wksht As Worksheet
    Dim cell As Range
    Dim LastRow As Long, LastCol As Long

Application.ScreenUpdating = False
Set WrkbookConsolidated = ThisWorkbook
FolderPath = "C:\Your\Path\Here\" 'Set folder path where your Excel files are stored
FilePath = FolderPath & "*.xlsx"

FileName = Dir(FilePath)

Do While FileName <> ""
    Set Wrkbook = Workbooks.Open(FolderPath & FileName)
    For Each wksht In Wrkbook.Worksheets
        wksht.Copy After:=WrkbookConsolidated.Worksheets(WrkbookConsolidated.Worksheets.Count)
    Next wksht
    Wrkbook.Close False
    FileName = Dir()
Loop

Application.ScreenUpdating = True
MsgBox "Files Merged Successfully!", vbInformation


End Sub

• Run the Macro: Run the macro by going back to Excel, pressing ‘Alt + F8’, selecting ‘MergeExcelFiles’, and clicking ‘Run’.

Here is the method that gives you more control over the merge process.

Using Excel’s Consolidate Feature

Consolidate is an easy-to-use tool for summing data from various ranges:

• Prepare Data: Ensure all your files have similar data structure.
• Open a Workbook: Open an Excel workbook where you want to consolidate data.
• Go to Data Tab: Click ‘Data’ > ‘Consolidate’.
• Select Data: In the ‘Function’ dropdown, choose ‘Sum’ or ‘Count’ as needed. Use the ‘Browse’ button to locate and add ranges from different workbooks.
• Finalize: Click ‘OK’ to consolidate the data into the active worksheet.

Using Third-Party Add-ins

There are several third-party add-ins that provide seamless integration for merging Excel files:

• Power BI: While primarily for analytics, Power BI can merge Excel files with its Power Query Editor.
• Excel Merge Add-ins: Look for add-ins like ASAP Utilities or Kutools for Excel, which offer merge utilities.

🛑 Note: Be cautious when using third-party tools; always check their compatibility with your Excel version and data security.

Manual Copy Paste

The simplest method, though time-consuming, especially with large data sets:

• Open Excel: Open the workbooks you want to merge.
• Select and Copy: Select data from one sheet and copy it to a new sheet or workbook.
• Paste: Paste the copied data into your destination sheet.
• Repeat: Continue this process for all files you need to merge.

In summary, merging Excel files can be approached in numerous ways, each suited to different scenarios and proficiency levels. Power Query and VBA macros offer automation, the Consolidate feature provides basic merging capabilities, third-party add-ins can enhance functionality, and manual methods are always available as a fallback. These tools empower users to efficiently manage and consolidate their data, providing the flexibility to choose the best method for their needs.

Can I merge Excel files with different sheet structures?

+

Yes, you can use Power Query or VBA macros to handle files with varying structures, although it might require more setup.

Is it safe to use third-party add-ins?

+

While many add-ins are reliable, always check their source, reviews, and security practices before integrating them into your workflow.

What’s the quickest method for merging a large number of files?

+

Power Query or VBA macros are the quickest methods for handling large sets of Excel files.

Do all Excel versions support Power Query?

+

Power Query is available in Excel 2016 and later versions or with a Microsoft 365 subscription.

What if I encounter errors during merging?

+

Common errors can include file path issues, incompatible data types, or corruption in the source files. Ensure your files are in good shape and paths are correct.