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.

highlight-unique-values

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

highlight-unique-values2

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!

highlight-unique-values3

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!

highlight-unique-values4

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