Compare cells in Excel files for matches and diffs

Common questions of the Excel users: How to compare values in two columns on the Excel sheet? Or how to compare two lists of data in Excel and pull matching values?

Use xlCompate to find difference between two Excel sheets

Compare two columns in Excel

Open your files in xlCompare

To start using the tool you need to open your files in the xlCompare. There are several commands you can use. You can use File | Open command on the Ribbon, or browse form file button on the main window. But we recommend to just drop your workbook into the xlCompare window from Windows Explorer. This is quick and easy way to get your files opened in the xlCompare application.
xlCompare immediately asks you to compare opened files. Our purpose is to compare two columns, so we just close this Comparison Wizard window and back to our worksheets.
Open Excel files in xlCompare

Define key columns (Optional)

To mark your column as key, please select column, do right click on the heading and use Key Column item in the right click menu.
Your column receives Key icon in the heading.
Define key column

Setup heading rows (Optional)

Usually, tables stored in Excel worksheets have heading rows. Heading row contains Field names for the table columns.
This row is highly important to getting correct and accurate results.
xlCompare tries to identify heading row and mark it with Key icon in your file. But at the any moment you can right click on the row heading and mark specific row as a header of your file.
Define heading row

Select columns to be compared (Optional)

Image, that in our case we would like to compare data in the Sales column for a specific partner company, taking into account marketing segment and country.
Right click on the worksheet and use Manage Columns command.
This window is a way to manage data in your worksheet and select what would you like to compare.
Manage Columns command in right click menu

Hide the unimportant data

We would like to have only a few columns on screen to be focused on the data.
Click on the Hidden word in the heading to hide all columns. Now we uncheck items we would like to have in the report.
We choose following columns in our example:
  • Segment
  • Country
  • Partner ID
  • Price
  • Sales
Hide columns in the Manage Columns window

Ignore columns you don't need

Note, that we would like to have Price column just for the information. It should not be compared with corresponding column.
Check the Ignored mark for this column.
Ignore column in the Manage Columns window

Get the results

We are ready to compare two columns for different values. Use Compare Sheets command on the Home tab on the Ribbon.
It brings you Comparison Wizard window.
Close it by using Compare button and go to the comparison report.
Comparison Wizard window

Working with comparison report

Now you have comparison report on your screen. All the rows are aligned one versus one. Corresponding values are exactly on the same places.
Values in column Price are shown with muted color. This means that column is ignored and all cells in this column are excluded from the comparison.
Comparison Report in xlCompare

Changed cells

All cells that was changed are highlighted with orange color in the report.
If you move your mouse over highlighted cell, xlCompare gives you detailed information about difference found in this cell.
For the numeric cells xlCompare displays difference between two values.
Changed cell in the Comparison Report window

Added and removed rows

Added row is a row from the left file, that doesn't have corresponding row in the right one. It is highlighted with green background color.
Similar to this, deleted row is a row from the right file, that doesn't have corresponding row in the left worksheet. xlCompare highlights it with red color.
You can change colors used to highlight changed cells and unique rows in the application options window.
Added and removed rows in the Comparison Report window

Save changed cells into third Excel file

To save only changed cells in the other file on disk, please do the following:
Apply Updated Cells filter
Use Save | Visible Rows command from the right click menu in the worksheet window
xlCompare asks you for a file name and saves data in this file on disk. Very easy method to extract the data you need from the comparison report.
Save visible rows

I have a question

Please, describe your question or inquiry in the form below. We will be happy to respond you!
Go to top