Home » Posts

Highlight 3 smallest values with criteria

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($B5=$E$5,$C5

April 14, 2025 · 1 min · 52 words · Aaron Myers PhD

Table of Contents

Explanation

Inside theAND functionthere are two logical criteria.

Other amounts are now FALSE.

Note: this is an array formula, butdoes notrequire control + shift + enter.

Excel formula: Highlight bottom values

from a set of numeric data.

AND returns TRUEonly if all the conditions are met.

If any conditions are not met, the AND function returns FALSE.

Excel formula: Highlight values greater than

Excel formula: nth smallest value with criteria

Excel SMALL function

Excel AND function

Explanation#

Explanation