To 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:

1. Highlight the second or more duplicates in two lists using conditional formatting in excel
2. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
3. Highlight smallest duplicate value in a column using conditional formatting in excel
4. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
5. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
6. Highlight duplicate values in a range using conditional formatting in excel
7. Highlight dates within a date range using conditional formatting
8. Prevent duplicates using dynamic conditional formatting in excel
9. Highlight unique values and unique distinct values in a range using conditional formatting in excel
10. Highlight common values in two lists using conditional formatting in excel