How to Find Duplicates in Excel

Microsoft Excel offers various methods to identify and manage duplicate data within your spreadsheets. Whether you're working with a small dataset or a large database, efficient duplicate management is essential. In this article, we'll explore several approaches to finding duplicates in Excel.

How to Find Duplicates in Excel

1. Conditional Formatting:

Conditional formatting is a visual way to highlight duplicate values in your dataset.

1. Select the range of cells containing your data. (lets select the "Price" row)

• Navigate to the "Home" tab on the ribbon.
• Click on "Conditional Formatting" in the "Styles" group.

• Choose "Highlight Cells Rules" and then "Duplicate Values."

• Customize the formatting options and click "OK."

This method visually identifies duplicate values, making them easy to spot in your spreadsheet.

2.COUNTIF Function:

The COUNTIF function helps identify duplicates by counting the occurrences of each value.

• Create a new column next to your data.

• Use the following formula (adjust the range based on your data)

excel formula

=COUNTIF($A$2:$A$100, A2)>1

Drag the formula down to apply it to all cells.

Filter or sort the new column to view and manage duplicates.

This method provides a systematic way to identify duplicates based on a count criterion.

3.Find Duplicates with Macro

Step 1: Open the VBA Editor

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Insert a New Module

• In the VBA editor, right-click on any item in the left pane (e.g., "VBAProject (YourWorkbookName)").
• Choose Insert and then Module.

Step 3: Copy and Paste the VBA Code

• Copy the following VBA code:

Sub HighlightDuplicatesInSelection()
    ' Check if a range is selected
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", vbExclamation
        Exit Sub
    End If
    
    Dim selectedRange As Range
    Set selectedRange = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In selectedRange
        ' Check if the value is a duplicate within the selected range
        If WorksheetFunction.CountIf(selectedRange, cell.Value) > 1 Then
            ' Highlight the cell with a specified color (yellow in this example)
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
    
    ' Display a message indicating the process is complete
    MsgBox "Duplicate highlighting process completed successfully.", vbInformation
End Sub

• Paste the code into the module you inserted in Step 2.

Step 4: Close the VBA Editor

• Close the VBA editor by clicking the "X" button or pressing Alt + Q.

Step 5: Select the Range in Excel

• In your Excel workbook, select the cell or range of cells where you want to highlight duplicate values.

Step 6: Run the Macro

• Press Alt + F8 to open the "Macro" dialog.
• Select the macro named "HighlightDuplicatesInSelection" from the list.
• Click "Run."

Step 7: View Results

11. Observe that the selected cells with duplicate values are highlighted in yellow.

Note:

• You can modify the color of the highlight by adjusting the RGB values in the code.
• Ensure that you save your workbook as a macro-enabled workbook (.xlsm) if prompted.

Following these steps will help you use the provided VBA code to highlight duplicate values in a selected range within your Excel workbook.

FAQ's