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.
Table of Contents
Applying conditional formatting to find duplicates
You have 3 columns of data and you want to highlight duplicates.
First select the whole table.
Go to the Ribbon. Home tab> Styles section. Click here to view Conditional Formatting > Highlight Cell Rules > Duplicate Values
A dialog box will appear. Choose how to highlight duplicates in Excel. I chose dark red text with a light red fill.
Excel highlights duplicates in your table.
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)))
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.
