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

Question: How do I highlight the smallest duplicate value in a column using conditional formatting?

Answer:

Conditional formatting formula in A2:

=MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))=A2

How to apply conditional formatting

  1. Select cell range
  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. Copy the above conditional formatting formula to "Format values where this formula is true:"
  7. Click Format button
  8. Select a formatting you like.
  9. Click OK
  10. Click OK

Explaining CF formula in cell A2

Step 1 - Identify duplicates

The COUNTIF function counts values based on a condition or criteria.

COUNTIF($A$2:$A$11, $A$2:$A$11)>1

becomes

COUNTIF({6;4;6;12;18;12;10;3;11;8}, {6;4;6;12;18;12;10;3;11;8})>1

becomes

{2;1;2;2;1;2;1;1;1;1}>1

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 2 - Extract duplicate numbers

The IF function uses a logical expression in order to determine which argument to return.

IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, $A$2:$A$11, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, {6;4;6;12;18;12;10;3;11;8}, "")

and returns

{6;"";6;12;"";12;"";"";"";""}.

Step 3 - Identify the smallest number in array

The MIN function extracts the minimum value in a cell range or array.

MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))

becomes

MIN({6;"";6;12;"";12;"";"";"";""})

and returns 6.

Step 4 - Compare smallest number to current cell

MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))=A2

becomes

6=A2

becomes

6=6

and returns TRUE. Cell A2 is highlighted.

Download Excel *.xls file

highlight-smallest-duplicate-value-in-a-column.xls