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)

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.
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.

Related posts:

Highlight duplicates using conditional formatting in excel

Highlight the second or more duplicates in two lists using conditional formatting in excel

Prevent duplicates using dynamic conditional formatting in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2