Question: In case of duplicates in sales values, conditional formatting highlights more than 10 values. Is there a way to only get exacly 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)

top-10

Answer: The picture below have exactly 10 values highlighted.

top-10-2

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.
top-10-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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