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
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
I have a question
Please, describe your question or inquiry in the form below. We will be happy to respond you!
