How to compare two Excel worksheets for differences
Let's say you have two Excel worksheets, or maybe two versions of the same worksheet, that you want to compare.
Or maybe you want to find potential problems, like manually-entered (instead of calculated) totals, or broken formulas.
This is one of the basic tasks if you are working with excel worksheets.
If you have relatively small excel worksheets, you can view worksheets side-by-side to find all differences. But, for example, if
you are working with Price Lists, each one have 200 rows and 20 columns - the most likely you will miss something.
There are a lot of articles on the Internet on how to compare 2 worksheets using VLOOKUP or other formulas. If you are doing this on
the daily basis - there is no sense to spend time for this - let professionals to do this job for you!
How xlCompare can help you in comparing Excel Worksheets
In xlCompare you should not spend time to arrange views side-by-side using Excel's commands. Layout is already oriented for comparison work.
You need only to open 2 excel worksheets, and they are immediately ready for being compared!
Before you start, you need to understand which data you have. If you are working with complex model with thousands of formulas - you need to compare
worksheets as usual Excel sheets with formulas. But if you have price list, customer list or accounting statement - you need to use
Database Comparison strategy.
Database comparison used very often. This type of Excel worksheet is used to interchange data tables. So, in our example we'll show this type
of excel worksheet comparison.
Every Database table should have Primary Key. In case of Excel worksheet, this could be one column with key values, or complex key which
consists of several columns. For example (First Name + Last Name).
xlCompare suggest you Primary Key columns for the worksheets you are working with, but you can change them using Manage Primary Keys
command on the Ribbon.
Compare two Excel worksheets
xlCompare is very simple and easy to use utility. Just open excel worksheets you want to compare and press Compare button on the main tab.
Understanding the results
In the side-by-side grid every sheet is compared with the corresponding one. xlCompare process all sheets in the workbooks.
Hidden sheets are included into comparison.
Comparison Results are reflected in the Difference Explorer window:
Every new record, which doesn't have corresponding match, or every updated cell is marked with color. This is
color added by xlCompare over existing color in your worksheet.
Original formatting is not changed!
- Added rows: green color
- Deleted rows: red color
- Updated cells: yellow color
Number if differences is reflected in the Ribbon and on the Sheets Tab.
Filter cells using comparison results
You can export comparison results to external file, for future use or to exchange with co-workers. Or you can apply filters
to perform various manipulations with data. Filter command in the Difference Explorer displays:
- Unique (Added and Deleted) rows
- Updated cells
- Updated and Equal cells
This screenshot displays unique (added and deleted rows).
Green Filter icon in the top-left corner of the worksheet header means that sheet has hidden rows.
xlCompare has wide range of the merging commands. You can merge records by clicking on the blue arrow button, or by checking appropriate
item in the Difference Explorer Window. Both of them are highlighted on the screenshot below:
When you click on the blue arrow record is added to the curresponding worksheet. This change is reflected in the
Difference Explorer - checkmark appears near corresponding item.
If you have large worksheet with thousands of inserted and deleted records manual merging will take a time. Use
Bulk Merge commands for this case.
Bulk merge commands allows you to merge tables in single click according to worksheet comparison results.
Highlighted commands in the Difference Explorer does the following:
- Create Union of two tables
- Create Intersection of two tables
- Create Difference between left and right worksheets
- Extract only unique rows
- Merge all records from the left book into the right
- Merge all records from the right book into the left
- Extract unique records into one worksheet and mark them with color
These commands are duplicated in the Right Click Menu:
As you see, xlCompare covers all possible Merge operations for Database Tables
Use these commands fo Filter Duplicate Records in the worksheet. xlCompare hides other records and
leave only duplicate records visible.
In the combination with commands like:
Remove Hidden Rows
Save only Visible Rows
which are available in the Right Click Menu, you receive powerful tool for data manipulation.
xlCompare is able to solve all tasks you encounter on the daily basis.
Database Tab on the Ribbon is a set of commands for database worksheets.
Filter by Primary Key command leave only rows with specific Key Values in the worksheets. All other rows are hidden.
You can quickly analyze clasters of the similar records in both worksheets by saving your time to filtering and sorting records.