Compare Excel Files with xlCompare

We would like to describe commands and options, you can use, in our Excel file comparison tool on one of the most common examples – compare two price lists in Excel for differences.

Excel worksheets are widely used to store table data like price lists, accounting statements, logs, etc. and complex calculation models, so almost every Excel user needs to know how to compare two Excel sheets and find the difference.

After we show you Excel spreadsheet comparison options, we put your attention on how to merge excel files.

Imagine you have 2 files you would like to compare. xlCompare treats one version of the file as MINE copy and another one is THEIR copy. These names are just for the User Interface, you can treat them as modified and original file.

MINE copy is on the left side and marked with green MINE label, THEIR copy is on the right side and also marked with red label THEIR.

Mine the Their labels

xlCompare is extremely easy to use excel file comparator. You need only a few clicks to get your files compared.

To start - open your workbook in xlCompare

This application is designed for the comparison operations, so Main Application Window is a bit different, then in Microsoft Excel Application. It is defined into 2 panels to display data to be compared side-by-side. This layout simplifies work with the comparison results.

To open Excel workbook for comparison, just drop it down into xlCompare window from the Windows Explorer.

When workbook is dropped into left panel – it became MINE workbook, when you drop it to the right – it became THEIR excel file.

Opened workbook looks exactly as in Excel. The only difference – hidden sheets became visible. This is important, because xlCompare allows to compare data on the hidden worksheets.

Mine Workbook opened

Now you have Excel files opened in the xlCompare.
What is the next?

In the top left corner on each panel yellow Key Icon appears. This means that xlCompare has identified your data as database table and suggests you a Primary Key row and Primary Key columns.

Primary Key Icon

Note: Primary Keys is an optional part, but we would like to describe it in this article.

Why do we need a Primary Key?

If you have database data – most of the excel files tables are extracted from the databases, so they keep original structure where every record is identified with key. In most cases, there is no sense to compare rows with different Primary Keys. Because they refer to different entities, which doesn’t correspond each other.

That’s why we suggest you to define Primary Key Columns and Rows on your worksheets, if you have database tables.

Should the Primary Key be unique?

In general, in the database, Primary Key MUST be unique, but in the excel tables processed by xlCompare DUPLICATES ARE ALLOWED. For example, you may compare several records which contains transactions for the same customer.

How to clear Primary Keys?

Right click anywhere in the worksheet and select menu item Clear Primary Keys. In one click all the Primary Keys are removed from sheet.

Clear Primary Keys

How to quickly setup Primary Keys?

This operation is as easy as clear keys. Select columns you would like to set as Key columns and do right click on the selected area. Use Set As Primary Key menu command. If your worksheet has complex key which consists of several columns – select all of them and apply this command to selection.

Set Primary Key

If your table has header, use same way to setup Primary Key Rows. In our example row 1 is a key row, it contains field names. In general, Primary Key Row should not be a first row in the worksheet. Any row could be set as primary.

Now we are ready to compare the worksheets

Click on the red Compare button on the main application window

Compare Button

First screen is just an information window, which describes comparison algorithms used in xlCompare:

Startup Screen

  • Compare by Primary Key – if your worksheet has Primary Keys – xlCompare will suggest you to use this option.
  • Compare as Worksheet - the most suitable for accounting statements and financials models. Use this option if your worksheet doesn’t have keys.

This screen is optional, you can check the box to hide it on the next run.

Next screen is Comparison Wizard

Comparison Wizard

Worksheets are compared one versus one, grouped into pairs. xlCompare needs to know which pairs of sheets you are going to compare. Comparison Wizard gives you a control on this process. Here you need to define data to be compared.

xlCompare groups worksheets with same names into pairs and give you a preliminary list of sheets to be compared.

Wizard is divided into 3 parts – Worksheets, VBA Modules and VBA Forms. Every tab display list of the grouped items.

Checked items will be included into comparison, unchecked ones – excluded.

You can change pairs and re-group the worksheets and VBA Modules and Forms in the Wizard Tabs.

In our example there is only 1 worksheet named Laptops and 2 Visual Basic Modules – Sheet1 and ThisWorkbook. We left this pair checked.

Our worksheet has only pricing records, and doesn’t have Visual Basic Code, so we can ignore VBA items.

We select VBA Modules tab and use Exclude All button. This tells xlCompare to excludes all VBA Module items. They immediately appear unchecked in the list.

Comparison Wizard - Exclude All

On the Worksheets tab Laptops item is checked and has Key icon. This means that xlCompare will use Compare by Primary Key algorithm for this sheet.

Comparison Wizard - Key Icon

Double click on this item or Edit button below this list opens window where you can change the algorithm.

Comparison Wizard - Key Icon

In our case initial selection is right and should not be changed.

So, we go ahead to the comparison report

After clicking the Compare button you get the results.

Comparison Wizard - Compare Button

Understanding the Results

Comparison Results

xlCompare displays Comparison Results in two areas:

  • Color Coded Report in the Worksheet View
  • Difference Explorer window below the worksheets

Color Coded Report

Color Coded Report displays changed 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.

Color Coded Report

Difference Explorer uses another approach to represent the differences. This is just a list of differences, grouped into the hierarchical tree form.

Every Changed Cell receives orange background and orange border.

Changed Cells

Unique Rows are marked with green background in MINE worksheet and red background on THEIR worksheet.

Unique Rows

In this example you see semitransparent red row without number on MINE sheet and similar semitransparent green row on THEIR sheet.

Virtual Unique Rows

Why there are no numbers on these rows? Because they are virtual. They are not present in this workbook and inserted by xlCompare in order to align equal rows – display them on the same places, so equal cells are always on the same line.

This option is called Align Worksheets

It can be disabled in the options. It is turned ON by default, and gives native representation for the comparison results.

With this option enabled you can turn OFF one of the panels and browse color coded report in one worksheet. If you have many data columns in your worksheet, hiding one of the panels make much more data visible on the screen.

One Worksheet View

Difference Explorer is like a Comparison Summary

Difference Explorer

In our Example it says that we have 2 unique rows, 1 in every worksheet, and 3 updated cells. First column, named Range, contains address of every changed cell. Other columns have cell values and difference between changed numerical cells. Last columns with icons describe what was changed in cell:

  • Value
  • Formula
  • Background Color
  • Border Style and Color
  • Font
  • Number Formatting
  • Text Alignment
  • Text Color

These values depend on the options selected. You can select what to compare in the Options and ignore formulas or cell formatting to reduce comparison noise.

All data is grouped by type in the Difference Explorer List: Unique Rows, Unique Columns and Updated Cells have separate branches in this tree.

Difference Explorer has 3 tabs:

  • Sheets
  • VBA Modules
  • VBA Forms

Comparison Wizard separates compared data – you select what would you like to compare: sheets, VBA code, VBA Forms and Controls. And the same grouping we have in Comparison Results in the Difference Explorer. They are also separated by their category. xlCompare doesn’t mix Cells with VBA Macros and Form Controls. Results we bring to you must be clear and structured.

How does xlCompare reflect differences?

On the vertical and horizontal scrollbars there are colored marks – orange, red and green. They point you to the position of the changed cells and unique rows.

Scrollbar marks

In this example scrollbar reflects that changed cells (orange) and unique rows (green) are visible on the current page.

Every sheet on the Worksheet Tab and in the Difference Explorer have count of differences, found by xlCompare. Here we have 5 differences – 2 unique rows and 3 updated cells.

Sheet Names

Difference Explorer displays count of found changes in the Tabs name. In this example we have 5 differences, so first tab is named Sheets (5). Next 2 tabs are VBA Modules and VBA Forms. This means that there are no differences found between VBA Code and Form Controls in the compared workbooks. This is right, because we’ve excluded all VBA specific items in the Comparison Wizard.

Extended View Mode

In the highlighted changed cell you see it’s value and the corresponding one. This option is named Extended View. With this option turned ON, xlCompare displays both values in the same place. You should not look for the corresponding cell on the other part of the sheet. Changed value is there. Arrow icon identifies increase or decrease of the numeric value. If value was increased in comparison with the corresponding one – cell is marked with green up arrow. In other case – cell gets red down arrow.

Extended View

Extended View has an option to display difference instead of the corresponding value. With this option enabled cell contains value and difference with the corresponding one.

Display Difference

Tooltip with Difference Details

To get detailed information for the modification – use tooltips. Put mouse cursor over the changed cell and xlCompare will give you:

  • Original Formula and Value
  • Corresponding Formula and Value
  • Difference between values
  • What was changed in cell formatting options

Tooltip

What you can do with Comparison Report

Print Report with colored cells

Excel workbook it not able to contain 2 values in one cell, so if you would like to have a copy of the report in Extended View mode – you need to save it as PDF. Export button opens Print Preview window, where you can change printing options and save the printout into PDF file on disk.

Print to PDF

Mark changes with color

Color formatting you see is virtual. It is not applied to cells in the workbook. xlCompare just overrides original formatting to highlight changed cells and unique rows. To apply formatting to cells in the workbook permanently, use Mark Changes command. This command opens wizard where you can select what would you like to format:

  • Both worksheets or only one
  • Entire sheet, or just a brunch selected in the Difference Explorer

For example, you can format with color only Unique Rows or only part of the Updated Cells.

Mark Changes

As a conclusion, if you need to compare 2 excel sheets and highlight differences – you should use this command.

Filter Changed Cells

Filter Changes

If you have a large worksheet, possible you would like to get all changes on the screen and hide other data. For this purpose xlCompare has Filter commands which show cells only by specific criteria:

  • Only Equal Cells
  • Only Changed Cells
  • Only Unique Rows

In case if you want to see only unique rows – apply this filter and you will have them on screen. Now you can copy this data to another worksheet, save it on disk, print as PDF, delete from the worksheet, etc.

Filter Updated Cells

Merge changes between workbooks

Merge cells is an important part of the xlCompare – this tool is for comparing and merging Excel worksheets. As you see in the Comparison Report, every changed cell has arrow icon. This is Merge Cell command.

Merge Cell Command

Put your cursor over this arrow and xlCompare will give you a tooltip with description of the operation, done by this button. It copies this cell into corresponding worksheet.

After being merged, cell receives other color formatting and corresponding value disappears, because now both cells contain same value, formula and formatting.

Merged Cell

Like changed cells, Unique Rows and Columns also have Merge Arrow on the header. Select rows you would like to merge and use this arrow button. xlCompare will move all these rows into another worksheet.

Merge Unique Row

So, you can select what you want to copy to another worksheet, and move this data just in a few clicks.

Remember, after every merge operation you CAN easily UNDO every merge action and restore cell value, formula and formatting.

Difference Explorer window is another way to merge worksheet cells

Every entry in its list has checkbox. Once checked, this value is copied into the corresponding worksheet. So, in the Difference Explorer you can browse all changes, found between worksheets, and merge when one by one.

Merged Item in Difference Explorer

What if you want to move all changed cells to another worksheet at once?

Bulk Merge commands

This type of merge command process all found modifications in the worksheets. Brown Merge button on the Main Window moves all changes made in the MINE worksheet into THEIR file. Just click this button and get your worksheets merged in a second.

Bulk Merge button

Difference Explorer also has Bulk Merge button

It has more options to merge data. You can copy cells from MINE sheet to THEIR and from THEIR to MINE. Also, in this window previous merge operation can be undone. In one click you restore previous state of your worksheet.

Bulk Merge command in Difference Explorer

In the comparison report mode Compare button the Main Window changes its name to Close Report. Once we’ve finished working with comparison report, click this button to exit to workbook view again.

Close Report button

xlCompare removes color formatting and every cell has its original color and font options. All merged cell values and formulas and inserted unique rows are left in the worksheets.

To save merged excel file on disk use Save and Save As commands. These commands are near file selection combo box, and they are duplicated in the File menu on the Ribbon.

Save Command

SUMMARY:

How many clicks do you need to compare two Excel files?

  • click on the Compare button
  • click in the Comparison Wizard

In just a 2 clicks you get your job done!

We don’t count welcome screen before Comparison Wizard, because it is optional and can be disabled. And excluding VBA modules is not actually needed. We’ve made this to show you how to exclude entries in the Comparison Wizard in one click.

This is not a full set of xlCompare commands and options

We’ve described only part of them by comparing 2 price lists, saved in the Excel files (XLSX). Product has commands to highlight and remove duplicate rows and duplicate records. To drill-down trace cell formulas and evaluate calculations. Convert text values to numbers, trim data, combine columns, etc.

xlCompare will save you a lot of time

It will do your spreadsheet comparison and merging work in a seconds.

With xlCompare is not only a tool to compare two Excel files, but also to highlight differences, create comparison summary reports and merge excel files into one.

In this article we’ve described how to compare 2 Excel files – the most simple use case. But xlCompare may also be used as Excel Diff Tool for any version control system, like SVN and GIT. Command line options allows easy integration into third-party application as Excel Diff Viewer.

Companies who trust our software

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

Need Our Help?

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

Order xlCompare

Standard
Personal Use
$99.99 $69.99
  • Compare and Merge Sheets
  • Compare and Merge VB Projects
  •  
  •  
  •  
  •  
  •  
  •  
  • Free Updates
  • 1 User
Order Now
Professional
Business Use
$149.99 $99.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
$449.99 $299.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 you 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.

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

Build Date: 4/14/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.