## How to highlight unique distinct values

*Article updated on December 22, 2017*

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

- Select the range (C2:C12)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formual is true" window.
- Type =IF(COUNT(IF($C$2:$C2=$C2, 1, ""))>1, TRUE, FALSE)
- Click OK!

**Sorting duplicates to bottom**

- Right click on any cell in range C2:C12
- Click "Sort"
- Click "Custom sort..."
- Select "On bottom" in the order column
- 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

### 3 Responses to “How to highlight unique distinct values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,19,8,13,

131,19,73,97,19,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8.

Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,19,8,13,

131,19,73,97,19,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8. Zen Archery

In his book Wonders of Numbers (Oxford: Oxford University Press, 2001), pp. 275-276, Clifford Pickover posed a "Zen Archery" problem. In its simplest form, there is a target with 24 numbers on it. The archer must shoot 5 arrows at the target and hit numbers adding up to 200. The 24 numbers on the target are

97,101,139,41,37,31,29,89,23,19,8,13,

131,19,73,97,19,139,79,67,61,17,113,127

Pickover posed a similar problem at Archery by the Numbers. This is really a combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

There is some quick and dirty Java code on the Web, associated with Pickover's book, which solves the Zen archery problem for the 24 numbers given. However, it is not exactly a model of good programming, and it even assumes some foreknowledge of the answer in the code, i.e. the fact that all combinations adding up to 200 include the number 8. the result is 27 groups of 5 number, the optimal combination how can I do that in excel 2003

or do pivot table on the list it is much faster and easier

Rona,

you are right! Sometimes you just want to examine the values having duplicates and then take the appropriate actions.