How to find and highlight duplicates in Excel

This tutorial shows you how to automatically highlight duplicate entries in Excel. We'll cover shading duplicate cells, entire rows, or consecutive duplicates using conditional formatting and a powerful add-in.

Last week, we looked at using formulas to find duplicates. Highlighting them visually, however, makes analysis significantly easier. Conditional formatting is the quickest method, automatically flagging new duplicates as you type.

These methods work across all Excel versions (365, 2021, 2019, 2016, 2013, 2010, and earlier).

- Highlight Duplicates

• Highlight Duplicates (Except First Instance)
• Highlight Duplicates Across Multiple Columns
• Highlight Nth and Subsequent Duplicates
• Shade Entire Rows Based on Duplicate Column Values
• Highlight Duplicate Rows
• Find Consecutive Duplicates
• Duplicate Remover Add-in (Fastest Method)

Highlighting Duplicates in Excel

Excel has a built-in rule for this. Follow these steps:

1. Select the data range (column, row, or range).
2. Go to the Home tab, click Conditional Formatting > Highlight Cells Rules > Duplicate Values…
3. The Duplicate Values dialog appears. Click OK to use the default red highlighting, or Custom Format… to choose your own colors.

Tip: Select "Unique" to highlight unique values instead.

This highlights duplicates across multiple columns (though it doesn't compare between columns – it simply flags all duplicates within the selected range).

Important Notes:

• This rule works on individual cells, not entire rows. For row-level highlighting, you'll need custom rules.
• It highlights all duplicates, including the first instance.

Highlighting Duplicates (Except First Instance)

To highlight only the second and subsequent occurrences:

1. Select the cells.
2. Go to Home > Conditional Formatting > New Rule > Use a formula…
3. Enter this formula: =COUNTIF($A$2:$A2,$A2)>1 (replace A2 with the topmost cell of your range).
4. Click Format…, choose your color, and click OK.

This highlights duplicates after the first instance.

Highlighting Nth and Subsequent Duplicates

Modify the formula above to highlight from the Nth occurrence:

• 3rd and subsequent: =COUNTIF($A$2:$A2,$A2)>=3
• 4th and subsequent: =COUNTIF($A$2:$A2,$A2)>=4
• Only the 2nd instance: =COUNTIF($A$2:$A2,$A2)=2

Highlighting Duplicates Across Multiple Columns

To highlight duplicates across multiple columns (without comparing between columns):

• Including First Occurrences: Use Excel's built-in duplicate rule or this formula: =COUNTIF($A$2:$C$8, A2)>1 (adjust range as needed).
• Excluding First Occurrences: This requires two rules: one for the first column (same as the "except first instance" rule above), and another for subsequent columns using a more complex formula (see original article for details). A single, more complex formula is also provided in the original article.

Highlighting Entire Rows Based on Duplicate Column Values

Select entire rows, then use these formulas:

• Excluding First Instance: =COUNTIF($A$2:$A2, $A2)>1
• Including First Instance: =COUNTIF($A$2:$A$15, $A2)>1 (adjust range as needed).

Highlighting Duplicate Rows (Across Multiple Columns)

Use COUNTIFS to compare across multiple columns:

• Excluding First Instance: =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)>1
• Including First Instance: =COUNTIFS($A$2:$A$15, $A2, $B$2:$B$15, $B2)>1 (adjust range as needed).

Highlighting Consecutive Duplicates

Select cells, then use these formulas:

• Excluding First Instance: =$A1=$A2
• Including First Instance: =OR($A1=$A2, $A2=$A3)

Formulas to ignore blank cells are also provided in the original article.

Highlighting Duplicates with the Duplicate Remover Add-in

The Duplicate Remover add-in offers a much faster way. It can highlight duplicates with various options (including color choices) in just a few clicks (see original article for screenshots and detailed instructions). The add-in provides both a quick "Dedupe Table" option and a more advanced step-by-step wizard.

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