Find Duplicates

Learn how to find duplicate values and highlight duplicate rows in Excel using conditional formatting through useful examples!

How to find duplicates in Excel

You can use a few easy steps to find and highlight duplicate values in Excel. But, first, let us see how it works.

1. Select data, in this case, C2:E12

select range

2. Click on the Home tab. Find the Conditional Formatting icon in the Styles Group.

locate the styles group on the home tab

3. Click Highlight Cells Rules. Choose the ‘Duplicate Values’ command from the list.

find duplicates in excel

4. Select a formatting style that you want to apply on the right side of the dialog box. Click OK. Excel will find the duplicate names in the range and highlight them using the picked color.

Excel will find the duplicate names in the range

Tip: If you want to apply an inverse transformation (for example, find unique values), use the ‘Unique’ option from the list.

How to highlight four duplicates in Excel

You want to find and highlight duplicates using a new rule in the example.

Your goal is to find the name which has four occurrences.

Select the range C2:E12 and use the formula


edit the rule description

How the formula works:

The =COUNTIF($C$2$:C$12, C2) formula counts the occurrences of records in the range C2:E12 equal to the name in cell C2. Thus, in this example, Michael.

find duplicates that occur 4 times

COUNTIF($C$2:$E$12,C2) = 4, so Excel formats cell C2. For example cell C2 contains the formula =COUNTIF($C$2:$E$12,C2)=4, cell C3 =COUNTIF($C$2:$E$12,C3)=4, and so on.

Find duplicates that occur more than n-times

If you change the search parameters, you can easily modify the formula. For example, you want to find and highlight duplicates that occur more than four times, adjust the formula:

For example, =COUNTIF($C$2:$E$12, C1)> 3 will highlight names that occur more than three times.

It’s important to use mixed references. For example, if you use a fixed range, like C2:E12, apply the $ sign, $C$2:$E$12 (absolute references).

The second part of the formula use relative references.

find duplicates example 2

How to find Duplicate rows in Excel

In this example, you want to highlight duplicated tows. The easiest method to do that is using the COUNTIFS function.

  1. Select the range H2:H12
  2. On the Home tab, click Conditional Formatting
  3. Choose ‘New Rule.’
  4. In the New Formatting Rule dialog box
  5. Select the ‘Use a formula to determine which cells to format.’
  6. Enter the formula: =COUNTIFS(Column1,$H2,Column2,$I2,Column3,$J2)>1
  7. Select the formatting style and click OK to apply the rule
countifs formula to find duplicates rows

Tip: we strongly recommend you use named ranges instead of cell references.

So, Column1 refers to the range H2:12; Column 2 refers to I2:12, and Column3 refers to J2:J12.

If you want to remove the duplicated rows quickly, use the Remove Duplicates Tools in Excel.

Download the practice file and learn more about conditional formatting.

Additional resources

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.