How to merge several Excel file into one worksheet in Excel?
How to merge several Excel file into one worksheet in Excel?
Please refer to Lz-3068's answer, the Power Query could help us to merge multiple Excel files into one Worksheet.
Such as the following steps, you may have a look.
- Please put all files that you want to merge into one folder and turn all Excel files off.

- Open Excel to create a new blank workbook, go to Data > Get Data > From File > From Folder and navigate to the folder above, click OK.

- Click Combine & Load under the Combine drop-down menu.

- Select Sheet1 and click OK. The result as the following image shown.

For more information, you may refer to “Import data from a folder with multiple files (Power Query)”.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hi, you could try Spire.XLS.
Step 1. Install Spire.Xls.dll in your project via NuGet. 
Step 2. Use the following code snippet to do the merge.
using Spire.Xls;
using System;
namespace MergeExcelFiles
{
class Program
{
static void Main(string[] args)
{
Workbook newbook = new Workbook();
newbook.Worksheets.Clear();
Workbook tempbook = new Workbook();
string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
for (int i = 0; i < excelFiles.Length; i++)
{
tempbook.LoadFromFile(excelFiles[i]);
foreach (Worksheet sheet in tempbook.Worksheets)
{
newbook.Worksheets.AddCopy(sheet);
}
}
newbook.SaveToFile("result.xlsx", ExcelVersion.Version2016);
}
}
Power Query is the best way to merge or combine data from multiple Excel files in a single file. You need to store all the files in a single folder and then use that folder to load data from those files into the power query editor. It also allows you to transform that data along with combining it.
It works something like this:
Saving All the Files into a Single Folder
Combining them using Power Query
Merging Data into a Single Table
Make sure to download these sample file from here to follow along and check out this tutorial to learn power query.
Note: For combining data from different Excel files, your data should be structured in the same way. That means the number of columns and their order should be the same.
To merge files, you can use the following steps:
First of all, extract all the files from the sample folder and save that folder at the desktop (or wherever you want to save it).
Now, the next thing is to open a new Excel workbook and open “POWER Query”.
For this, go to Data Tab ➜ Get & Transform Data ➜ Get Data ➜ From File ➜ From Folder.
Here you need to locate the folder where you have files.
In the end, click OK, and once you click OK, you’ll get a window listing all the file from the folder, just like below.
Now, you need to combine data from these files and for this click on “Combine & Edit”.
From here, the next thing is to select the table in which you have data in all the workbooks and yes, you’ll get a preview of this at the side of the window.
Once you select the table, click OK. At this point, you have merged data from all the files into your power query editor and, if you look closely you can see a new column with the name of the workbooks from which data is extracted.
So, right-click on the column header and select “Replace Values”.
Here in the “Value to Replace” enter the text “.xlsx” and leave “Replace With” blank (here idea is to remove the file extension from the name of the workbook).
After that, double click on the header and select “Rename” to enter a name for the column i.e. Zone
At this point, your merged data is ready and all you need is to load it into your new workbook. So, go to the Home Tab and click on the “Close & Load”.
Now you have your combined data (from all the workbooks) into a single workbook.
This is the moment of JOY, write “Joy” in the comment section if you love to use “Power Query for combining data from multiple files”.
7 people are following this question.