How to just identify (and not remove) duplicates in excel column?

Question

I have an excel sheet containing hundreds of rows.

A particular column might have values repeated across multiple rows in the same column.

I need to identify such values in that column. How do I do that?

VLOOKUP would be handy if I need to check value from 1 column exists in another column; but guess it would be very cumbersome to find duplicates in the same column.

Answer 1

If you use vlookup you must pay attention that the range formula in the control column excludes the value itself i.e: Let's say you have are looking for dups in column A from row 1 to 120, then in B1 you would write:

VLOOKUP(A1,A2:A$120,1,FALSE)

in B2:

VLOOKUP(A2,A3:A$120,1,FALSE)

With Conditional Formatting you have 2 ways:

1) With the provided function Home->Conditional Formatting->Highligth Rules->Duplicate Values (which will color every duplicated values regardless of how many time they occur)

2) By creating a custom Highlight Rule (Home->Cond. Form->Highligh..->Other Rules->Use formula to determine...) and adding as a formula:

=COUNTIF(A1:A120,A1:A120)>1

Where A1:A120 is the range of your search and >1 is the number of occurrence you look for. In that case only the duplicates are highlighted but not the 'original' (So if you have 2 times the value 'foobar' it will be highlighted only once).

This method will allow you to give different colors to different occurrences (say: 2 times yellow, 3 times red...)

Answer 2

As M. Sgarbossa Mentioned, You can get the Duplicate Values by highlighting it. Otherwise You can use Match function to highlight the first value which will repeat later in the column. Use following function as given in image below:

=IF(ISNUMBER(MATCH(Match_Value,Next_Cell:Last_Cell(Locked),0)),"Repeating","")

Answer 3

conditional formatting -> highlight cells rules -> duplicate values