Author: Oscar Cronquist Article last updated on November 14, 2018

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted.

Conditional Formatting Formula:

=COUNTIF($B$3:B3, B3)=1

The COUNTIF function counts the value in cell B3 in cell range $B$3:B3 and if it is equal to 1 the formula returns TRUE and that will highlight the cell.

$B$3:B3 is an expanding cell range, the first part of the cell reference is locked to B3 and the second part changes as the CF formula is applied to cells below.

Use the dollar sign $ to lock a cell reference, if you put it in front of the column letter then the column is locked. The same thing happens if you put it in front of the row number.

How to apply conditional formatting to a cell range

  1. Select cell range.
  2. Go to tab "Home"
  3. Click "Conditional Formatting" button.
  4. Click "New Rule..."
  5. Select "Use a formula to determine which cells to format"
  6. Type the formula above in field "Format values where this formula is true:"
  7. Click "Format" button, then pick a formatting.
  8. Click OK button.
  9. Click OK button.

Download Excel *.xlsx file

Highlight unique distinct values.xlsx