Compare two columns for mismatching values

Imagine that you have two Excel sheets with data imported from another software into XLSX format. This could be your corporate solution where you keep clients, goods or other items that belong to your business.

In our example we have two Excel files with lists of laptops. Some items are equal, but other ones may be added or incorrectly entered. How to get the list of mismatching items and correct them.

Compare columns step-by-step

Use the instructions below to get your columns compared in a few clicks.

1. Open both Excel files in the xlCompare

Start the xlCompare and drop one file into the left panel and another file into the right panel.

Drop two XLSX files into xlCompare

2. Select Key Columns and Heading Rows

This step is optional, but if you are going to compare just 2 columns - probably you need it.

If your table has heading - select heading row and do the right mouse click on it. Click the Heading menu item in the context menu.

Select Heading Row

In our example we've selected first row as Heading.

To correctly identify missing values in your column(s) - mark it as key. Select the entire column and do the right click. Then click the Key command in the context menu.

Select Key Columns

In our worksheet we are going to compare SKU columns, so SKU column was marked as key.

When you set SKU as a key column xlCompare automatically marks this column as key on the corresponding panel.

Key columns and heading rows are highlighted with blue color in the xlCompare window.

For more information on Key Columns and Heading Rows visit this article - Select key columns and headings

3. Compare two worksheets

Compare Sheets command on the Ribbon

Click the Compare Sheets button on the ribbon. xlCompare compares data in both worksheets and presents output to you.

Report in xlCompare

4. Display only mismatching rows

Our goal is to find the mismatching items in SKU columns between both lists. Click the New Items command on the ribbon.

New Rows command on the Ribbon

This command leaves only mismatching items on the screen. All identical and changed rows are hidden.

New Rows are filtered on the worksheet panel

Now you can preview the results. All mismatching SKU items are on your screen.

5. Save mismatching items on disk

Right click on the worksheet and click the Save | Only Visible rows item in the context menu.

Save mismatching items on disk

It saves all visible rows in the new XLSX file. This is exactly what we need - save the mismatching items for future review or to email them to a colleague.

The Bottom Line

In just a few clicks xlCompare presents you with a color-coded report that contains only mismatching SKU items between two columns.

xlCompare gives you a solution right now in just a few clicks.

What differs xlCompare from AI and online tools that compare Excel files?

Privacy. xlCompare doesn't upload your files into the Internet. Your data never leaves your computer. No one will use your data for learning (like AI) or for other purposes. xlCompare is the enterprise solution you can use in the corporate environment.