Merge Several Excel Worksheets into One

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.

Main Window

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 utility. 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.

Portrait Layout Option

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.

Workbook in 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.

Press brown Merge button on the Main Window

Merge Button

It opens Merge Worksheets Wizard.

Merge Wizard

This is simple window where you should select the following:

  • Worksheet that receives data (result of the merge operation)
  • Worksheets to be copied into target sheet

We plan to merge Sheet2 and Sheet3 into Sheet1. So, on the bottom of the Wizard we select Sheet1 as a result of the Merge operation. And click on the check box on the header:

Check all items

It checks all items in list.

This means that checked worksheets (Sheet2 and Sheet3) will be merged into result worksheet.

That’s all we need.

Press Merge button on the bottom of the Wizard

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

Merged Lists

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

Contents of the Sheet2

Contents of the Sheet3

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 select all sheets to be merged
  • One click to confirm Merge

So, in 3 clicks and few seconds we’ve combined 3 Excel worksheets into one. In the same 3 clicks you can merge 50 worksheets with 10000+ rows on the each 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 Excel Compare. 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.

We enable option Mark Changes when Merging. In the Options | Merge tab. It was disabled, so all merged rows appear with source formatting.

Options: Mark Changes

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

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

Highlighted rows

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
  • and more ...

Select records to be merged

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 Excel Compare 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.

Main Window

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

Compare Button

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

Comparison Wizard

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

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

Undo Icon

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.

Close Report

Merge lists, which are in the different workbooks

It is similar to merging several excel worksheets into one sheet.

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.

Main Window with 2 files to be merged

In this case Merge button on the Main Window doesn’t show Merge Wizard. 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.

2 workbooks merged

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

Conclusion

Excel Compare 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 merge utility you will greatly simplify your work with Excel worksheets, and make it more effective.

Excel Compare is a good investment, which saves you time and money


Companies that trust our software

Volkswagen Logo
Siemens Logo
Zurich Insurance Logo
Xerox Logo
Oracle Logo
Electronic Arts Logo

We've got thousands satisfied customers

What do our customers say about xlCompare?

The best comparison software!

Proc: Extremely useful tool for database data comparison. Perfect visual representation of the comparison report.
Cons: Export difference report to more formats.
Summary: I need to compare database reports from my co-workers every day and this tool saves a lot of my time. It compares 2 large workbooks in several seconds. Comparison report is perfect. Data can be easily merged and saved directly in the report. Very useful tool.
Ralph Langley
Great product!

Proc: We use Excel extensively for our financial models, and this product greatly speeds up our productivity! Large workbooks are easily compared and merged. Drag-n-drop feature for merging workbooks is fantastic. I suggest it to everyone.
Cons: I like all features in this product.
Summary: We are going to have more people in the development on our Excel model, so merging is very helpful. xlCompare seems to be the best option, only it could handle our model files.
John Robert

Need Our Help?

If you have any questions regarding xlCompare, please contact us and we will give you a prompt response.

Spreadsheet Tools on Youtube

We've started publishing videos on the Youtube service, to show how to use xlCompare to compare Excel files for differences.

Ask, your questions directly in the comments, to share common questions with other users.

Please, visit out channel to get additional information about the product.