How to conditionally format highest value of multiple ranges

1
votes

I'm trying to use conditional formatting to highlight the maximum value over multiple ranges. That is, find the one highest value in said ranges and highlight all instances of it. I have been able to use conditional formatting to highlight the highest number of one column, but not over multiple.

The ranges in question are: G3:G13,J3:J13,M3:M13,P3:P13,S3:S13,V3:V13

Screenshot of the spreadsheet

The versions I have tried either highlight every value in the first row, multiple values but not the highest one, or nothing at all.

An alternative is to use a MAX function and place that value in another cell (the little 4 in the bottom right corner) and use conditional formatting based on that value. However, it's not a particularly elegant solution nor have I been able to make that work properly.

I am using New Google Sheets and am familiar with custom formatting and custom formulas for doing so.

1
google-sheetsformattingmaxgs-conditional-formatting
Found the way to properly format it using the additional cell. In this case: =G3=$V$15 for range G3:G13, J3:J13, M3:M13, P3:P13, S3:S13, V3:V13 or =[first cell in range]=[cell with the value using $] - Esca

1 Answers

4
votes

You need to use Conditional Formatting Custom Formula.

Set Apply to Range

G3:G13,J3:J13,M3:M13,P3:P13,S3:S13,V3:V13

Enter in Custom Formula is

=G3=(max($G$3:$G$13,$J$3:$J$13,$M$3:$M$13,$P$3:$P$13,$S$3:$S$13,$V$3:$V$13))

Pick your formatting style. You may have to scroll down the Format cells if menu to find Custom Formula.