Explanation
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don’t want to use named ranges, make sure you use absolute references for both of these ranges in the formula.
This formula is based on the LARGE function , which returns the nth largest value from a range or array of values. The range appears as the first argument in LARGE, and the value for “n” appears as the second:
LARGE(data,input)
In the example, the input value (F2) is 5, so LARGE will return the 5th largest value in the data, which is 110. The formula then compares each value in the data range with 110, using the greater than or equal to operator:
=B4>=LARGE(data,input)
=B4>=110
Any cell with a value greater than or equal to 110 triggers the rule, and the conditional formatting is applied.
Explanation
The COUNTIF function counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the rule.
Conditional formatting is evaluated for each cell that is applied to. When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but A1 is fully relative. So, the rule is evaluated for each of the 40 cells in A1:D10, and A1 will be updated to a new address 40 times (once per cell) while $A$1:$D$10 remains unchanged.
Named ranges for a cleaner syntax
Another way to lock references is to a named range since named ranges are automatically absolute. For example, if you name the range A1:D10 “data”, you can rewrite the rule with a cleaner syntax like so:
=COUNTIF(data,A1)=1
