Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List

Tom’s Tutorials For Excel: Conditional Formatting the Nth Highest and Lowest Items in a List

When you want to highlight more than just the single lowest (minimum) or single highest (maximum) value in a list, here’s how.



In the above picture, three years of quarterly performance are entered in row 7. If you want to highlight, say, the three highest (best performing) and three lowest (worst performing) quarters, the below formulas and pictured Conditional Formatting dialog boxes provide an example, while cells in range A7:L7 were selected.

Formula for the three highest percentages (cells shaded in blue):
=A7>=LARGE($A$7:$L$7,3)

Formula for the three lowest percentages (cells shaded in pink):
=A7<=SMALL($A$7:$L$7,3)

Conditional Formatting dialog box in Excel version 2003.


Conditional Formatting dialog box in Excel version 2010.