❏ Find Duplicates
There are three simple approaches to dealing with duplicates in Excel. One of which will extract only the unique values from a list with potential duplicates. The second, return true if a value appears more than once, and a quick conditional formatting rule to highlight all duplicates.
Return Unique Values
Syntax: =INDEX(LIST,MATCH(0,COUNTIF(NEW LIST,LIST),0))
Example: =INDEX(B5:B15,MATCH(0,COUNTIF($D$4:D4,B5:B15),0))
Return True If Duplicates Appear
Syntax: =COUNTIF(LIST RANGE,RELATIVE CELL)># OF INSTANCES
Example: =COUNTIF($B$5:$B$15,B5)>1
Highlight All Duplicates In A Range
Highlight list
Select “Conditional Formatting”
Select “Highlight Cell Rules”
Select “Duplicate Values …”