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. If it found a duplicate in column B, it returned FALSE.

I am doing this Excel tutorial to help you solve such problems. I hope that I was successful and that my website will help you in many other matters.