Merge 2 Excel Files

  1. Merge 2 Excel Files
  2. How To Merge Spreadsheet Data
  3. Combine Excel Sheets
  4. Merge 2 Excel Files Using Vlookup
  5. Merge 2 Excel Files Macro

Last updated on August 8th, 2020 at 08:09 pm

Reading Time: 7minutes

Enable Excel, and click Kutools Plus Combine, in the Combine Worksheets dialog, check Combine multiple worksheets from workbooks into one worksheet option. Enable Excel, and click Kutools Plus Combine, in the Combine Worksheets dialog, check Combine multiple worksheets from workbooks into one worksheet option. In the second Combine Worksheets window, click Add File or Folder to open the Open dialog, then select the workbook you want to combine all sheets into one. First, open Excel and open both of the files you're looking to merge. Make sure the column headers are the same. Now open a new spreadsheet, which will be the destination for your merged data. From this new spreadsheet, click on the 'Data' tab and click on 'Consolidate.'

First use Read Range to read the excel files, then use merge data table activity to merge the data table then use the resultant data table to make the merged excel file using WriteRange activity! And this will convert it into PDF Ruchi15 (Ruchi) July 23, 2018, 7:18am #10 i did the same but getting errors. Apr 01, 2020 Merge multiple Excel files into one dataset using Python. A few interesting observations about the final combined dataframe: Both PolicyID (from df1) and ID (from df2) got brought into the dataframe, we’ll have to drop one to clean up the data.

Excel Merger is a free tool for combining multiple Microsoft Excel (XLS/XLSX) files into one file. You can also use it to merge multiple Excel Spreadsheets and Workbooks into a single file or Workbook.

As if that’s not enough already, this tool merges columns and rows in an Excel worksheet. And just as important, it also supports the merging of ODS/CSV/XML files.

The software’s claim to fame is its ability to consolidate data in a single file or Workbook in a few sort clicks. As a result, Excel Merger allows you to quickly summarize and analyze data in a single sheet/workbook.

Benefits of Merging Excel Files into One

Regardless of your field, there are many reasons why you would want to merge multiple excel files into one sheet. For example, if you’re an analyst or manager, you would need to combine budgets and estimates from various departments. A student may need to combine assignments, analyses, research data, etc.

In addition to making the information more presentable, excel merging helps you to create one single reference file. You won’t have to move back and forth between multiple documents just to find all the information you need. Instead, you consolidate all the useful data from more than one source and summarize it in one place.

Besides, the best merge Excel files tools allow you to use source data from different worksheets that are not Excel files. For instance, Excel Merger can import data from ODS/CSV/XML and consolidate it in one Excel file. That’s very important because, more often than not, it’s easier to manipulate data in Excel than in other formats.

Excel Merge: The Best Free Tool for Merging Excel Files

There are so many excel combine tools, but Excel Merger packs just enough features to edge them all. Below are all the good things that it brings to the table:

  • Free: Excel Merger is completely free to download and use. It’s a PC-based software that works on all versions of Windows from XP all through Windows 10. With a size of 3.3 MB, the file is small and lightweight enough for new and older computers.
  • Effective Excel combines tool: the software allows you to merge multiple Excel files into a single file in just 3 steps. Please read the step-by-step guide below to find out how you can do that.
  • Create one spreadsheet: in addition to creating one Excel file, this program can pick multiple spreadsheets from multiple Excel files and merge them into one spreadsheet in a single Excel file.
  • Merge workbooks and worksheets: if you have multiple workbooks in a file, you can use Excel Merge to combine them all in a single worksheet for easy analysis. Similarly, the software can merge workbooks from different files into one workbook in one file.
  • Sheet range: this function allows you to select a few worksheets from an Excel file and merge only them instead of merging the entire document. This is a handy feature for when you only need data from a select number of worksheets rather than the entire document. You can specify the range of worksheets yourself.
  • Merge rows and columns: you can pick particular rows and columns from many different files or spreadsheets and merge them in one spreadsheet. For example, you can pick row 2 from one document, row 14 from another document and row 27 from the final document and then combine them into a single row in one spreadsheet. In essence, Excel Merger consolidates data from any number of rows or columns into one row/column for easy analysis.
  • Import data from other file formats: Excel Merger is the best Excel editor because it can import data from ODS (Open Document Sheets) CSV (Comma-Separated Values) and XML (Extensible Markup Language) files.
  • Ultra-fast: merge files, spreadsheets and worksheets in just 3 steps. Excel Merger processes the data lightning-quickly to get the job done.
  • Doesn’t require Microsoft Excel or Microsoft Office: you don’t need to install Office or Excel to use Excel Merger.
  • Multi-lingual: supports English, Chinese, Dutch, French, German, Greek, Italian, Japanese, Korean, Polish, Portuguese, Russian, Spanish, and Swedish

How to Merge Excel Files, Spreadsheets and Workbooks with Excel Merger: A Step by Step Guide

First, download Excel Merger for Windows here.

Once installed you can perform any of the following Excel merging functions:

Excel

1. Merge Multiple Excel Files into One Excel File

How do I consolidate multiple Excel files? Below is a complete guide. Note that Excel Merger supports both XLS and XLSX type of files.

Step 1: Add files to Excel Merger

There are two ways of doing that. First you can drag and drop your files to the program’s interface. Secondly, you can use the “Add” button.

Click on it and then browse your PC to where the files are located. Select all the ones that you want to merger and click “Open” to add them.

Step 2: Set options

Since you are merging files, select “Files” in the Merge dropdown menu.

Head over to the “Output” dropdown menu and choose your preferred output format. It’s XLSX by default but your options are XLS, XLSX, CSV, XML & ODS. Finally, set your output path under the “Save to” option.

Step 3: Merge

Click on “Start Merging Now” to consolidate your Excel files into one.

Excel Merger will automatically open the output folder once the merger is complete.

2. Merge Excel Spreadsheets and workbooks into one

How do you merge two excel spreadsheets together? And how do you merge two Excel workbooks together? Excel Merger can help. Better yet, it can merge more than two spreadsheets or workbooks. Here’s how:

Step 1: Add files to Excel Merger

You can either drag and drop Excel files or use the “Add” button.

Step 2: set options

Since you are merging sheets, select “Sheets” in the Merge dropdown menu.

Go to the “Output” dropdown menu and choose your preferred output format between XLS, XLSX, CSV, XML & ODS. Specify the worksheet range if you only want to merge a few worksheets instead of the entire file. Finally, set your output path under the “Save to” option.

Step 3: Merge

Click on “Start Merging Now”.

Excel Merger will combine all the workbooks in all the files that you added and produce one consolidated worksheet.

3. Merge Multiple Excel Files into One Excel Workbook

How do I consolidate multiple Excel files into one workbook? Below is a step-by-step guide. It will help you merge several Excel files into a single Excel workbook in one Excel file.

Step 1: Add files to Excel Merger

Files

Drag and drop Excel files onto Excel Merger or use the “Add” button.

Step 2: set options

In the Merge dropdown menu, select “Files & Sheets”.

In the “Output” dropdown menu, choose your preferred output format. It could be XLS, XLSX, CSV, XML or ODS. Now, set your output path under the “Save to” option.

Step 3: Merge

Click on “Start Merging Now” to combine all the selected Excel files and workbooks into one workbook.in one file

4. Merge Excel Rows and Columns in a Worksheet

How do I combine columns in Excel? And how can I merge rows in a spreadsheet?

Step 1: Add files to Excel Merger

Drag and drop Excel files to the Excel Merger interface or use the “Add” button.

Step 2: set options

Click on the “Merge” dropdown menu and choose either “Rows” or “Columns”.

Needless to say, choose the latter if you’re merging multiple columns and the former if you’re merging multiple rows.

Merge 2 Excel Files

In the “Output” dropdown menu, select your preferred output format (XLS, XLSX, CSV, XML or ODS). Finally, set your output path under the “Save to” option.

Step 3: specify range

Head to the boxes labelled “Start” and “End” and then type in the first row/column and the last row/column (respectively) that you want to merge.

Remember to specify the delimiter. This is basically a separator for rows/columns. It’s blank by default but you can choose a sign/symbol of your liking.

Step 4: choose what to do with empty rows/columns

By default, Excel Merger will merge rows/columns in the first row/column of the new worksheet and leave the original rows/columns empty. But you can tell it what to do with those empty rows/columns.

Check the “Keep Empty Rows/Columns” option if you want to retain them or uncheck it if you want to remove them.

How To Merge Spreadsheet Data

Step 5: Merge

Click on “Start Merging Now” to merge your rows/columns.

Merging Other Workbook File Formats

You can use the same procedures to merge files that are saved in other workbook formats besides Excel (XLS and XLSX). Specifically, Excel Merger allows you to merge CSV, XML and ODS files.

In the first step – when adding files – simply choose all the files that you want to merge, whether they are XLS, XLSX, CSV, XML or ODS files. Then follow the above procedures for merging files, worksheets, workbooks, rows and columns.

Merge 2 Excel Files

Having your data spread across multiple Excel files? Want to merge Excel files into a single Excel Workbook? Today we going to explore just that – how to merge multiple Excel Workbook spreadsheets into a single Workbook. The obvious tool for this task is Visual Basic for Applications so let us jump right in.

Want to merge Worksheets or CSV files instead?:

Merge Excel files manually

Open the destination Workbook

Open the destination Excel Workbook (in our example Destination.xlsx) to which you want to copy the Worksheets of the remaining Workbooks (in our example Source.xlsx).

The further steps need to be repeated for each Excel Workbook you want to copy to the destination Workbook.

Open (each) the Workbook you want to merge and copy Worksheets

Open each Excel Workbook you want to merge with the destination Workbook.

Next right-click on each Worksheet you want to copy, click Move or Copy.... In the Move or Copy Window select the destination Workbook (Destination.xlsx in our case). Next:

  • If you want to move (cut & paste) the Worksheet – simply click the OK button to proceed
  • If you want to copy (copy & paste) the Worksheet – select the Create a Copy checkbox and click the OK button


Repeat Step 2 for each Workbook you want to copy to the destination Workbook.

Merge Excel files using VBA

Let us assume we have a couple of files listed in our directory (in my example these are File1 and File2.xlsx). What we want to do is create a new Excel Workbook and copy Worksheets to this new Workbook. I wanted however to account for 2 typical scenarios:

  • Copy ALL worksheets
  • Copy only a single worksheet – with a specific name

The code below supports both these scenarios.

Merge Excel files code

Use the MergeExcelFiles Sub procedure below to merge any number of Workbooks:

How to use the procedure above? Below I create a simple Test procedure that lists the Excel files within the Workbook directory and merges the Workbooks.

Pretty simple right?

Merge Excel files within a directory

The scenario above works pretty well for situations where we want to list explicitly files we want to merge into a single Excel Workbook. How about when we have tons of files? Or to make it more simple, if we want to merge all files within a SINGLE directory. As this is also a typical scenario I have modified the above Test procedure to accommodate just that:

What happens is that we use the VBA Dir function to loop through all files within a selected directory. As we go along we modify our VBA Array redefining its size and adding additional items.

Combine Excel Sheets

Download the code

Want to download the code above and the example?

Merge Excel files in a whole directory structure

Merge 2 excel files based on a column

Merge 2 Excel Files Using Vlookup

One last scenario that came to mind is when we have a whole directory structure (directories within other directories) containing files with want to merge. As the VBA Dir function works only for a single directory and does not traverse any directories within we would need to amend additionally the code above. If needed I recommend reading my post on How to traverse directories using the VBA Dir function.

Merge 2 Excel Files Macro

Related posts: