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

Answer:

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

Conditional formatting formula in A2:

=MIN(IF(COUNTIF(List, List)>1, List, ""))=A2

How to implement conditional formatting formula to your workbook in excel 2007

For example, your range is F3:F9. Change named range List to F3:F9.

  1. Select your range F3:F9.
  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 A2 in the above conditional formatting formula to F3. Your range is F3:F9, remember?.
  7. Copy the above conditional formatting formula to "Format values where this formula is true:"
  8. Click Format button
  9. Select a formatting you like. For example, cells colored yellow.
  10. Click OK
  11. Click OK

Named ranges
List (A2:A11)
What is named ranges?

Download excel sample file for this tutorial.
highlight-smallest-duplicate-value-in-a-column.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

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