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.


Download Practice Workbook

Here is the practice workbook.


4 Quick Ways to Compare Two Excel Sheets for Duplicates

  1. 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.

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

And here is the Sheet2.

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

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.

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

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

 

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

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

  1. 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.

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

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.

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

STEP 3:

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

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

 

  • Again click OK.

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

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

Here Sheet1,

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting

and Sheet2,

Compare Two Sheets for Duplicates and Highlight Data with Conditional Formatting


  1. 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.

Search for More Duplicate Values on The Other Sheet and Highlight

STEP 2:

  • Select the Rule bar and click Duplicate Rule.

Search for More Duplicate Values on The Other Sheet and Highlight

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

Search for More Duplicate Values on The Other Sheet and Highlight

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

Search for More Duplicate Values on The Other Sheet and Highlight

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

Search for More Duplicate Values on The Other Sheet and Highlight


  1. 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,

Using VLOOKUP In Multiple Worksheets

and the Sheet4,

Using VLOOKUP In Multiple Worksheets

  • Select the cell
  • Write the formula:
=VLOOKUP(B5,Sheet4!B5:C10,2,FALSE)
  • Hit Enter.

Using VLOOKUP In Multiple Worksheets

  • 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.

Using VLOOKUP In Multiple Worksheets

  • 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")

Using VLOOKUP In Multiple Worksheets

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

Using VLOOKUP In Multiple Worksheets


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.


Related Readings

Excel Formula to Find Duplicates in One Column

VLOOKUP to Return Multiple Columns in Excel (4 Examples)

How to use IFERROR Function in Excel (3 Examples)

How to Remove Duplicates Based on Criteria in Excel (4 Methods)

Excel Conditional Formatting Dates Older than Today (3 Simple Ways)

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. Here I will post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo