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.
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.
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.
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.
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.
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
First screen is just an information window, which describes comparison algorithms used in xlCompare:
This screen is optional, you can check the box to hide it on the next run.
Next screen is 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.
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.
Double click on this item or Edit button below this list opens window where you can change the algorithm.
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.
Understanding the Results
xlCompare displays Comparison Results in two areas:
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.
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.
Unique Rows are marked with green background in MINE worksheet and red background on THEIR worksheet.
In this example you see semitransparent red row without number on MINE sheet and similar semitransparent green row on THEIR sheet.
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.
Difference Explorer is like a Comparison Summary
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:
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:
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.
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.
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 has an option to display difference instead of the corresponding value. With this option enabled cell contains value and difference with the corresponding one.
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:
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.
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:
For example, you can format with color only Unique Rows or only part of the Updated Cells.
As a conclusion, if you need to compare 2 excel sheets and highlight differences – you should use this command.
Filter Changed Cells
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
SUMMARY:
How many clicks do you need to compare two Excel files?
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.
If you have any questions regarding xlCompare, please contact us and we will give you a prompt response.
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
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.
Microsoft Windows
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.