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.

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

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

1. Select a range
2. Go to "Home" tab
3. Click Conditional formatting
4. Click "Format only unique or duplicate values"
5. Click "Format" button
6. Select a formatting option
7. Click ok
8. 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

Related posts:

Highlight duplicate values in a range using conditional formatting in excel

Highlight unique distinct values in two ranges combined using conditional formatting in excel

Highlight duplicates using conditional formatting in excel

Highlight the second or more duplicates in two lists using conditional formatting in excel

Highlight common values in two lists using conditional formatting in excel