Here we show you how to compare 2 excel files and merge some cells
In this video, we show you how to compare Excel files for differences using Excel Compare
Imagine you have 2 files you would like to compare. Excel Compare treats one version of the file as "FILE 1 copy and another one is FILE 2 copy.
The FILE 1 copy is on the left side and marked with a green FILE 1 label, the FILE 2 copy is on the right side and also marked with red label FILE 2.
To open the Excel workbook for comparison, just drop it down into Excel Compare window from Windows Explorer.
When the workbook is dropped into the left panel - it becomes the "FILE 1" workbook. When you drop it to the right - it becomes a "FILE 2" excel file.
In the top left corner on each panel, a yellow Key Icon appears.
This means that Excel Compare has identified your data as a database table and offers you a Primary Key row and Primary Key columns.
We suggest you define Primary Key Columns and Rows on your worksheets, if you have database tables.
Excel Compare doesn't require Primary Keys to be unique. You can compare files with the duplicate records.
To change Primary Key columns use the Right Click menu:
Now, we're ready to compare the worksheets.
Click on the red Compare button on the main application window.
This is the Comparison Wizard.
Worksheets are compared one versus one, grouped into pairs. Excel Compares group's worksheets with the same names into pairs and gives you a preliminary list of sheets to be compared.
Checked items are included in comparison, unchecked ones - are excluded.
Change the pairs and re-group the worksheets, VBA Modules, and Forms with Add and Delete buttons.
In our example there is only 1 worksheet named Laptops, and 2 Visual Basic Modules - Sheet1 and This Workbook.
We left this pair checked.
Our worksheet has only pricing records, and doesn't have Visual Basic Code, so we can ignore VBA items. Select the VBA Modules tab and click the Exclude All button.
This tells Excel Compare to exclude all VBA Module items. They appear unchecked in the list.
On the Worksheets tab, the Laptops item is checked and has Key icon.
This means that Excel Compare uses Compare by Primary Key algorithm for this sheet. Double click on this item to change the algorithm. In our case, the initial selection is right and should not be changed.
Click the Compare button to get the results.
xlCompare displays the Comparison Results in two areas.
The Color Coded Report in the Worksheet View displays changes the data in-place, directly on the worksheet. Every modified cell, row or column is marked with color, so you can easily identify them among other cells.
This window is called Difference Explorer. It contains a structured list of differences, grouped by type.
Inserted and deleted rows, columns and changed cells have separate branches in this list.
In our Example it says that we have 2 unique rows, 1 in every worksheet, and 3 changed cells.
So, just in a few clicks you get the comparison report.
Every Changed Cell receives orange background and orange border.
Inserted Rows are marked with green background in the FILE 1 worksheet. Removed - with red background on FILE 2 worksheet.
Use the tooltips to get detailed information for the modification. Put your cursor over the changed cell, and Excel Compare will give you:
Here's what you can do with Comparison Report
Mark changes, command highlights all modifications with color.
Create a difference report for the compared worksheets
Your Report can be saved as an Excel File, PDF or HTML document or copy it to the clipboard.
Print the Report with colored cells in the PDF format.
In this Print Preview window, you can change the printing options and save the file in a PDF format on the disk.
Excel Compare is a quick and easy to use utility. It will do your spreadsheet comparison and merging work in seconds.