Learn to combine multiple Excel workbooks efficiently using built-in tools and functions. This guide covers four proven techniques for merging Excel files in 2024.

Merging Excel Files Using the Consolidate Tool

The Consolidate tool in Excel offers a straightforward way to merge data from multiple workbooks. Here’s how to use it:

Step 1. Open the destination workbook.

• Launch Excel and open the workbook where you want to merge your data.
• Create a new sheet or select an existing one to receive the consolidated data.

Step 2. Access the Consolidate feature.

• Click on the “Data” tab in the Excel ribbon.

• Look for the “Data Tools” group and select “Consolidate.”

Step 3. Choose the function to apply.

• In the Consolidate dialog box, click the “Function” dropdown menu.

• Select the appropriate function for your data (e.g., Sum, Average, Count, Max, Min).

Step 4. Select source ranges from different workbooks.

• Click the “Reference” field in the Consolidate dialog box.

• Navigate to the first workbook and sheet containing source data.
• Select the range of cells you want to merge.
• Click “Add” to include this range in the consolidation.

• Repeat this process for all source ranges in different workbooks.

Step 5. Specify the destination for merged data.

• In the Consolidate dialog box, ensure the “Use labels in” options are checked if your data has row or column labels.
• Click “OK” to apply the consolidation and merge your data into the current worksheet.

How do I merge two Excel files into one?

For a simple merge of entire workbooks, follow these steps:

Step 1. Open both Excel files.

• Launch Excel and open both the source and destination workbooks.

Step 2. Prepare to move or copy sheets.

• In the destination file, right-click on a sheet tab at the bottom of the window.
• Select “Move or Copy” from the context menu.

Step 3. Choose the source file and complete the merge.

• In the “Move or Copy” dialog box, select the source file from the “To book” dropdown menu.

• Choose where you want to insert the copied sheet in the destination workbook.
• Check the “Create a copy” box if you want to keep the original sheet in the source file.

• Click “OK” to complete the merge.

Step 4. Repeat for additional sheets.

• If you need to merge more sheets, repeat steps 2-3 for each sheet you want to combine.

Combining Data from Multiple Sheets with Power Query

Power Query is a powerful tool for merging data from multiple Excel files. Here’s how to use it:

Step 1. Open a new workbook and access Power Query.

• Launch Excel and open a new workbook.
• Go to the “Data” tab and click “Get Data” > “From File” > “From Workbook.”

Step 2. Select the first Excel file to merge.

• Browse to the location of your first Excel file and select it.
• Click “Import/Get Data” to open the Navigator window.

Step 3. Choose the sheets to combine.

• In the Navigator, select the checkboxes next to the sheets you want to merge.
• Preview the data in the right pane to ensure you’ve selected the correct sheets.

Step 4. Transform the data in Power Query Editor.

• Click “Transform Data” to open the Power Query Editor.

• Make any necessary adjustments to your data, such as removing columns or changing data types.

Step 5. Repeat the process for additional files.

• In the Power Query Editor, go to “Home” > “New Source/Get Data” > “Excel.”

• Select the next Excel file you want to merge and repeat steps 2-4.

Step 6. Merge data from all files using Append Queries.

• In the Power Query Editor, select one of your queries.
• Go to “Home” > “Combine” > “Append Queries.”

• Choose “Append Queries as New” to create a new merged query.

• Select all the queries you want to combine and click “OK.”

Step 7. Load the merged data into Excel.

• In the Power Query Editor, click “Close & Load” to import the merged data into your Excel workbook.

What is the easiest way to merge data in Excel?

The easiest method depends on your specific needs:

1. For simple text merging:
   • Use the CONCAT function: =CONCAT(A1, B1)

• • This combines text from multiple cells into one.

1. For large datasets or multiple files:
   • Employ Power Query as described above.
   • It handles complex merges and data transformations efficiently.
2. For summarizing data across sheets:
   • Utilize the Consolidate tool as outlined earlier.
   • This is ideal for creating summary reports from multiple sources.

Merging Excel Files with VBA

For advanced users, VBA (Visual Basic for Applications) offers a powerful way to automate the merging process:

Step 1. Open the Visual Basic Editor.

• Press Alt + F11 or go to “Developer” > “Visual Basic” in the Excel ribbon.

Step 2. Insert a new module.

• In the VBA Editor, go to “Insert” > “Module” to create a new module.

Step 3. Paste and customize the VBA code.

• Copy and paste the following VBA code into the module:

vba

Copy

Sub MergeExcelFiles()

Dim FolderPath As String

Dim Filename As String

Dim Sheet As Worksheet

 

‘ Specify the folder path containing Excel files to merge

FolderPath = “C:\Path\To\Your\Folder\”

 

‘ Specify the file extension

Filename = Dir(FolderPath & “*.xlsx”)

 

‘ Loop through all Excel files in the folder

Do While Filename <> “”

Workbooks.Open Filename:=FolderPath & Filename

 

‘ Copy each worksheet to the master workbook

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Next Sheet

 

Workbooks(Filename).Close

Filename = Dir()

Loop

End Sub

• Customize the FolderPath variable to specify the location of your Excel files.

Step 4. Run the macro to merge files.

• Return to your Excel workbook.
• Go to “Developer” > “Macros” (or press Alt + F8).

• Select the “MergeExcelFiles” macro and click “Run.”

How do I merge two Excel spreadsheets based on a common field?

To merge Excel spreadsheets based on a common field, you have several options:

1. Use the VLOOKUP function:
   • In your destination sheet, use =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
   • This matches data based on a key column from one sheet to another.
2. Employ Power Query’s Merge feature:
   • In Power Query, select “Home” > “Combine” > “Merge Queries.”
   • Choose the common field in both queries to create a more complex join.
3. Create a PivotTable to summarize data:
   • Select your data and go to “Insert” > “PivotTable.”

• • Drag the common field to the “Rows” area and other fields to “Values” to summarize data from multiple sheets.

Combining Excel Sheets Without Duplicates

When merging Excel sheets, you may end up with duplicate entries. Here’s how to remove them:

Step 1. Merge sheets using any of the above methods.

Step 2. Select the entire dataset in the combined sheet.

• Click the top-left cell of your data range.
• Press Ctrl + Shift + End to select all cells with data.

Step 3. Remove duplicates.

• Go to the “Data” tab in the Excel ribbon.
• Click “Remove Duplicates” in the “Data Tools” group.

Step 4. Choose columns to check for duplicates.

• In the “Remove Duplicates” dialog box, select the columns you want to check for duplicate values.

• Ensure the “My data has headers” box is checked if your data includes column headers.

Step 5. Remove duplicate entries.

• Click “OK” to remove duplicate entries from your dataset.
• Excel will display a message indicating how many duplicate values were removed and how many unique values remain.

Streamline Your Excel Workflow

By mastering these techniques for merging Excel files, you’ll save time and improve data management. Experiment with different methods to find the best fit for your specific needs.

Ready to take your Excel data management to the next level? Try Coefficient to seamlessly sync live data from 50+ business systems into your spreadsheets. Get started with Coefficient today and experience the power of real-time data in Excel.