Highlight unique values and unique distinct values in a range using conditional formatting in excel

Filed in Excel on Jul.19, 2009.

Highlighting unique distinct values in a range using conditional formatting

Repeated values are not highlighted, only unique distinct values.

Conditional formatting formula:

=IF(ROW(A1)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(A1)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), A1)+COUNTIF(OFFSET(tbl, MIN(ROW(A1)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(A1))), A1), COUNTIF(OFFSET(tbl, 0, 0, 1, MIN(COLUMN(A1))), A1))

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

Share and Enjoy: