3 Ways to Highlight Duplicates in Two Excel Sheets

Comparing data between two Excel sheets often requires identifying and highlighting duplicates for accurate analysis. Whether you're merging customer databases, tracking inventory, or looking for repetitive entries, this task can be critical for ensuring data integrity. In this post, we'll explore three effective methods to highlight duplicates in two Excel sheets using conditional formatting, formulas, or Excel VBA.

Method 1: Using Conditional Formatting

Conditional formatting in Excel allows you to apply visual cues to cells that meet specific criteria. Here’s how to highlight duplicates between two sheets:

• Select the range in one sheet where you want to check for duplicates.
• Go to Home > Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter the formula: =COUNTIF(Sheet2!A:A,A1)>0 assuming you’re comparing column A from both sheets.
• Set the format you want for highlighting (e.g., red fill).
• Click OK and then OK again.

✅ Note: Ensure both sheets have the same column structure for accurate comparison. Also, remember to replace ‘Sheet2’ with the appropriate sheet name if different from the example.

Method 2: Using Formulas

If you need to flag duplicates without altering the cell content visually, you might prefer using formulas:

• On a new column in the first sheet, enter the formula: =IF(ISERROR(VLOOKUP(A1, Sheet2!A:A, 1, FALSE)), “”, “Duplicate”).
• Drag this formula down the column to apply it to all rows.
• The formula will check each cell in column A of Sheet1 against Sheet2’s column A.

This method will display “Duplicate” in the adjacent cell where a match is found, making it easy to see the duplicates without formatting changes.

Method 3: Using VBA for Advanced Operations

If you require more control over the highlighting process, or if your datasets are extensive, VBA can offer an automated solution:

• Open the VBA editor by pressing Alt + F11.
• Insert a new module through Insert > Module.
• Paste the following code into the module:

Sub HighlightDuplicates()
    Dim rng1 As Range, rng2 As Range, cel As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ThisWorkbook.Sheets(“Sheet1”)
    Set ws2 = ThisWorkbook.Sheets(“Sheet2”)
    Set rng1 = ws1.Range(“A1:A” & ws1.Cells(ws1.Rows.Count, “A”).End(xlUp).Row)
    Set rng2 = ws2.Range(“A1:A” & ws2.Cells(ws2.Rows.Count, “A”).End(xlUp).Row)

For Each cel In rng1
    If Not Application.WorksheetFunction.IsError(Application.VLookup(cel.Value, rng2, 1, False)) Then
        cel.Interior.Color = RGB(255, 0, 0) 'Red color
    End If
Next cel


End Sub

• Close the VBA editor and run the macro from Excel using Developer > Macros or by setting a button to trigger it.

🚨 Note: Ensure macros are enabled in your Excel settings for this method to work. Also, adjust the sheet names in the VBA code to match your Excel workbook’s setup.

In conclusion, identifying and highlighting duplicates between two Excel sheets is crucial for data analysis and integrity checks. Whether you prefer the visual cues of conditional formatting, the straightforwardness of formulas, or the control offered by VBA, Excel provides multiple avenues to achieve this task effectively. Understanding these methods allows you to choose the best approach for your particular dataset and requirements, ensuring your data analysis is accurate and efficient.

What is the difference between conditional formatting and formulas for highlighting duplicates?

+

Conditional formatting visually highlights duplicates directly within cells, whereas formulas provide a separate column with text indicating duplicates, preserving the original data’s appearance.

How does VBA benefit in highlighting duplicates in Excel?

+

VBA offers automation, allowing for extensive customizations like highlighting duplicates across multiple sheets or performing complex conditional highlighting, which would be time-consuming manually.

Can I highlight duplicates in Excel without altering the original data?

+

Yes, conditional formatting and formulas allow you to highlight or flag duplicates without changing the actual data in the cells. VBA can also be set up to perform visual highlighting only.