Selecting duplicates in excel

Posted on Latest In Blogs
145

A guide to Selecting duplicates in excel

There are two main approaches to finding duplicate rows in Excel: using Conditional Formatting and using formulas.

1. Highlighting Duplicates with Conditional Formatting (Easy & Visual)

This method is ideal for quickly identifying duplicate rows based on their appearance.

  1. Select the data range you want to check for duplicates.

  2. Go to the Home tab.

  3. Click on Conditional Formatting.

  4. In the dropdown menu, select Highlight Cells Rules > Duplicate Values.

  5. A pop-up window appears. Choose how you want to format the duplicates:

    • Format cells that contain duplicates: This option highlights all rows containing duplicate values in any column.
    • Format values only once: This highlights only the first occurrence of the duplicate value, leaving subsequent ones unformatted.

  6. Select your desired formatting style (e.g., fill color, font style) for highlighting duplicates.

  7. Click OK.

Now, all duplicate rows will be visually distinguished based on the formatting you applied.

2. Finding Duplicates with Formulas (More Control & Filtering)

This method offers more control over identifying duplicates based on specific criteria.

  1. Choose a helper column next to your data. In this example, we’ll use column E.

  2. In the first cell of the helper column (E2), enter the following formula (assuming your data starts in row 2):

    Excel
    =COUNTIF($A$2:$D2, $A2:$D2) > 1
    • This formula counts the occurrences of values in each row across all columns (A:D) referenced in the formula.
    • If the count is greater than 1, it means there’s a duplicate, and the formula returns TRUE. Otherwise, it returns FALSE.

  3. Drag the formula down throughout the helper column to apply it to all rows.

  4. Now, any cell in the helper column showing TRUE indicates a duplicate row in the corresponding data row.

  5. Filtering: You can use this formula to filter and view only duplicate rows. Select the entire data set, then go to the Data tab and click Advanced.

    • In the “Criteria range” section, select the helper column (E:E in this example).
    • Check the box for “Copy to another location.”
    • Choose a destination for filtered results (e.g., a new sheet).
    • Under “List criteria,” select “Contains” and enter TRUE.
    • Click OK. This will filter and display only the rows marked as duplicates in the new sheet.

Additional Considerations:

  • Both methods can be customized to consider duplicates based on specific columns instead of the entire row.
  • For the formula method, adjust the cell references in the COUNTIF function to match your data range and desired comparison columns.
  • You can remove duplicates entirely using the “Remove Duplicates” function under the Data tab.

By following these methods, you can effectively identify and manage duplicate rows in your Excel spreadsheets.