You may have tried to create a unique distinct list, using conditional formatting with excel 2007, merging duplicates into one distinct value. The problem is that it only highlights all values occurring only once.  See picture.

If you highlight all duplicates, excel 2007 highlights all values occurring twice or more. See picture.

But to be able to delete values occuring the second time or more, we need to use another conditional formatting formula. So I thought why not create a conditonal formatting formula that highlights only the cells that needs to be deleted. The end result is a unique (distinct) list. Here is how to do that:

Highlighting duplicate values occuring the second time or more:

1. Select the range (C2:C12)
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 =IF(COUNT(IF($C$2:$C2=$C2, 1, ""))>1, TRUE, FALSE)
8. Click OK!

Sorting duplicates to bottom

1. Right click on any cell in range C2:C12
2. Click "Sort"
3. Click "Custom sort..."
4. Select "On bottom" in the order column
5. Click OK!

Select highlighted values and press "Delete" on keyboard

Download excel example file.
highlight-duplicate-values.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

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

Related posts:

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