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.

Primary Keys

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.
Compare Database Tables

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:
Difference Explorer
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).
Filter Unique Rows
Green Filter icon in the top-left corner of the worksheet header means that sheet has hidden rows.

Merge Tables

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:
Manual Merge Commands
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.

Bulk Merge Commands

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.
Bulk Merge Commands
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:
Bulk Merge Menu

As you see, xlCompare covers all possible Merge operations for Database Tables

Filter Duplicate Rows

Filter Duplicates
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.

Filter by Primary Key

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

Download xlCompare

Pick the version suitable for your Windows. A free trial is available to help you with the decision-making.

Current Version: 9.3.11

Build Date: 4/27/2021


Version History

During 10+ years our team is working to provide you a robust and quality software. It boosts your productivity and saves you time and money. Just look into our list of changes on the page below to imagine amount of work done on the product.

Supported Versions

Microsoft Windows

  • Windows 10
  • Windows 8.1
  • Windows 8
  • Windows 7

64-bit and 32-bit versions are supported

Microsoft Excel

xlCompare is based on the Spreadsheet Core engine.

It doesn't require Microsoft Excel to be installed.

Questions & Answers


Which version to choose - 64-bit or 32-bit?
If you have 64-bit Windows - install 64-bit version of the xlCompare. It allows to open larger workbooks and use full power of your 64-bit processor.
How does the 30-day trial work?
Once you decide to use xlCompare, you can run a 30-day free trial first. Including all the product features, it will help you decide which features are of the greatest value to you. Once the 30 day period is over, you will be asked to make your choice.
Are there any special requirements?
No, xlCompare is a completely independent software - it doesn't have any special requirements. You can install it on the any Windows PC. Just download, install and use.
Do you offer discounts for students?
We offer student and non-profit discounts. Just open a private discussion with us, verify your status and we will provide you with a 25% discount on the regular price. We may ask you to confirm your status with documentation, so please get ready to verify that.

Order xlCompare

Standard
Personal Use
$49.99 $34.99
  • Compare and Merge Sheets
  • Compare and Merge VB Projects
  •  
  •  
  •  
  •  
  •  
  •  
  • Free Updates
  • 1 User
Order Now
Professional
Business Use
$89.99 $64.99
  • Compare and Merge Sheets
  • Compare and Merge VB Projects
  • Command Line Mode
  • SVN\GIT Integration
  • Bulk Merge Commands
  • VBA Automation
  • Trace and Debug Calculations
  • License Never Expires
  • Free Updates
  • 1 User
Order Now
Professional, 5
Business Use
269.99 $189.99
  • Compare and Merge Sheets
  • Compare and Merge VB Projects
  • Command Line Mode
  • SVN\GIT Integration
  • Bulk Merge Commands
  • VBA Automation
  • Trace and Debug Calculations
  • License Never Expires
  • Free Updates
  • 5 Users
Order Now
* -30% discount is valid until April 30
* Standard license is for personal use only. You can't use it for your business
* All pricing packages include FREE Technical Support and new features and hotfixes by request.

Questions & Answers


Which payment methods are supported?
Orders are processed by our e-commerce partner, MyCommerce (Share*It) (part of the Digital River), through a secure SSL connection. It supports rich set of payment methods: All major Credit Cards, PayPal, Bank/Wire Transfer, ...
Is this a one-time payment?
Yes, there will be no future payments. xlCompare is sold as Permanent License, not like a subscription. You pay once and get a lifetime license.
Do you have volume discounts?
Yes. If you are ordering 3 and more licenses, please contact us for a Coupon Code. We'll provide you a discount.
Do you collect my Credit Card information?
No. Spreadsheet Tools and Share-It do not collect your Credit Card data.
Should I order a license for my new PC?
No. If you've got new PC - you can transfer your license to this new computer. You can do it yourself in your account in the Customer Area, or send us an e-mail and we'll do it for you.
How many activations do I have?
One xlCompare license allows you to use the software on one PC. If you plan to use xlCompare on more then 1 computer - you need a license for the every PC.
Is technical support included?
Yes. Free updates and unlimited technical support is included into your license. If you have any questions - contact us to get a prompt response.
Do you work with resellers?
Yes, sure. Please contact us for getting additional information.

Need Our Help?

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

F.A.Q. Options and Commands


What is Extended View?

This is an important feature, which displays cell value(formula) and corresponding value in same cell. With this option turned ON (default selection) you should not search for the corresponding value on the other panel. It greatly saves your time and increase usability of the Comparison Report.

I need to see Difference between changed values.

Option Display Difference activates feature you need in the Extended View mode. With this option enabled, you see difference between value in MINE cell and value in the THEIR cell. Colored Arrow mark indicates increase or decrease in the value.

Can I save report I see in the Extended View?

Yes, sure. You can export this report into PDF format for future use. For example, you can print it or send to your co-worker.

Does xlCompare find differences in formulas?

Yes. xlCompare finds differences in the text of formula and text of array formula. However, if you don't need to compare formulas, you can exclude this in the options. xlCompare gives you rich set of options to control what you are searching for. You can compare:

  • All worksheet data
  • Only constant cells
  • Only calculated cells
  • Only text of formula
  • Only value of formula
  • Combination of the options above

How to identify found differences?

All differences are listed in the Difference Explorer report. When you select difference in this list - xlCompare activate it in the Worksheet\VBA Panel.

All unique rows and changed cells are marked with color - background, text color and border. Formatting in the equal cells is not changed.

Can I ignore hidden rows and columns?

Yes.xlCompare has an option to exclude hidden rows and columns from comparison. If your worksheet has data, you want to exclude, - hide it and run the comparison. This is one of the methods to reduce comparison noise.

How to save Comparison Report to present it to colleagues?

There are two options. You can print Comparison Report exactly as it looks with all formatting into the PDF format. Use Export button, with PDF icon, in the Difference Explorer.

Export PDF Report

It opens Print Preview window, where you can preview your report, change printing options – put all columns on one page, align printout, etc. and save it on disk.

This report perfectly presents all differences, found between Excel Worksheets.

PDF Report

Other option – Summary Report button in the Difference Explorer.

Summary Report

This command generates outlined report, which contains all items you have in the Difference Explorer. It doesn’t display modifications in-place, like a previous PDF report, but it gives you a complete summary of the comparison done.

Choose one of the following formats to save this report:

  • XLSX
  • HTML
  • Text

Or just copy it to clipboard and paste into e-mail or other application.