Note: Excel contain a conditional formatting "preset" that highlights bottom values. However, using a formula instead provides more flexibility.
How this formula works
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 SMALL function, which returns the nth smallest value from a range or array of values. The range appears as the first argument in SMALL, and the value for "n" appears as the second:
In the example, the input value (F2) is 5, so SMALL will return the 5th smallest value in the data, which is 9. The formula then compares each value in the data range with 9, using the less than or equal to operator:
To highlight the highest values in a set of data with conditional formatting, you can use a formula based on the LARGE function . In the example shown, the formula used for conditional formatting is: = B4 >= LARGE ( data , input ) Note: Excel...
Note: Excel contains built-in "presets" for highlighting values above / below / equal to certain values, but if you want more flexibility you can apply conditional formatting with your own formula as explained in this article. If you want to...
To highlight the 3 smallest values that meet specific criteria, you can use an array formula based on the AND and SMALL functions. In the example shown, the formula used for conditional formatting is: = AND ( $B3 = $G$3 , $C3 <= SMALL ( IF (...
The Excel SMALL function returns numeric values based on their position in a list ranked by value. In other words, it can retrive "nth smallest" values - smallest value, 2nd smallest value, 3rd smallest value, etc.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.