Find duplicates and unique rows in two sheets in Excel

One of the most popular searches in Google: How to compare two Excel sheets for matches. Or how to get unique rows between two Excel worksheets. In this tutorial we explain several methods on how to compare two Excel sheets and find duplicate rows. This task is relatively simple, and you should not be an Expert in Microsoft Excel to find duplicate rows between two Excel sheets.

Find duplicate rows between Excel sheets using formulas

If you just need to identify different and matching rows on two Excel sheets, you can use Excel for this. Excel itself is a powerful application. It is the best spreadsheet application.
In this tutorial we will use simple Excel file with SKU units. It contains two worksheets where:
  • column A represents SKU CODE
  • column B - MSRP PRICE
  • column C contains RETAIL PRICE
This is an abstract example, that shows you the technique. You can use the same method with any Excel file.
sample excel file
Our goal is to find rows between Sheet1 and Sheet2, which have the same SKU codes.
Sheet1 is a source worksheet, Sheet2 is a sheet where we are going to find the matching rows.

Use MATCH function to find the matching value in the source worksheet

Select the first data cell after the rightmost column on Sheet2. In our example this is cell D2. Columns A:C contains data, so we should use column D.
select cell D2 on the Sheet2
Our sheet contains one heading row, so we skip the heading rows, and select the second row.
If the data, you are comparing doesn't have a heading - just use the first data row (D1).
Enter following formula into cell D2:
enter formula into D2
It returns MATCH word in our example. This means that this SKU is present on the other worksheet. In case if this value is not present - formula returns empty value.
returned value in the D2 cell

Expand formula to the whole table

This is very easy. Just double click on the fill handle and Excel will expand this formula to the whole table:
fill handle in the D2 cell
As you see we have several matching values. Now we can start working with results.
formula expended to whole range of cells

Format matching rows with color

We use Conditional Formatting for this. You don't need to learn the conditional formatting feature. We will use a small part of its capabilities. Just repeat several steps we've described below.

Select Cells

Select entire worksheet Sheet2 by clicking on the left-top corner of the window.
select all cells on the Sheet2

Add New Rule

Use Conditional Formatting | New Rule command to add new conditional formatting rule to your worksheet.
New Rule command on the ribbon

Enter Formula

Select Use Formula to determine which cells to format option.
Use formula to determine which cell to format
Enter following formula
It will format the row that contains the MATCH string in the column D. That's why it is important to put dollar sign before D and omit it before 1.
Enter formula into conditional formatting rule

Select Formatting Options

In our example we are using a light green background for the matching rows.
You can select another appropriate formatting you like.
Select background formatting for Conditional Rule

Get the Results

After you've added the conditional formatting rule - you get matching rows on your worksheet formatted with background color.
This looks like a presentation, so you can save this file on disk with formatting.
Matching rows, formatted with background color

Filter matching rows on the worksheet

This is also relatively easy to do. Let's use AutoFilter option.

Select resulting column

This is the column that contains MATCH word. In our example this is column D. Click on the heading and get your column selected.
Selected column D on the Sheet2

Create Filter

Use menu Sort and Filter | Filter on the ribbon. This command creates a filter in column D.
Filter command on the Ribbon

Filter Matching Rows

Click on the filter button on column D and uncheck the blanks item in the Filter window. Click OK to close the Filter Window.
Uncheck blank items in the Filter Window
Now you have only matching rows on your screen.
Only matching rows on the worksheet

Filter Unique Rows

To filter unique rows (that are not present on Sheet1) uncheck the MATCH item on the Filter window and check the blanks.
Check MATCH items in the Filter Window
That's all. You have only unique rows in your table.
Only Unique rows on the worksheet

Delete Unique Rows on two sheets

After you've made previous step - select all visible rows on your worksheet and delete them with a right click Delete Row command.
Delete Row command in the right click menu
You've compared two worksheets and removed unique rows!
Restore all other rows by checking all items in the Filter window.
Now your table contains only matching rows.


As you see Excel is a powerful application that is the best in the spreadsheet business. By using several simple steps, you can filter your table from duplicate or mismatching records.
Features we've used:
  • MATCH, ISNA, IF worksheet functions
  • Conditional Formatting
  • Auto Filter

Use xlCompare tool to find matching rows between two sheets

xlCompare is a good alternative to Excel if you need to compare Excel files. It is a powerful and professional excel diff tool. We will show you the differences between results you are getting in Excel using standard method and xlCompare.

Compare two Excel sheets with xlCompare

Look into the following tutorial. You need to take just a few steps to the results.

Start xlCompare application

Usually, xlCompare shortcut is present on your desktop. So, you can start it from your desktop.

Drop your Excel file into xlCompare

The simplest method to open a file in xlCompare is to drop the file from Windows Explorer right into xlCompare.
So, just drop your file into the Left Panel in xlCompare and into the Right Panel in xlCompare.
Now you have Sheet1 on the left and Sheet2 on the right.
Excel sheets opened in the xlCompare window

Compare Excel Sheets

Click the Compare sheets button on the Compare tab on the ribbon.
Compare Sheets button on the Ribbon
xlCompare immediately gives you comparison results.
Unique rows on both worksheets are highlighted with fill color. xlCompare uses red color on the right sheet and green color on the left sheet.
Also, xlCompare compares values in all matching rows. This is a good benefit you are getting.
Comparison report window

Filter Matching Rows in two Excel sheets

xlCompare contains several filtering commands on the ribbon, you can use to filter matching and duplicate values on the sheets.
Apply Filter Duplicates command to get only matching rows on your screen.
Filter duplicates command on the Ribbon

Save Matching Rows on the other Excel sheet

After you've completed the previous step, use Save | Visible Rows command from the Right Click Menu. It will ask you to select Excel file for the resulting data.
Save only visible rows command in the context menu

Get rid of unique rows on two sheets in Excel

Use the Filter Unique Rows button on the xlCompare ribbon.
Filter Unique commands on the Ribbon
Now you have only unique rows on your screen. They are highlighted with colors.
Select all rows on the worksheet by clicking on the heading and delete them using the Delete command in the right click menu.
Delete command in the context menu
Just in a few clicks you've removed all unique rows between two Excel sheets.


It this tutorial we've explained you how to compare two Excel sheets for matching(duplicate) rows. If your task is simple and you need to filter matching rows - Excel itself is the best for this. Just enter formulas using the instructions above and get the results.
If your worksheet is large and you need to compare values in the matching rows or create a presentation report - xlCompare will do this work for you.

What is the difference between using Excel and xlCompare for comparing Excel tables?

Why should I use xlCompare tool if I can do my job in Excel application.

No formulas, No Conditional Formatting, No Filters

xlCompare transforms the comparison process into several simple clicks. You should not learn anything. You are getting the results without extra effort. This is the reason why you can consider using xlCompare.
xlCompare gives you more options to filter data and save it on disk. It creates presentation reports on the worksheet and in HTML. You can use it to present changes to your colleagues.

See Also

I have a question

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