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

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.

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.

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

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

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.

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

• 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

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

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

• After that, press ENTER.
• In addition, drag down the formula with the Fill Handle tool.
• After that, you can see the complete Salary column.

• 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

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

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

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