How to compare two Excel sheets and highlight differences
- Introduction to Spreadsheet Comparison
- How to Start Comparing Excel Sheets for Differences
- Define Primary Keys
- Compare Two Excel Worksheets
- Using Comparison Wizard
- Understanding the comparison results
- What you can do with Comparison Report?
- Merge Excel Files
- Conclusion: Benefits of using Excel Compare Tool
- Bonus: Youtube videos
Introduction to Spreadsheet Comparison
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 data in two Excel sheets for differences. In our example this is 2 price lists.
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. Excel Compare 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.
There are other methods you can find by searching for how to compare two excel sheets for differences, or how to match data in excel from 2 worksheets, or compare excel sheets using VLOOKUP or VBA macro to compare Excel sheets.
But in this article we'll describe the most effecient and easy method - using Excel Compare tool.
xlCompare is extremely easy to use xlcomparator. You need only a few clicks to get your files compared.
How to Start Comparing Excel Sheets for Differences
To start - open your workbook in Excel Compare
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 Excel Compare 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 Excel Compare allows to compare data on the hidden worksheets.
Now you have Excel files opened in the xlCompare.
What is the next?
Define Primary Keys on Excel Worksheets
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 it will help to compare two Excel sheets and highlight differences between them.
Should the Primary Key be unique?
In general, in the database, Primary Key MUST be unique, but in the excel tables processed by Excel Compare 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
How to Compare Two Excel 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 Excel Compare:
- Compare by Primary Key – if your worksheet has Primary Keys – Excel Compare 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
Worksheets are compared one versus one, grouped into pairs. Excel Compare 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.
Excel Compare 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 Excel Compare 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 Excel Compare 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 for our task: compare two Excel sheets and highlight differences.
Understanding the comparison results
Excel Compare 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.
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 Excel Compare 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:
- 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. Excel Compare doesn’t mix Cells with VBA Macros and Form Controls. Results we bring to you must be clear and structured.
How does Excel Compare 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 Excel Compare. 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, Excel Compare 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 Excel Compare will give you:
- Original Formula and Value
- Corresponding Formula and Value
- Difference between values
- What was changed in cell formatting options
What you can do with Comparison Report
Print Report with Highlighted Differences
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.
Highlight differences with color
Color formatting you see is virtual. It is not applied to cells in the workbook. Excel Compare 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.
As a conclusion, if you need to compare 2 excel sheets and highlight differences – you should use this command.
Filter Updated 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 Excel Compare 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.
Merge Excel Files
Merge cells is an important part of the Excel Compare – 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 Excel Compare 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. Excel Compare 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?
Merge two Excel worksheets
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.
Excel Compare 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.
CONCLUSION:
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 Excel Compare 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.
Excel Compare will save you a lot of time
It will do your spreadsheet comparison and merging work in a seconds.
With Excel Compare 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 Excel Compare 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.