How to compare 2 columns in Excel files

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?
Compare two columns in Excel

Download xlCompare for Windows

Download and install latest up-to-date version of desktop xlCompare tool
The download is a full-featured version
download Download xlCompare 12.02
Build Date: July 30, 2025
Join the list of xlCompare users
More than 300,000 downloads

Find matching values between two columns in the Excel tables

xlCompare is a Spreadsheet Compare tool you can use to find matching and different cells in Excel spreadsheets, compare XML, CSV and TEXT files. In this article we will show you how to use xlCompare to compare columns in Excel tables.

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
PLANS & PRICING

Choose the plan that best fits your needs

You are licensed to use this software for evaluation purposes without charge for a period of 3 days. If you use this software after the evaluation period, a registration fee is required.
Monthly
$9.99 / 1 month
ORDER
Yearly
$49.99 / 1 year
ORDER
Lifetime
$99.99 / forever
ORDER
IMPORTANT Monthly and Yearly licenses are not a subscriptions. There is no automatic renewal of your license. Pay only when you need the software.

I have a question

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