Search code examples
excelconditional-formattingmaxifs

Excel: Highlight row that has max and min values in other columns

I have a simple Excel table that I want to highlight the row that has max in columns A,B, D and min in C. In this example it should highlight row 1

    A       B       C       D
1   36.4%   35.9%   2.25    272.8
2   19.4%   9.0%    6.5     122
3   17.5%   20.2%   3.2     248.5
4   6.4%    11.9%   16      137.3
5   2.7%    9.8%    23      108.2

All criteria must match, so if the max in one column is not on the same row as the max in the other column etc.., then it's not a match, hence it's possible no row is highlighted. I've tried max, maxifs, large but not having any luck.

Solution

  • Just do CF rule based on formula:

    =AND(MAX($A$1:$A$5)=$A1;MAX($B$1:$B$5)=$B1;MAX($D$1:$D$5)=$D1;MIN($C$1:$C$5)=$C1)

    enter image description here

    enter image description here