How to Find Duplicates in Excel

In this lesson, you will learn how to find duplicates in Excel. It’s a quick way to find a large amount of data in just a second.

Applying conditional formatting to find duplicates

You have 3 columns of data and you want to highlight duplicates.

Compare Columns Table

First select the whole table.

Compare Columns Data

Go to the Ribbon. Home tab> Styles section. Click here to view Conditional Formatting > Highlight Cell Rules > Duplicate Values

Compare Columns Duplicate Values

A dialog box will appear. Choose how to highlight duplicates in Excel. I chose dark red text with a light red fill.

Compare Columns Values

Excel highlights duplicates in your table.

Excel compare columns find duplicates

Applying the match formula

Another way I know is to use the match function.

Excel includes a match function that allows you to find a value in a given range of cells. It is enough to dress the match function in logical functions and we will get such a formula

=NOT(ISNUMBER(MATCH(B3,$C$3:$C$8,0)))

Match formula to find duplicates

As you can see in the picture above, Excel correctly identified the duplicates. When the value is unique, it displays TRUE, and if a duplicate is found in the comparison between column B and C, it returns FALSE.

When the value is unique it displays TRUE. If it found a duplicate in column B, it returned FALSE.

See also  Show Yes/No As the Values in a Pivot Table