How to Compare Two Excel Sheets for Duplicates (4 Quick Ways)

Conditional formatting is used to compare two excel sheets and quickly highlight the duplicates. To meet certain criteria, this powerful feature of Excel helps to apply formatting to a cell. But this formatting will work if the two sheets are in the same excel workbook. Otherwise, we cannot use it.

Table of Contents hide

Download Practice Workbook

4 Quick Ways to Compare Two Excel Sheets for Duplicates

Compare from One Workbook with Two Different Sheets by Viewing Them Side by Side

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

Search for More Duplicate Values on The Other Sheet and Highlight

Using VLOOKUP In Multiple Worksheets

Conclusion

Download Practice Workbook

Here is the practice workbook.

Compare Two Sheets for Duplicates.xlsx

4 Quick Ways to Compare Two Excel Sheets for Duplicates

Compare from One Workbook with Two Different Sheets by Viewing Them Side by Side

Let’s consider we have an Excel workbook with two sheets. Here we are going to compare them viewing side by side.

Here is the Sheet1.

And here is the Sheet2.

To view them side by side let’s-

Open the workbook and tap View. Click New Window. The same workbook will open in two windows.

Now again tap View. Click Arrange All and select Vertical and click Ok.

The sheets will appear side by side and we can start.

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

Let’s consider we have two sheets and now we are going to use Conditional Formatting to find the duplicate values.

STEP 1:

At first, select the data we have in sheet 1.
Then go to the Home tab and tap Conditional Formatting.
Now select New Rule.

STEP 2:

In the New Formatting Rule select the pointed rule type.
Now in the rule description box use COUNTIF Function,

=COUNTIF(Sheet2!$C$5:$C$11,C5)

♦ NOTE: This function has two criteria. For the range, go to the second sheet. Here select all the data from where we are looking at and press F4 to make it absolute. Now put a comma and specify the criteria. For that, we will go to the first sheet and select the cell.

Click Format.

STEP 3:

In the Format section choose the Fill color and click OK.

Again click OK.

Now the final result is here and we can see the duplicated values are highlighted.

Here Sheet1,

and Sheet2,

Search for More Duplicate Values on The Other Sheet and Highlight

If there are more than two duplicates on the other sheet, we can highlight them. For that,

STEP 1:

At first, select a cell and go to the Home tab.
Click on the Conditional Formatting and select Manage Rule.

STEP 2:

Select the Rule bar and click Duplicate Rule.

A new Rule bar appeared. Select it and hit Edit Rule.

Now add ‘>1’ with the formula.
From the Format, choose the Fill color and click OK.

Again click OK. The more duplicate values on the other sheet are highlighted.

Using VLOOKUP In Multiple Worksheets

We can use the VLOOKUP formula to find the matches in different worksheets. Let’s say we have two worksheets. We are going to find the exact matches in the second sheet and pull out the required information to show it in the first one. Here is the Sheet3,

and the Sheet4,

Select the cell
Write the formula:

=VLOOKUP(B5,Sheet4!B5:C10,2,FALSE)

Hit Enter.

The required output is shown in Sheet 1.
Now drop down the cursor to see the next values.
Here #N/A Error shows as there is no match found.

To avoid this error, we use IFERROR Function.
Select the cell and write the formula:

=IFERROR(VLOOKUP(B5,Sheet4!B5:C10,2,FALSE),"Not Available")

Hit Enter and drop down the cursor.
Personalized words will be displayed if there is no match found in Sheet 2.

Conclusion

By using these methods, one can easily compare two excel sheets for duplicate values. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.