3 Ways to Find Duplicates in Excel Across Sheets

In the world of data management, dealing with duplicate entries is a common challenge. Microsoft Excel, a stalwart in the field of spreadsheet software, provides multiple solutions for detecting duplicates not just within a single worksheet, but across several sheets. Here's how you can manage duplicate entries effectively:

Method 1: Using Conditional Formatting

Conditional Formatting in Excel is a tool that allows you to highlight cells that meet specific criteria. Here’s how to spot duplicates across multiple sheets:

• Select the range or column where you want to find duplicates in the first sheet.
• Go to the ‘Home’ tab, click ‘Conditional Formatting’, then ‘New Rule’.
  
• Choose ‘Use a formula to determine which cells to format’.
• Enter the formula:

  =COUNTIF(INDIRECT(“‘Sheet2:Sheet” & SHEET() & “’!A1:A1048576”), A1)>1</pre>
      where:
      <ul>
        <li>Sheet2:Sheet is the range of sheets you're searching through.</li>
        <li>A1:A1048576 is the range of cells to compare.</li>
        <li>A1 refers to the cell being evaluated.

🔍 Note: Adjust the sheet and range names to match your workbook setup.

Method 2: Consolidating Data with VBA

If you’re dealing with a large workbook, using Visual Basic for Applications (VBA) can automate the process:

• Press Alt + F11 to open the VBA Editor.
• Insert a new module by going to Insert > Module.
• Enter the following VBA code:

      Sub FindDuplicates()
          Dim ws As Worksheet, wsList As Worksheet
          Dim dict As Object, cell As Range, val As String
          Dim i As Long
  
      Set dict = CreateObject("Scripting.Dictionary")
  
      ' Create a sheet to list duplicates
      Set wsList = ThisWorkbook.Worksheets.Add
      wsList.Name = "Duplicates List"
  
      ' Loop through each worksheet
      For Each ws In ThisWorkbook.Worksheets
          If ws.Name <> wsList.Name Then
              For Each cell In ws.UsedRange
                  val = cell.Value
                  If dict.Exists(val) Then
                      dict(val) = dict(val) + 1
                  Else
                      dict.Add val, 1
                  End If
              Next cell
          End If
      Next ws
  
      ' List all duplicates with occurrences greater than 1
      For Each Key In dict.keys
          If dict(Key) > 1 Then
              i = i + 1
              wsList.Cells(i, 1).Value = Key
              wsList.Cells(i, 2).Value = dict(Key)
          End If
      Next Key
  End Sub
  </pre>
  
  
  

• Run the macro by pressing F5 or running it from Excel’s Macro dialog box.

This VBA script will search for duplicates across all sheets except for the one it creates to list the duplicates.

Method 3: Power Query for Advanced Data Matching

Power Query is Excel’s data transformation tool that can handle complex queries:

• Go to ‘Data’ tab, click ‘From Table/Range’ to load your data into Power Query Editor.
• Use the ‘Merge Queries’ feature to combine data from multiple sheets.
• Select the columns you want to check for duplicates.
• Select ‘Advanced Editor’ and enter:

      let
          Source = Excel.CurrentWorkbook(),
          Sheets = Source{…}…,
          MergedData = Table.Combine(Sheets),
          Grouped = Table.Group(MergedData, {“Column1”}, {{“Count”, each Table.RowCount(_), Int64.Type}}),
          Filtered = Table.SelectRows(Grouped, each [Count] > 1)
      in
          Filtered
      

  Replace ‘Column1’ with the name of the column you want to check for duplicates.
• Close and load the data back to Excel.

💡 Note: Ensure all column names are consistent across sheets when using Power Query.

By employing these methods, you can efficiently identify and manage duplicate data in your Excel workbooks. Whether you're handling a small dataset or a complex one, Excel offers tools that cater to all skill levels, from basic conditional formatting to advanced Power Query operations. These techniques not only help in cleaning your data but also in enhancing its reliability and usability.

Can I find duplicates in Excel without VBA or Power Query?

+

Yes, you can use Conditional Formatting to highlight duplicates directly in the Excel interface without any coding or advanced tools.

How do I handle duplicates once they are found?

+

Once duplicates are highlighted or listed, you can manually delete, merge, or flag them for review. If using VBA or Power Query, you could automate the process further with custom scripts to remove or aggregate duplicate entries.

Will these methods affect the performance of my Excel workbook?

+

Using Conditional Formatting is generally low impact. However, VBA and Power Query might slow down your workbook if dealing with very large datasets. It’s advisable to manage dataset size or use these tools on optimized computers.