5 Ways to Merge Two Excel Sheets Effortlessly

Merging two Excel sheets can be a daunting task, especially for those who are not familiar with Excel's advanced features or lack experience in data manipulation. However, Excel provides several tools and techniques that can simplify this process, making it both efficient and error-free. In this post, we'll explore five effective methods to merge Excel sheets, catering to both beginners and advanced users.

Method 1: Using the Consolidate Function

The Consolidate feature in Excel allows you to combine data from multiple sheets into one summary sheet.

Steps to Consolidate Sheets

• Select an empty cell where you want to place the consolidated data.
• Go to Data > Consolidate.
• In the Function box, choose the operation you want (e.g., Sum, Count).
• Click Add to select the range from the first sheet.
• Repeat for each sheet you want to include.
• Check Create links to source data if you want the consolidated data to update automatically when source sheets change.

⚠️ Note: The consolidate function works best when the sheets have identical structures or similar data fields.

Method 2: Power Query

Power Query is an Excel feature for data transformation and preparation.

Steps to Merge Sheets with Power Query

• Navigate to Data > Get Data > From Other Sources > Blank Query.
• In the Power Query Editor, click Advanced Editor and paste the following script to load sheets:

  let
      Source = Folder.Files(“C:\YourPath\To\ExcelFile”),
      Combined = Source{[Content]}[Content],
      #“Imported Excel” = Excel.Workbook(Combined, null, true),
      Sheets = #“Imported Excel”{[Kind=“Sheet”]},
      #“Remove Other Columns” = Table.SelectColumns(Sheets,{“Name”, “Data”}),
      Result = Table.Combine(#“Remove Other Columns”[Data])
  in
      Result
      

• Adjust the path in the script to point to your Excel file’s location.
• Close & Load the query into a new sheet.

🔍 Note: Power Query requires some familiarity with M code. Use the Advanced Editor to modify the query for specific needs.

Method 3: VBA Macro for Merging

VBA macros can automate the process of merging sheets with a few lines of code.

Sample VBA Code for Merging Sheets

Here’s a basic VBA macro to merge all sheets:

Sub MergeSheets()
    Dim ws As Worksheet, Merged As Worksheet, rng As Range
    Set Merged = Sheets.Add
    Merged.Name = “MergedData”
    With Merged.Cells(1, 1)
        For Each ws In Worksheets
            If ws.Name <> “MergedData” Then
                .Offset(1, 0).Resize(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count).Value = ws.UsedRange.Value
                .Offset(1, 0).Resize(1, 1).Value = ws.Name
                .Offset(1, 0).Resize(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count).Interior.Color = RGB(255, 255, 204)
            End If
        Next ws
    End With
End Sub

Copy this script into a VBA module (Alt + F11, Insert > Module) and run it.

Method 4: Using Excel’s Query Editor for Simple Data Merge

For a simpler approach with less technical know-how:

Steps to Merge Sheets with Query Editor

• Select a cell in any sheet and go to Data > Get Data > From Other Sources > Blank Query.
• In the Query Editor, right-click on the left sidebar and select Merge.
• Choose your sheets from the list and merge based on common headers.
• Expand the merged table in the Query Editor to view the combined data.
• Close & Load the query to Excel.

Method 5: Manually Copying and Pasting

While not as efficient as other methods, manual copying can be effective for small datasets:

Steps for Manual Merge

• Open all Excel files containing the sheets you want to merge.
• Select and copy the data range from the first sheet.
• Create a new sheet for the merged data or use an existing one.
• Paste the copied data into the destination sheet, adjusting for alignment if needed.
• Repeat this process for each sheet, adding labels to indicate the source sheet for clarity.

🖱️ Note: This method is prone to human errors, especially for larger datasets.

Excel provides numerous ways to merge sheets depending on the complexity of the task at hand. Whether you're looking for a quick manual merge or need to automate the process for regular updates, Excel's features like Consolidate, Power Query, and VBA cater to all levels of users. By understanding and utilizing these methods, you can streamline your data management, enhance productivity, and reduce the likelihood of data entry errors.

Can I automate the merging of sheets regularly?

+

Yes, by using Power Query with a query that references external files, or by creating a VBA script that runs on a schedule.

What if my sheets have different column names?

+

Methods like Power Query allow you to map columns from different sheets even if they don’t match exactly. You can rename or reorder columns as part of the merge process.

Is there a limit to how many sheets I can merge?

+

Excel itself doesn’t impose a hard limit on the number of sheets you can merge, but practical limits arise from memory constraints and file size.