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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to compare two Excel sheets for duplicates, you have come to the right place. Here, we will walk you through 5 easy and effective methods to do the task smoothly.


Download Practice Workbook

You can download the Excel file and practice while reading this article.


5 Quick Ways to Compare Two Excel Sheets for Duplicates

In the following article, we will describe 5 quick and effective methods to compare two Excel sheets for duplicates. Here, we used Excel 365. You can use any available Excel version.


1. View Duplicates in Two Sheets Side by Side

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

Here the first sheet is Side by Side 1.

View Side by Side to Compare Two Excel Sheets for Duplicates

And the second sheet is Side by Side 2.

Here, we have duplicates in the two sheets. Next, we will find these duplicates by viewing them side by side.

Steps:

  • First of all, open the workbook, go to the View tab>> from Window >> click New Window.
  • Therefore, the same workbook will open in two windows.

  • After that, again go to the View tab >> from Windows >> select Arrange All.

  • At this moment, an Arrange Windows dialog box will appear with several options.
  • Then, select Vertical >> click OK.

Use of Vertical Arange Windows to Compare Two Excel Sheets for Duplicates

  • Hence, you can see two sheets side by side.
  • Now, you can compare two Excel sheets duplicates.


2. Use Conditional Formatting

In this method, we will use the Conditional Formatting tool to compare two Excel sheets duplicates.

Here the first sheet is Conditional Formatting 1.

And the second sheet is Conditional Formatting 2.

Here, we have duplicates in the two sheets. Next, we will find these duplicates by using Conditional Formatting.

Steps:

  • First of all, select the cells you want to apply Conditional Formatting.
  • Here, we select cells C5:C11.
  • After that, go to the Home tab >> select Conditional Formatting.
  • Then, select New Rule.

Use of Conditional Formatting to Compare Two Excel Sheets for Duplicates

  • At this point, a New Formatting Rule dialog box will appear.
  • Afterward, we will select Use a formula to determine which cells to format option.
  • Along with that, we will type the following formula in the Format values where this formula is true box.
=COUNTIF('Conditional Formatting 2'!$C$5:$C$11,C5

This formula includes the COUNTIF Function. Here, the COUNTIF function has two criteria. For the range, go to the second sheet. Here select all the data from where we are looking 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.

  • Moreover, click Format.

Applying Formula to Compare Two Excel Sheets for Duplicates

  • Then, a Format Cells dialog box will appear.
  • Furthermore, from Fill >> select a color.
  • Here, we choose Pink color, you can choose any color you want.
  • Therefore, you can see the Sample of the color.
  • Moreover, click OK.

  • Along with that, you can see the Preview color in the New Formatting Rule dialog box.
  • At this point, click OK.

  • Now the final result is here and we can see the duplicate values are highlighted with Pink color in the Conditional Formatting 1 sheet.


3. Use of Manage Rule

If there are more than two duplicates on the other sheet, we can highlight them using the Manage Rule of Conditional Formatting.

Steps:

  • First of all, select a cell and go to the Home tab.
  • Click on the Conditional Formatting and select Manage Rule.

Using Manage Rule to Compare Two Excel Sheets for Duplicates

  • At this point, a Conditional Formatting Rules Manager dialog box will appear.
  • Moreover, select the Rule bar and click Duplicate Rule.

  • At this moment, a new Rule bar appeared >> select it and hit Edit Rule.

  • At this moment, an Edit Formatting Rule dialog box will appear.
  • Now add ‘>1’ with the formula.
  • Therefore, the formula becomes
=COUNTIF('Conditional Formatting 2'!$C$5:$C$11,C5>1
  • Then, click on Format.

  • Then, a Format Cells dialog box will appear.
  • Furthermore, from Fill >> select a color.
  • Here, we choose Orange color, you can choose any color you want.
  • Therefore, you can see the Sample of the color.
  • Moreover, click OK.

Choosing a Color to Compare Two Excel Sheets for Duplicates

  • Along with that, you can see the Preview of the color in the Edit Formatting Rule dialog box.
  • Then, click OK.

  • Also, click OK in the Conditional Formatting Rules Manager dialog box.

  • Therefore, you can see the duplicate in cell C6 is highlighted with an Orange color.


4. Employ Excel VLOOKUP to Compare Sheets Duplicates

In this method, we will use the VLOOKUP function 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.

The first sheet is VLOOKUP 1, and we will pull the Salary from the second sheet.

  • The second sheet is Vlookup 2.

  • Next, we will write the following formula in cell C5 of the first sheet.
=IFERROR(VLOOKUP(B5,'VLOOKUP 2'!B5:C10,2,FALSE),"Not Available")

Employing Formula to Compare Two Excel Sheets for Duplicates

Formula Breakdown

  • The VLOOKUP function searches for a value in a range of cells based on a lookup value
  • The IFERROR function makes sure no error comes if the formula contains any error.
  • IFERROR(VLOOKUP(B5,’VLOOKUP 2′!B5:C10,2,FALSE),”Not Available”) → becomes
    • Output: $4000
  • Explanation: $4000 is the Salary for ID No 110.
  • After that, press ENTER.
  • In addition, drag down the formula with the Fill Handle tool.
  • After that, you can see the complete Salary column.

Dragging Down Formula to Compare Two Excel Sheets for Duplicates

  • Here, Not Available indicates that there are no Duplicates for those ID No in the second sheet.


5. Insert VBA Macro in Excel to compare Two Sheets for Duplicates

In this method, we will insert VBA to compare two Excel sheets for duplicates. Here, we will highlight cells that have no duplicates in two sheets with Red color. Therefore, the cells that will remain unhighlighted will be duplicates in two sheets.

Here, you can see the data in VBA 1 sheet.

Furthermore, you can see the data set in VBA 2 sheet.

These two sheets have duplicate values. Next, we will find those values using VBA.

Steps:

  • First of all, right-click on the VBA 1 sheet >> select View Code from the Context Menu.

  • At this point, a VBA Editor window will appear.
  • Moreover, we will type the following code in the window.
Sub Compare_Sheets_Duplicates()
Dim range_cell As Range
For Each range_cell In Worksheets("VBA 1").UsedRange
    If Not range_cell = Worksheets("VBA 2").Cells _
    (range_cell.Row, range_cell.Column) Then
     range_cell.Interior.Color = vbRed
   End If
Next range_cell
End Sub

Inserting VBA to Compare Two Excel Sheets for Duplicates

Code Breakdown

  • We take Comapre_Sheets_Duplicates as the Sub.
  • We declare range_cell as Range.
  • We used the For loop to find the duplicates until the last cell.
  • IF statement is used to color the cells that are not duplicates.
  • After that, Save the code >> Run the code.
  • In addition, go back to your Worksheet VBA 1.
  • Hence, you can see that the cells that have no duplicates are marked with Red color.
  • Therefore, the unlighted cells are duplicates.

Highlighted Cells to Compare Two Excel Sheets for Duplicates

Read More: How to Compare Two Excel Sheets for Differences in Values


Practice Section

You can download the above Excel file and practice while reading this article.


Conclusion

In this article, we describe 5 easy methods to compare two Excel sheets duplicates. Thank you for reading this article. We hope it was helpful. You can visit our website Exceldemy to explore more.


Related Articles

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also 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.

2 Comments
  1. Thanks for informative article in finding duplicates in one file. Could you please explain how can we perform this method, if our data is in two different files?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo