How to find and remove duplicate cells in Excel

This tutorial shows you how to quickly identify and manage duplicate data in your Excel worksheets. Whether your data is imported or manually entered, duplicates create problems. This guide covers several effective deduplication techniques.

- Highlight duplicate cells

• Find duplicates using formulas
• Remove duplicate rows
• All-in-one tool for finding and deleting duplicates

Note: This article focuses on duplicate cells within a range or list. For comparing two columns, see our separate guide on finding duplicates across columns.

How to Highlight Duplicate Cells

Excel's Conditional Formatting is typically used to highlight duplicates. You can use a pre-defined rule or create a custom formula-based rule.

Example 1: Highlight All Duplicates (Including First Occurrence)

This uses a built-in rule to highlight all instances of a duplicate value.

1. Select the range containing your data.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values…
3. Choose your formatting (the default is Light Red Fill with Dark Red Text) and click OK.

Tips: Customize formatting using Custom Format…, or highlight unique cells by selecting Unique instead of Duplicate.

Example 2: Highlight Duplicates (Excluding First Occurrence)

This requires a custom rule and an empty column to the left of your data (column A in this example).

1. Select the data range.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter this formula: =IF(COLUMNS($B2:B2)>1, COUNTIF(A$2:$B$7,B2),0) COUNTIF(B$2:B2,B2)>1 (B2 is the first cell in your data, B7 is the last, A2 is the corresponding cell in the empty column).
4. Click Format…, choose your formatting, and click OK.

Tips: An empty column is needed. For multiple columns, use separate rules. For structured data, see how to highlight rows based on duplicate values in a key column. A simpler method is using the Find Duplicate Cells tool.

Finding Duplicates with Formulas

COUNTIF and IF functions can identify duplicates within a column.

To find all duplicates (including the first): IF(COUNTIF(*range*, *cell*)>1, "Duplicate", "")

To find duplicates excluding the first: IF(COUNTIF(*expanding_range*, *cell*)>1, "Duplicate", "")

The difference lies in the range definition. For example:

=IF(COUNTIF($A$2:$A$10, A2)>1, "Duplicate", "") (finds all duplicates)

=IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "") (finds duplicates excluding the first)

Deleting Duplicates

Excel's Remove Duplicates tool deletes entire rows based on duplicates in specified columns. It keeps the first occurrence.

1. Select your data.
2. Go to Data > Remove Duplicates.
3. Select the columns to check and click OK.

Tips: Back up your data before using this tool. Remove any filters or subtotals beforehand. For deleting duplicates within individual cells, use the Duplicate Cells tool.

All-in-One Duplicate Management Tool

The Ablebits Duplicate Remover add-in simplifies duplicate management:

• Finds duplicates (with or without first occurrences) or unique cells.
• Finds duplicates based on values, formulas, or formatting.
• Handles case-sensitive searches and ignores blanks.
• Clears, colors, or selects duplicates.

1. Select your data.
2. Go to the Ablebits Data tab > Duplicate Remover > Find Duplicate Cells.
3. Configure your search options and choose an action (clear, color, or select). Click Finish.

For structured data, use Duplicate Remover. For comparing two tables or columns, use the Compare Two Tables tool. These are included in Ultimate Suite.

Download Links

Example files and Ultimate Suite trial are available for download.

The above is the detailed content of How to find and remove duplicate cells in Excel. For more information, please follow other related articles on the PHP Chinese website!