5 Ways to Spot Duplicates in Two Excel Sheets Easily

Compare Visually

The most straightforward way to find duplicates between two Excel sheets is by comparing them visually. Here’s how you can do it:

• Open both sheets in the same Excel workbook or in separate windows if using different workbooks.
• Adjust the view so you can see the columns you're comparing side by side.
• Scroll through each sheet simultaneously, looking for matching values or patterns.

🔍 Note: This method is effective for small datasets but becomes inefficient as the number of rows increases.

Conditional Formatting

Use Excel’s conditional formatting to highlight duplicates:

• Select the cells in the first sheet where you want to highlight duplicates.
• Go to Home > Conditional Formatting > New Rule.
• Choose Use a formula to determine which cells to format.
• Enter a formula like this: =COUNTIF(Sheet2!A:A, A1) > 0, assuming you're comparing column A in both sheets.
• Click Format to choose how duplicates should be highlighted, then press OK.
• Repeat this on the second sheet, switching the sheet references.

VLOOKUP or XLOOKUP Functions

These functions can help you spot duplicates:

• In the first sheet, insert a new column next to the data you're checking for duplicates.
• Type the following formula in the cell of the new column: =IF(ISERROR(VLOOKUP(A1, Sheet2!A:A, 1, FALSE)), "Unique", "Duplicate").
• Use XLOOKUP for newer versions of Excel: =IF(XLOOKUP(A1, Sheet2!A:A, "Duplicate", "Unique")="Duplicate", "Duplicate", "Unique").
• Drag the formula down to apply it to the entire column.

Power Query

For larger datasets, Power Query offers an advanced solution:

• Import both sheets into Power Query using From Table/Range from the Data tab.
• Merge both queries on the key columns you want to check for duplicates.
• In the Merge dialog, choose to use a Left Outer join or another relevant join type.
• Expand the new column created by the merge, and then filter to show only rows where duplicates exist.
• Load the result back to Excel or continue to modify the data in Power Query.

Using VBA

If you’re comfortable with coding, VBA can automate this process:

• Press Alt+F11 to open the VBA editor.
• Insert a new module by right-clicking on any of your VBA project files in the left window.
• Paste the following code to find duplicates:

  
  Sub FindDuplicates()
      Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
      Dim lastRow As Long, i As Long, j As Long
      Dim rng1 As Range, rng2 As Range
      
      Set ws1 = ThisWorkbook.Sheets("Sheet1")
      Set ws2 = ThisWorkbook.Sheets("Sheet2")
      Set ws3 = ThisWorkbook.Sheets("Sheet3")  ' Optional for reporting results
      
      lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
      
      For i = 1 To lastRow
          Set rng1 = ws1.Range("A" & i)
          On Error Resume Next
          Set rng2 = ws2.Range("A:A").Find(What:=rng1.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
          On Error GoTo 0
          If Not rng2 Is Nothing Then
              ws3.Cells(i, 1) = rng1.Value  ' This assumes you have a "Sheet3" for results
          End If
      Next i
  End Sub
  
  

This wraps up the easy-to-follow strategies for finding duplicates in Excel, each suited for different data sizes and user proficiency. By adopting these methods, you can swiftly identify and manage duplicates in your datasets, enhancing your data analysis capabilities.

Final Thoughts

Spotting duplicates between two Excel sheets can save time and reduce data inaccuracies. Each method mentioned has its unique advantages:

• Visual Comparison suits smaller datasets.
• Conditional Formatting offers an immediate visual cue for duplicates.
• VLOOKUP/XLOOKUP functions are handy for comparing specific columns.
• Power Query is best for complex data merging and cleaning.
• VBA provides the automation needed for regular, repetitive tasks.

Choose the method that fits your dataset size, skill level, and workflow needs for maximum efficiency in managing your Excel data.