How to Merge Multiple Excel Sheets into One? [A Detailed Guide]
Published On - November 21, 2025
It is quite a challenging task for users to handle multiple Excel workbooks. Merging Excel workbooks into a master file is the best option to organize the scattered data. But the task itself seems complicated to many users.
Therefore, the blog will guide you on multiple ways to merge Excel file data manually. It includes practical and real-life techniques like Power Query, formulas, VBA automation, and more.
Before You Merge: Prepare This Checklist to Save Hours!
Prepare the following things before you start merging Excel workbooks. It helps to prevent errors, duplicates, mismatched columns, and formatting issues.
- Back up the original files to a safe location.
- Make standardized headers for all the workbooks.
- Check and be ready with a complete list of all the files.
- Select the appropriate merging method as per your needs.
- Verify the merged cells, hidden rows/columns, date formats, blank columns, and inconsistent text.
6 Best Ways to Merge Excel Files Manually!
We have tried and tested several methods to combine Excel workbooks, and these ones worked for us. You can check them out individually to find the most suitable one for yourself.
Solution 1: Manual Copy & Paste (For Small Jobs)
You can use this method when you have a limited number of files with a similar structure. Here’s how:
Step 1. Open the master and source workbook.
Step 2. Press Ctrl+A keys to select all data from the source file. Then, copy data using Ctrl+ C keys.
Step 4. Go back to the maser workbook and click on the next available empty row.
Step 5. Press Ctrl+V keys to paste the copied data.
Step 6. Convert the merge data into a table with Ctrl+T keys.
Pro tips:
- Always paste as Values.
- Keep the header only once.
- Remover blank rows before merging data
Solution 2: Merging Excel Data using the Consolidate Option
The Consolidate feature of Excel helps users to merge values like Sum, Average, Count, and more from multiple sheets. Here are the complete steps to do so:
Step 1. Open the workbook and go to Data tab.
Step 2. Then, click on Consolidate option.
Step 3. Choose the required Function to proceed.
Step 4. Browse to Add the suitable reference.
Step 5. Check the Top Row and Left Column option. Then, click OK.
Important to Know:
- Not suitable for row-by-row merging.
- Combine multiple sheets with an identical layout.
- Best for summarized results, not for raw data consolidation.
Solution 3: Merge Workbook using Power Query
Power Query is the best option to merge multiple Excel sheets into one single workbook. It automates the entire process and easily handles inconsistent data types. Here’s how:
Step 1. Create a dedicated folder and put all the Excel files in it.
Step 2. Import the entire folder into the Excel application.
- Go to Data tab and click on Get Data.
- Choose From File> From Folder and select the folder.
Step 3. Click on Combine & Transform Data to proceed.
Step 4. Transform your data inside Power Query Editor. You can perform actions like:
- Promote headers
- Remove blank rows
- Change data types
- Standardize columns
- Add “SourceFile” column if needed.
Step 5. Tap on Close & Load to worksheet or Data Model.
Step 6. Refresh to update datasets automatically.
Pro tips:
- Use “Remove Duplicates” to avoid duplicate entries.
- Disable load for intermediate queries to speed things up.
- Use Append Queries for merging different sheets into one workbook.
Solution 4. Merge Excel Sheets Using Formulas
This method works best in Excel 365 or 2021 with dynamic arrays to merge sheets with a consistent structure. The detailed steps are as follows:
1: Use VSTACK()
This formula will help you stack the ranges vertically.
=VSTACK(Sheet1!A1:D100, Sheet2!A1:D100, Sheet3!A1:D100)
2: Using INDIRECT() (Legacy Method)
The INDIRECT function helps to create dynamic formulas that can change without being manually updated.
=INDIRECT(“‘Sheet1’!A1”)
Warning: It recalculates data constantly and can slow down workbooks.
3: Combine two sheets without duplicates
Use a helper column to merge both sheets and remove duplicates instantly.
=UNIQUE(VSTACK(Sheet1!A:D, Sheet2!A:D))
Solution 5. Using a VBA Macro (For Automated & Repeatable Merging)
If you frequently merge dozens of Excel files, VBA can save hours. If you are merging Excel workbooks on a frequent basis, then VBA can save lots of time. You prepare weekly or monthly reports in multiple batches with customized merge rules. Here’s how:
Step 1. Press ALT+F11 to access VBA.
Step 2. Navigate to Insert tab and click Module.
Step 3. Paste the VBA script to merge all workbooks.
Sub MergeWorkbooks()
Dim wb As Workbook, ws As Worksheet
Dim fPath As String, fName As String
Dim masterWS As Worksheet
Dim lastRow As Long
Set masterWS = ThisWorkbook.Sheets(“Master”)
fPath = “C:\YourFolderPath\”
fName = Dir(fPath & “*.xlsx”)
Do While fName <> “”
Set wb = Workbooks.Open(fPath & fName)
For Each ws In wb.Sheets
lastRow = masterWS.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.UsedRange.Copy masterWS.Cells(lastRow, 1)
Next ws
wb.Close False
fName = Dir
Loop
End Sub
This macro does the following things:
- Open every workbook in the folder.
- Copy all the used ranges.
- Add them to the master sheet.
- Close each file automatically.
Safety Tips:
- Always run VBA on data copies.
- Avoid merging formulas and convert them to values.
Solution 6. Power Pivot / Data Model (For Massive Data)
Power Pivot/Data Model is the ultimate solution to merge rows, dimension tables, and Lookup relations on a large scale. Here are the steps to do so:
Step 1. Load each workbook using Power Query.
Step 2. Add to the Data Model and create relationships.
Step 3. Build PivotTables for detailed analysis
Note: This method gives you fast performance and scalable reporting.
Key Exceptions or Special Cases You Shouldn’t Ignore!
Here is the list of the important exceptions that you may encounter while merging Excel data:
- Files with different column names: Use Power Query to map and rename fields.
- Dates appear incorrectly: Standardize using the “Change Type → Date” of Power Query.
- Merge without duplicates: Remove duplicates with Power Query or =UNIQUE() formula.
- Combine all sheets from every workbook: Use a VBA loop or the “Combine Sample File” function of Power Query.
- Merge formulas & values: Convert formulas to values before merging to avoid broken references.
Post-Merge Checklist: Your Final Quality Assurance!
Always check the following list of items once the merging process of Excel sheets is completed:
- Total rows count
- Missing columns or headers
- Duplicate entries
- Formatting consistency
- Random blank rows
- Mixed data types.
Once everything looks good, save a backup file.
Direct Approach: Merge Excel Workbooks Instantly!
As you can see, there are different manual ways to merge multiple Excel sheets into one. But they often require careful execution to get the desired results. In addition to that, the situation becomes more complicated when you have to combine large datasets with exceptional cases.
Therefore, using advanced tools like Recoveryfix Excel Merger tool will be the best approach. The Excel Merger tool helps to merge several sheets without any errors.
Conclusion
The blog has covered a detailed guide on how to merge workbooks using the best available manual methods. Every method has its own advantages and limitations that serve different Excel users. You try to pick the most suitable one to consolidate your data. If you want to avoid complex procedures, then go for the recommended solution.
FAQs
Q-1 What is the best way to merge Excel workbooks automatically?
A- Power Query is one of the best ways to merge Excel workbooks automatically. It refreshes all the recent changes in just one click.
Q-2 Can I merge sheets with different column names?
A- Yes, you can rename, reorder, or map columns easily with the help of Power Query.
Q-3 How do I merge files without duplicates?
A- You can use the Remove Duplicates or UNIQUE() function to avoid duplication.
Q-4 Can I merge files without opening them?
A- Yes, you can use VBA or PowerShell scripts to merge files without opening them.
Q-5 What if my Excel version doesn’t support Power Query?
A- You can use VBA macros or formulas like VSTACK and INDIRECT instead of Power Query.
