Tips for identifying duplicates in a list
Lets say the list is
Tip#1: Conditional formatting
Go to conditional formatting box and enter
“Formula is” =COUNTIF($A$1:$A$8,A1)>1
Format the box with a different colour (yellow in above)
Format paint for all cells A1 to A8 with ‘format painter’ brush
Tip#2: Array formula
Enter an array formula
{=IF(MAX(COUNTIF(A1:A8,A1:A8))>1,”Duplicates”,”No Dups”)}
Array formulas are entered by ctrl+shift+enter
In this example, formula gives result as Duplicates
Tip#3: Flag
In cell B1 enter
=IF(COUNTIF($A$1:$A$8,A1)>1,”Dup”,”No_dup”)
Drag the formula to B8
This flags off the dup entries
Tip#4: create an array of unique entries
Select A1:A8, click Data -> Filter -> Advanced Filter… ->Unique records only
This list is without any duplicates
Useful Excel Tips 