5 Ways to Merge Sheets from Different Excel Workbooks

The need to merge data from different Excel workbooks arises frequently in various professional settings. Whether it's for consolidating financial reports, combining project data, or integrating departmental information, merging sheets from separate Excel files can streamline your workflow significantly. In this comprehensive guide, we'll explore five effective methods to merge sheets from different Excel workbooks, ensuring that you can handle even complex datasets with ease.

Method 1: Using Power Query in Excel

Power Query, introduced in Excel 2010 as a part of Excel for Microsoft 365, is an incredibly powerful tool for merging data from multiple sources. Here’s how you can use it to combine sheets:

• Open Excel and navigate to the Data tab, then select Get Data > From File > From Workbook.
• Select the first workbook you want to merge data from.
• After the workbook is loaded into Power Query Editor, choose the sheets or ranges you want to combine by clicking on their tabs at the left side.
• To merge data, go to the Home tab, click Append Queries, and select Append.
• Select the second workbook or sheet to append data from. Repeat this for each workbook.
• Once all data is combined, click Close & Load to bring the merged data back into Excel.

💡 Note: Remember to specify the sheet names correctly in Power Query to avoid errors while merging.

Method 2: VLOOKUP Function

For simpler datasets where you need to combine information based on a common key, VLOOKUP can be your go-to solution:

• Open the workbook where you want the merged data to reside.
• Identify a unique key (like an ID or name) in both workbooks to link them.
• Use the VLOOKUP function in the cell where you want the combined data to appear:

  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

For example, to look up the name from workbook 1 in workbook 2:

🔔 Note: VLOOKUP returns approximate matches by default. Use FALSE for an exact match.

Method 3: INDEX and MATCH Combo

When VLOOKUP doesn’t suit your needs, especially for leftward lookups, INDEX and MATCH come to the rescue:

• Use INDEX to return a value from a specific row and column:

=INDEX(array, row_num, [column_num])

• And MATCH to locate the position of a lookup value in a range:

=MATCH(lookup_value, lookup_array, [match_type])

Combine these to create dynamic lookups:

=INDEX([Workbook2.xlsx]Sheet1!$B:$B, MATCH(A2, [Workbook2.xlsx]Sheet1!$A:$A, 0))

📌 Note: This method is more versatile than VLOOKUP because it doesn't require sorting or exact matches.

Method 4: Consolidate Data

Excel’s Consolidate feature can be used to combine data based on row or column labels:

• Open the destination workbook.
• On the Data tab, click Consolidate.
• Select the function you want to use (e.g., Sum, Average).
• Add all the ranges from different workbooks you want to consolidate. Ensure all workbooks are open or referenced correctly.
• Choose your linkage options (by position or labels) and click OK.

🛑 Note: The workbooks must have similar structures for this method to work effectively.

Method 5: VBA Macro

For advanced users, VBA (Visual Basic for Applications) allows you to automate complex tasks, including merging sheets from different workbooks:

• Open the VBA editor by pressing ALT + F11.
• Insert a new module from Insert > Module.
• Write or copy a VBA script that will loop through each workbook and sheet, copy data, and paste it into a master workbook or sheet.

Sub MergeSheets()
    Dim ws As Worksheet
    Dim master As Workbook
    Dim wb As Workbook
    Dim sourceRange As Range

    Set master = ThisWorkbook
    ' Assuming the sheets to be merged are named similarly in each workbook
    For Each wb In Application.Workbooks
        If wb.Name <> master.Name Then
            For Each ws In wb.Worksheets
                ' Set the range to copy
                Set sourceRange = ws.Range("A1").CurrentRegion
                ' Paste into the next available row in the master workbook
                With master.Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                    .Value = sourceRange.Value
                End With
            Next ws
        End If
    Next wb
End Sub

🚨 Note: Always save backups before running macros that alter data, as macros can be destructive if coded incorrectly.

In summary, merging sheets from different Excel workbooks is crucial for data analysis and reporting across various industries. Whether you opt for the simplicity of VLOOKUP or the automation capabilities of VBA, each method has its merits:

• Power Query is ideal for transforming and merging data from multiple sources with a visual, easy-to-use interface.
• VLOOKUP is straightforward but limited to lookups where the search value is on the left side of the data you’re extracting.
• INDEX and MATCH offer flexibility in lookup directions, making them useful for more complex scenarios.
• Consolidate works well when your data needs to be summarized using functions like sum or average, but requires structural similarity across workbooks.
• VBA provides automation for merging sheets, but requires a good understanding of programming concepts.

How can I merge sheets if they have different structures?

+

If sheets have different structures, you might need to manually align data or use VBA to handle varying structures. Power Query is also flexible enough to adjust for minor structural differences.

What happens when there are duplicate rows during merging?

+

Depending on the method, duplicates can either overwrite existing data, or you can use data manipulation techniques in Power Query or Excel formulas to handle or keep duplicate entries.

Can I merge data from workbooks without opening them?

+

With VBA, you can create scripts to access and merge data from closed workbooks, though direct access to file paths might be necessary.