How to Merge Excel Files and Sheets: Proven Methods for Efficiency

Need to consolidate multiple datasets into one spreadsheet? Discover efficient, expert-recommended methods to streamline your workflow.

Excel data often spans multiple sheets or files, making analysis cumbersome. While copy-pasting works for small tasks, it’s time-consuming for larger datasets. As an experienced Excel power user, I’ve tested these reliable techniques that save hours.

Merge Excel Sheets into a Single Workbook

Excel’s built-in tools make combining sheets straightforward. Open all sheets you want to merge, then follow these steps:

1. Go to Home > Format > Move or Copy Sheet.
2. Select (new book) from the "To book" dropdown.
3. Choose the position in the Before sheet box if needed.
4. Click OK.

Repeat for each sheet, then save the new workbook. This method is fast for simple merges.

Consolidate Data onto One Sheet

For combining datasets into a single sheet, use the Consolidate feature. Ensure data shares identical layouts (same column headers).

Create a new sheet, go to Data > Consolidate:

1. Select Sum (or another function).
2. Click Add to reference ranges from sheets or files (use Browse for external workbooks).
3. Check Top row and Left column for labels.
4. Enable Create links to source data for dynamic updates.
5. Click OK.

This works best for numeric data; text requires VBA.

Combine Multiple Workbooks with VBA

For automating merges from many files, use this VBA macro. Place all files in one folder, create a new workbook, and enable the Developer tab.

Press Alt + F11 to open VBA, insert a module, and paste:

Sub GetSheets()
    Dim Path As String, Filename As String
    Path = "C:\Users\Brad\MakeUseOf\MergingSheets\"  ' Update path
    Filename = Dir(Path & "*.xls*")
    Do While Filename <> ""
        Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
        For Each Sheet In ActiveWorkbook.Sheets
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
        ActiveWorkbook.Close
        Filename = Dir()
    Loop
End Sub

Save as .xlsm, run the macro. All sheets will consolidate automatically.

Plan Ahead for Smoother Merges

Merging large spreadsheets post-creation often leads to issues. Design workbooks with consolidation in mind from the start for long-term efficiency.

Have tips for merging datasets? Share in the comments!