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

The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance are not highlighted.

Conditional formatting formula:

### How to apply conditional formatting

- Select your range B2:E5.
- Go to "Home" tab on the ribbon.
- Press with left mouse button on "Conditional formatting" button.
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "Use a formula to determine what cells to format"
- Copy the conditional formatting formula above and paste to "Format values where this formula is true:"
- Press with left mouse button on Format button
- Select a formatting.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button.

### Explaining CF formula in cell B2

There are two parts in this formula, one part determines if a value is a duplicate in the first column. The second part of the formula determines if a value is a duplicate in the remaining columns.

The reason the formula looks like this is because of the order of how Excel calculates cells.

IF(*logical_expression*, *first_part*, *second_part*)

#### Step 1 - Check if first column is being evaluated

The COLUMNS function counts columns in a cell reference.Â $A$1:A1Â is an expanding cell reference, it grows becauseÂ A1 is a relative cell reference that changes between cells.

COLUMNS($A$1:A1)=1

becomes

1=1 and returns TRUE.

#### Step 2 - Count cells based on a condition

The IF function changes the calculation based on the logical expression in the first argument. The second argument is calculated if the logical expression returns TRUE, the third argument is calculated if the logical expression returns FALSE.

The COUNTIF function makes sure that duplicates are not highlighted, only the first instance of each value. However this works only in the first column, the remaining columns need a different formula in order to do correct calculations.

IF(COLUMNS($A$1:A1)=1,COUNTIF($B$2:B2,B2),COUNTIF($B$2:B2,B2)+COUNTIF(OFFSET($B$2:$E$5,,,4,COLUMNS($A$1:A1)-1),B2))=1

becomes

IF(TRUE,COUNTIF($B$2:B2,B2),...)=1

becomes

IF(TRUE,COUNTIF(0,0),...)=1

becomes

1=1

and returns TRUE. Cell B2 is highlighted.

#### Step 3 - Calculations in remaining columns

If we move to cell C2 the IF function behaves differently.

IF(COLUMNS($A$1:A1)=1, COUNTIF($B$2:C2, C2), COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))=1

becomes

IF(COLUMNS($A$1:B1)=1, COUNTIF($B$2:C2, C2),COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))=1

becomes

IF(2=1, COUNTIF($B$2:C2, C2), COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))=1

becomes

IF(FALSE, COUNTIF($B$2:C2, C2), COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))=1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, COUNTIF({0,6}, 6)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))=1

The OFFSET function returns an expanding cell reference that grows as the CF moves from column to column.

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+COUNTIF(OFFSET($B$2:$E$5, , , 4, 1), C2))=1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+COUNTIF($B$2:$B$5, C2))=1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+COUNTIF({0;11;14;16},6))=1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+0)=1

becomes

1=1 and returns TRUE. Cell C2 is highlighted.

### Get Excel file

highlight unique distinct values in a range.xlsx

### Recommended reading

### Built-in conditional formatting

Data Bars Color scales Icons### Highlight cells rule

Highlight cells containing stringHighlight a date occuring

Highlight cells equal to

Highlight unique/duplicates

### Top bottom rules

Highlight top 10 valuesHighlight top 10 % values

Highlight above average values

### Basic CF formulas

Working with Conditional Formatting formulasFind numbers in close proximity to a given number

Highlight empty cells

Highlight text values

### Search using CF

Highlight records – multiple criteria [OR logic]Highlight records [AND logic]

Highlight records containing text strings (AND Logic)

Highlight lookup values

### Unique distinct

How to highlight unique distinct valuesHighlight unique values and unique distinct values in a cell range

Highlight unique values in a filtered Excel table

Highlight unique distinct records

### Duplicates

How to highlight duplicate valuesHighlight duplicates in two columns

Highlight duplicate values in a cell range

Highlight smallest duplicate number

Highlight more than once taken course in any given day

Highlight duplicates with same date, week or month

Highlight duplicate records

Highlight duplicate columns

Highlight duplicates in a filtered Excel Table

### Compare

Highlight missing values between to columnsCompare two columns and highlight values in common

Compare two lists of data: Highlight common records

Compare tables: Highlight records not in both tables

How to highlight differences in price lists

Compare two columns and highlight differences

### Min max

Highlight smallest duplicate numberHow to highlight MAX and MIN value based on month

Highlight closest number

### Dates

Highlight dates in a date rangeHow to highlight MAX and MIN value based on month

Highlight odd/even months

Highlight overlapping date ranges using conditional formatting

Highlight records based on overlapping date ranges and a condition

Highlight date ranges overlapping selected record [VBA]

How to highlight weekends [Conditional Formatting]

How to highlight dates based on day of week

Highlight current date

### Misc

Highlight every other rowDynamic formatting

How to change cell formatting using a Drop Down list

Highlight cells based on ranges

Highlight opposite numbers

Highlight cells based on coordinates

### Excel categories

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form