Reading time: 7 minutes
Working with different Excel files at the same time is quite challenging and frustrating. Especially when you need to prepare analysis reports, presentations, or compile data for projects. Whether you are an IT professional or a business professional, you might need to merge multi Excel files to single file.
This detailed guide will walk you through some manual methods to merge multiple Excel files into a single file for beginner to advanced Excel users.
Method to merge multiple Excel files to single file
There are various methods available to merge multiple Excel files into a single file. Here are some of them:
Method 1. Using the copy-paste method
If you have small datasets (<1000 rows), then you can try the copy-pasting method. Here’s how to perform it:
Step 1. Open the Excel sheet and copy all the data by pressing the Ctrl+A keys, then the Ctrl+C keys on your keyboard.
Step 2. Open another Excel sheet where you want to save all the data and paste the copied data there.
Method 2. Using VBA script
Here are the steps to merge multiple Excel files into a single file using a VBA script:
Step 1. Open Excel and press Alt+F11 keys to open Microsoft Visual Basic for Applications.
Step 2. Click on the Insert button, then click Module.
Step 3. Paste the below-mentioned VBA script in the dialogue box.
Sub MergeExcelFiles() Dim fnameList, fnameCurFile As Variant Dim countFiles, countSheets As Integer Dim wksCurSheet As Worksheet Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:=”Microsoft Excel Workbooks (.xls;.xlsx;.xlsm),.xls;.xlsx;.xlsm”, Title:=”Choose Excel files to merge”, MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next
wbkSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox “Processed ” countFiles ” files” ; vbCrLf “Merged ” countSheets ” worksheets”, Title:=”Merge Excel files”
End If
Else MsgBox “No files selected”, Title:=”Merge Excel files” End If End Sub
Note: Make sure Excel sheets are correctly closed. If not closed, then you may receive a run-time error.
Step 4. Click on Run >Run Sub/UserForm or press the F5 keys.
Step 5. Click on the Run button to execute the process.
Method 3. Using Power Query
Another way is to use Power Query to merge multiple Excel files. Follow the below mentioned steps to do so:
Step 1. Open Excel and click Data >Get Data >From File >From Folder.
Step 2. Select the Excel folder to merge and click Open.
Step 3. Click on the Combine button and select the Combine & Load To option.
Step 4. Select the worksheet present on the left side of the screen, then click OK.
Step 5. Select Table >New worksheet and click OK.
Step 6. Power Query will begin the merge process, and soon your Excel files will be merged into a single file.
Method 4. Using Excel’s Consolidate Tool
Here are the detailed steps to merge multiple Excel files using the Consolidate tool:
Step 1. In Excel, go to Data >Data Tools >Consolidate.
Step 2. Click on Browse to add reference, then choose the Function >Use labels, and then click OK.
Limitations of Manual Methods
There are various limitations of manually merging multiple Excel files into a single file. Some of them are:
- Time-consuming for a large number of Excel files.
- Need to perform multiple times, hence risk of human error.
- Chances of formatting and formulas loss.
- There is no data validation, hence the risk of data leaks increases.
- Power Query doesn’t work in older Excel versions.
- Hard to track which files were merged.
- Performance lags during the copy-paste method.
Use an Automated Excel Merge Tool- Recommended Solution
When looking for the best solution to merge multiple Excel files to single file, an automated Excel merge tool stands out best. Kernel Excel Merge tool is an excellent software which can easily merge multiple Excel files within minutes without data loss. This tool comes with numerous robust features, such as:
- Merge multiple Excel files into a single workbook.
- Merge unlimited Excel files into individual workbooks.
- Keep all the formulas and formatting intact.
- Support both XLS and XLSX formats.
- Allow saving progress report in CSV format.
- Provide accurate results without data loss.
- No need to install Excel on the system to run the software.
- Free trial allows merging 25 records per Excel file for free.
Conclusion
Merging multiple Excel files into a single file is a good option for efficient data management. But merging Excel files manually can cause potential errors and data loss. Thus, we recommend using the Kernel Excel Merge tool, an instant merging solution. This tool makes the entire process hassle-free and provides guaranteed results.
Frequently Asked Questions
Ans. There are many reasons to merge multiple Excel files into a single file, such as:
1. It saves a lot of time in switching between multiple files.
2. Better collaboration and enhanced data analysis.
3. Improved performance and reduced errors.
4. Ensures uniform formatting and data structure.
Ans. Use the Kernel Excel Merge tool to combine multiple Excel files into one without losing formatting and structure.
Ans. You can use Excel’s Power Query to merge multiple Excel files into a single file for free.
