3 Simple Ways to Merge Excel Sheets Quickly

🚨 Pro-Tip: Before diving into these methods, ensure all your Excel sheets are saved in the same file format (.xls, .xlsx) to avoid compatibility issues.

1. Using Consolidate Feature in Excel

Excel’s built-in Consolidate feature is a quick and user-friendly way to combine data from multiple sheets into one master sheet:

• Open Excel and click on the tab where you want your consolidated data to appear.
• Go to the “Data” tab, find the “Data Tools” group, and select “Consolidate.”
• In the Consolidate dialog, choose your function (SUM, COUNT, AVERAGE, etc.) for how you want to merge data.
• Under “Reference,” navigate to the first sheet with data you want to consolidate, highlight the cells, and then click “Add.”
• Repeat the above step for each sheet you need to include.
• Check “Create links to source data” if you want updates in the source sheets to reflect in the consolidated sheet.
• Click “OK.”

📝 Note: If your sheets have headers, ensure they are consistent to avoid misinterpretation during consolidation.

2. Using Power Query

For users comfortable with Excel’s advanced features, Power Query provides a robust tool for merging sheets:

• From the “Data” tab, select “Get Data” then “From File” > “From Workbook.”
• Navigate to your Excel file and select it to open Power Query Editor.
• In the “Navigator” window, check the sheets you want to merge.
• Click “Load” to load the data into Excel.
• Once loaded, select “Append Queries” in the “Home” tab to merge the data into a single table.

Power Query also allows for data transformations and cleaning which can be very handy for complex datasets.

3. Using VBA Macro

Automate the merging process with a simple VBA macro:

• Open the Excel file where you want to create your macro.
• Press ALT + F11 to open the VBA editor.
• Insert a new module and paste the following code:

  
  Sub MergeSheets()
      Dim WS As Worksheet
      Dim DataSh As Worksheet
      Dim i As Integer
      Dim LastRow As Long
  
  ' Add a new worksheet for the consolidated data
  Set DataSh = Worksheets.Add
  DataSh.Name = "Consolidated Data"
  
  ' Set the first row of the consolidated sheet
  Set WS = Worksheets(1)
  LastRow = WS.UsedRange.Rows.Count
  WS.Range("A1", WS.Cells(LastRow, WS.UsedRange.Columns.Count)).Copy Destination:=DataSh.Range("A1")
  
  ' Merge data from subsequent sheets
  For i = 2 To Worksheets.Count
      Set WS = Worksheets(i)
      LastRow = DataSh.UsedRange.Rows.Count
      WS.UsedRange.Copy Destination:=DataSh.Cells(LastRow + 1, 1)
  Next i
  
  MsgBox "Merge completed successfully."
  
  
  End Sub
      

• Close the editor, save your workbook as “Macro-Enabled Workbook” (.xlsm).
• Run the macro by going to “Developer” tab > “Macros” > select “MergeSheets” > click “Run.”

This macro assumes all sheets in the workbook have the same structure.

⚠️ Note: Using macros can introduce security risks. Always ensure your files are from trusted sources.

Which Method is Best for You?

The best method depends on:

• Complexity: If you’re dealing with simple, uniform data, the Consolidate feature might suffice. Power Query or VBA Macros offer more flexibility for complex tasks.
• Repetition: If you often perform this task, consider mastering Power Query or creating a VBA macro for automation.
• Future Updates: If you need the consolidated sheet to reflect changes in the source sheets, then the “Create links to source data” in Consolidate or Power Query’s dynamic data refresh are advantageous.

By merging data from various Excel sheets, you not only streamline your data management process but also enhance your ability to analyze and present data effectively. Understanding these methods allows you to choose the one that best fits your workflow, ultimately saving you time and reducing potential errors in data handling.

What if my Excel sheets have different structures?

+

If your sheets have different structures, you might need to manually align the data before using these methods or use Power Query for more control over data transformation.

Can these methods update dynamically?

+

Yes, both the Consolidate feature with “Create links to source data” and Power Query can be set up for dynamic updates, reflecting changes from the source sheets automatically.

What should I do if I need to merge data from sheets in different Excel files?

+

For different files, you can open them as separate windows in Excel or use Power Query to load and combine them in one workbook.

Is there a limit to the number of sheets I can merge?

+

The limit primarily depends on your computer’s memory and Excel’s capabilities, but practically, you can merge hundreds of sheets without issues if your system can handle it.