Author: Oscar Cronquist Article last updated on August 12, 2018

Question: In the case of duplicates in sales values, conditional formatting highlights more than 10 values. Is there a way to only get exactly 10 values?

Here is an example picture of 11 rows highlighted, 2 values are the same. (Canada and Brazil).

Here is the conditional formatting formula: =$C4>=LARGE($C$4:$C$18,10)


Answer: The picture below have exactly 10 values highlighted.


Conditional formatting formula: =AND($C21>=LARGE($C$21:$C$35, 10), NOT(AND(IF(COUNT(IF($C$21:$C21=LARGE($C$21:$C$35, 10), 1, ""))>1, 1, 0), $C21=LARGE($C$21:$C$35, 10))))

Download excel sample file for this article.
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

LARGE(array,k) returns the k-th largest row number in this data set.