Finding duplicate rows in excel

Question

I have an excel spreadsheet with two rows. One is for name, and the other is for id. Is there any way i can find the rows that have the exact same id and allow me to easily see those? For example i have the rows:

Name:     Id
Hello     1
World     5
Mylo      1
Jack      6
Jil       9
Frank     5

So in the above example data, excel should somehow mark the rows with hello and mylo and world and frank to indicate that there are duplicate id's for those

Answer 1

In the third column you could add this formula (cell B3 example) :

=IF(COUNTIF(B$2:B$7,"="&B2)>1,"<--Dup!","") 

You will have to adjust the range B$2:B$7 to reflect the actual data range...

Answer 2

The absolute fastest and easiest way. Conditional formatting, highlight duplicates (on the ID column). Then filter the column (presumably in a table) by the coloring (above the check boxes).