## Highlight unique values and unique distinct values in a cell range

*Article updated on December 22, 2017*

### Highlighting unique distinct values in a range using conditional formatting

Repeated values are not highlighted, only unique distinct values.

Conditional formatting formula:

### Named ranges

tbl (A1:D4)

What is named ranges?

### How to implement conditional formatting formula to your workbook in excel 2007

Let ´s say your range is D4:G9.

- Select your range D4:G9.
- Go to "Home" tab
- Click Conditional formatting
- Click "New Rule.."
- Click "Use a formula to determine what cells to format"
- Change A1 in the above conditional formatting formula to D4. Your range is D4:G9, remember?.
- Copy the conditional formatting formula to "Format values where this formula is true:"
- Click Format button
- Select a formatting you like. For example, cells filled with yellow.
- Click OK
- Click OK

### Highlighting unique values in a range in excel 2007 using conditional formatting

- Select a range
- Go to "Home" tab
- Click Conditional formatting
- Click "Format only unique or duplicate values"
- Click "Format" button
- Select a formatting option
- Click ok
- Click ok

### Highlighting unique values in a range in earlier versions of excel using conditional formatting

Conditional formatting formula:

=COUNTIF(range,first_cell_in_range)=1

Example, range A1:D4. Formula: =COUNTIF($A$1:$D$4,A1)=1

Download excel example file.

highlight-unique-distinct-values-in-a-range.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

**ROW(**reference**)**

returns the row number of a reference

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MAX(**number1,[number2],**)
**Returns the largest value in a set of values. Ignores logical values and text.

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**COLUMN(**reference**)** Returns the column number of a reference

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

Count how many numbers between a specific value occurring multiple times

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]### One Response to “Highlight unique values and unique distinct values in a cell range”

### 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

When you modify to use anything other than the 'A' column the first instance of duplicate does not highlight. Using the example simply move 'tbl' one column to the right to see.

Regards

Dave