highlight-duplicates-using-conditional-formattingTo the right is a picture of an excel list. (B3:B11) The second or more duplicates are colored and are easily identified.

To sort all duplicates to the bottom of the list for removal, creating a unique distinct list. See this blog post
How to create a unique list using conditional formatting in excel 2007

To color duplicate cells I use conditional formatting in excel. The conditional formatting formula in B3:B11:

=COUNTIF($B3:$B$3, $B3)-1>0

How to highlight duplicate values occuring the second time or more using conditional formatting in excel:

  1. Select the range (B3:B10)
  2. Click "Home" tab on the ribbon
  3. Click "Conditional formatting"
  4. Click "New rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Click "Format values where this formual is true" window.
  7. Type COUNTIF($B3:$B$3, $B3)-1>0
  8. Click OK!

Download excel example file.
highlight-duplicates-using-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

Related posts:

Highlight the second or more duplicates in two lists using conditional formatting in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel

Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel

Prevent duplicates using dynamic conditional formatting in excel