In the example shown, the formula used for conditional formatting is: Note: Excel contain a conditional formatting “preset” that highlights top values. However, using a formula instead provides more flexibility. 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: 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: Any cell with a value greater than or equal to 110 triggers the rule, and the conditional formatting is applied.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
