merge excel files

Question

How to merge several Excel file into one worksheet in Excel?

Answer 1

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:

1. Saving All the Files into a Single Folder
2. Combining them using Power Query
3. Merging Data into a Single Table
4. 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:

1. 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).
2. Now, the next thing is to open a new Excel workbook and open “POWER Query”.
3. For this, go to Data Tab ➜ Get & Transform Data ➜ Get Data ➜ From File ➜ From Folder.
4. Here you need to locate the folder where you have files.
5. 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.
6. Now, you need to combine data from these files and for this click on “Combine & Edit”.
7. 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.
8. 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.
9. So, right-click on the column header and select “Replace Values”.
10. 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).
11. After that, double click on the header and select “Rename” to enter a name for the column i.e. Zone
12. 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”.

Answer 2

• Power Query / Get & Transform – Merge, Join, Append, Combine
• Power Query / Get & Transform – Combine All or Selected Excel Sheets or Tables in Folders

Answer 3

@Patrick Wu

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.

Answer 4

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);  
        }  
    }  

Answer 5

To merge several Excel files into one worksheet in Excel, you can use the following steps:

1. Open the first Excel file that you want to merge.
2. Go to the worksheet that you want to merge the other files into.
3. Click the cell where you want to start the merge.
4. Go to the Data tab and click on the “From Other Sources” button in the “Get External Data” group.
5. Choose “From Microsoft Query” and click OK.
6. In the Choose Data Source dialog box, select the “Microsoft Excel” option and click OK.
7. In the Select Workbook dialog box, navigate to the location of the second Excel file that you want to merge and select it.
8. Choose the worksheet in the second Excel file that you want to merge, and click OK.
9. Repeat the process for each additional Excel file that you want to merge, selecting the appropriate worksheet from each file.
10. When you’re finished, click the “Return Data” button to bring the data into your first worksheet.
11. You may need to adjust the formatting of the merged data to make it look the way you want.

These steps will merge the data from several Excel files into one worksheet in a new workbook. You can save this workbook as a new file, or copy and paste the merged data into an existing workbook.