Merge Excel files(sheets) into one

If you've found this page, probably you need to merge your Excel files into one, or to merge contents of all worksheets you have in the Excel file into one sheet. There could be various reasons why you need to do this operation. Possible, you've split some work to several co-workers and after getting result you need to combine all files into one. Also, this operation is often requested for CSV files, when you need to merge several CSV files into one. CSV file is very similar to Excel worksheet, so all comparison and merging operations applicable to XLSX files are applicable to CSV files also.

What are the typical methods of merging Excel files?

Open Excel files you are going to merge

To merge your Excel files, copy sheets from one Excel file into another using Move or Copy command in the Right Click Menu.

In the opened Move or Copy dialog you need to select option Create a Copy.

As you see - this method is extremely simple and easy. But it requires your working time. In this modern life this is important disadvantage. Your working time and effectiveness are the most important KPIs.

This is another method you can use to automate this job.

VB macro does exactly same operation, as in the previous method. But it works automatically.

So, this is better solution for merging Excel files.

This is an alternative to the methods described above.

Why do you need a utility for this job, as you can do it manually? If the solutions described above works for you - this is good, and probably you can stop on this.

But we would like to introduce you xlCompare - tool for comparing and merging Excel worksheets.

Why do you need xlCompare?

The answer is quite simple - Merge Excel Files functions are FREE in xlCompare.

Other functions that require file comparison are not free and require a license. But if you need to merge your Excel files, xlCompare will save your time:

• You don't need to copy and paste worksheets between Excel files
• You don't need to paste and configure VBA macro to combine your worksheets
• You are getting all of this for FREE

Why not use free functions of the professional tool?

xlCompare is a tool to compare Excel files for differences. It has complete set of merge commands for merge operations. To combine two Excel files into one, you need to do the following:

Drop your files into xlCompare window.

File on the right panel will receive the data. So, if you've dropped source workbook into the left panel - use switch panels command.

After you drop second file xlCompare offers you to compare them. Just ignore this request by closing the Comparison Wizard window.

Now go to the Merge tab

Use Merge Files command

In one click all cells from the left workbook are moved into the right workbook.

You've got an immediate result.

Do you still want to copy and paste your data between Excel files?

Next command on the Merge panel is Merge Sheets. It is simple and clear.

It copies all the data from left worksheet into the right worksheet. In other words - it appends data.

Very easy and handy command.

Imagine following case - you have 100 Excel file in a folder that have subfolders. You goal is to combine all data into one sheet for further processing.

Manual method is not an option. VBA macro can do this work, but you need to configure it on your computer.

In xlCompare all you need is to select a folder, whose contents you would like to merge.

After getting a folder xlCompare shows you list of all files that will be merged. Here you can exclude specific files.

Press OK and get result of merge operation opened in the Left Panel!

You've merged entire folder with all Excel files in just a few clicks.

Again, why not using this function if it is completely free?

Your database table was divided into several parts and split into worksheets within same Excel file?

xlCompare will join all of them into one sheet in a few simple clicks.

Select command Combine sheets into one on the Merge tab.

If you have left panel active - left workbook is merged. If you put your cursor into right panel, right file is merged.

xlCompare shows you a window where you need to select worksheet that receives data and other worksheets that should be merged into master worksheet.

This is all. Click Ok and get your file merged. After this you can remove all the worksheets you don't need.

As you see xlCompare can easily do all merge operations you have on the daily basis.

Yes, if you need to merge contents of one Excel file into other file and skip matching records, you need to use other commands, that we are going to describe in the topics below.

These functions are not free, as they require workbooks to be compared. But look how they work. They will move your productivity to the highest level!

One of the common daily tasks for Excel users is to combine several Excel worksheets into one. In this article we'll show you how Excel Compare utility does this in a few clicks only.

Let's imagine you have 3 lists with auto parts, saved in a form of Excel worksheets, received from your colleague. You need to combine them into single list for future processing.

If you have 3 small lists, you can just copy them manually and paste into one worksheet. But if you have 50 worksheets and 1000+ records in each list, this operation will take a time. If lists contain duplicate records - manual comparison is not a good solution - you can miss important information.

Errors in spreadsheets cost companies millions dollars. Do not lose your time and money on the mistakes in the workbooks you are using.

So, on this small artificial example we describe how xlCompare merges sheets and excel files into one copy.

xlCompare is fast and easy to use Excel merge tool. Just in several mouse clicks you have your merge job done. You will not spend you time learning software options for basic operations.

Open your worksheets to begin

In this example all lists with auto parts are on the different sheets in the same workbook. So, we just drop the workbook into Excel Compare application from Windows Explorer.

We are going to merge all parts listed on the Sheet2 and Sheet3 into worksheet Sheet1.

Excel Compare has Portrait and Landscape layouts. By default we are using Portrait, when worksheets are tiled vertically. Left worksheet contains MINE worksheet and right worksheet contains THEIR worksheet.

If sheet has many columns with data, it may be useful to change layout to Landscape, to have more data on the screen. So, we've changed layout to Landscape. All images you see on this page are from the Landscape layout.

Please, note: this feature is optional. We've described it just for your information. It is not used in the Merge process.

Now we go ahead to merge the worksheets.

Switch to Merge tab on the Ribbon.

Use Update Sheets command.

You receive Comparison Wizard window, where you can change the options used to compare worksheets and modify primary keys on the merged worksheets.

After you complete this wizard all new rows and edited cells are moved into Right panel.

There will be no comparison results and no difference explorer window. xlCompare gives you results silently and immediately. This command is for mass merge operation.

As you see, data from the Sheet2 was copied into Sheet1.

You can iterate this function for Sheet 3 and merge it's contents into the Sheet1.

Sheet2 and Sheet3 has rows above the list with auto parts. They are also present in the Sheet1, so they were not copied.

Only modified data was copied to the target worksheet.

Summary

Let's count how may clicks we need to merge several excel worksheets into one:

• One click to Open Merge Wizard
• One click to confirm Merge

So, in 2 clicks and few seconds we've combined 2 Excel worksheets into one.

What about duplicated rows. If one part is present in two worksheets? In this case xlCompare identifies same row and copies only modified cells. Row is not duplicated in the target worksheet.

How to identify inserted rows?

Good question. You want to know which record were added into my original list by xlCompare. In the example above we've got our target sheet filled with rows from other sheets, but it is too hard to distinguish them. In the large amount of rows it is almost impossible.

xlCompare has an option Mark Changes when Merging. In the Options | Merge tab. If it is disabled - all merged rows appear with source formatting.

Let's open this workbook again and to the same operation to merge 2 sheets into 1.

All inserted rows appear in green fill color. Background color identifies inserted rows within other rows in this worksheet.

How to change formatting on these rows. Select all rows filled with green color and change formatting on the Format tab on the Ribbon. It has interface very similar to Excel application. You can change:

• Fill Color
• Text Color
• Font Options
• Border Style
• Number Formatting
• Text Alignment

With the Format tab you can create the formatting you need.

Let's imagine another situation - you need to select data, which should be inserted into your worksheet.

In this case automatic merge command is not applicable.

We will merge these lists with auto parts manually.

Again, drop file into xlCompare window. Now we are using Portrait orientation, to have rows on the same level.

In the left panel we select MINE worksheet Sheet1. It will receive the data and on the right side - Sheet2. Rows from the Sheet2 will be copied into target sheet.

Use Compare Files button on the Main Window. It opens Comparison Wizard. In the Comparison Wizard we select pairs of worksheets to be compared.

This example is relatively simple - we have one pair of worksheets: (Sheet1, Sheet2).

Now press Compare button on the bottom of the Wizard to see color coded difference between worksheets.

This is Comparison Report. We will use it to merge cells between worksheets.

All unique rows are marked with colors:

• Green rows - unique rows in MINE worksheet
• Red rows - unique rows in THEIR worksheet

There are no duplicate rows in our lists, so all rows we have on the worksheets are marked with red and green fill color.

For example - we need only first row - Disk Brake for Toyota.

Click on the merge button (blue arrow icon) on the row headings. This moves row into another worksheet.

Merge icon in the header is changed to the UNDO icon. So, you can undo this merge operation in case of mistake.

This way takes more time, and requires some attention, but you have full control over the data you merge.

After you've finished merging - click on the Close Report button and exit from Comparison Report mode.

It is similar to merging several excel worksheets into one sheet. But in this example we merge two Excel files into one.

We've divided this list into 2 parts and will merge 2 workbooks.

Drop target file into left panel - it will receive new rows - result of the merge command. We drop other file on the right side - this is source for the new(inserted) records.

Use Update Files command on the Merge tab. It just moves data from one workbook into another one.

So, now in single click without selecting any comparison options we've got the results.

Part List 2.xlsx has being merged into Part List 1.xlsx.

Color formatting is optional. Turn option Mark Changes when Merging ON, to enable it.

xlCompare is powerful excel merge tool. Comparison Engine allows to implement all possible types of merge operations you need on the daily basis.

If we know all differences between worksheets - we can merge them in all ways.

Armed with this Excel merge tool you will greatly simplify your work with Excel worksheets, and make it more effective.

xlCompare makes complex work simple

This Excel diff tool is a good investment, that saves you time and money.