Forum Discussion
How define an Excel range in a conditional format with a range that increases
I have a spreadsheet with 1376 rows that is 6 years of daily data and with 2 columns: 1st=dates, 2nd=value. I want to do a conditional format that highlights the max value of the values for each yea...
As Martin_Angosto suggested it's better to transform the data into structural table. That's most reliable way.
If to work with the range rule formula as
=$E2=MAX(IF(YEAR($A2)=YEAR(OFFSET($A$2,0,0,COUNTA($A:$A)-1 )),OFFSET($E$2,0,0,COUNTA($A:$A)-1 ),""))works. Another story how the rule range will be expanded by Excel. It could be not expanded at all, or expanded like
or some other way. Depends on how you add new data and, most probably, on which Excel version you are.
Another way, you may Applies To conditional formatting rule with some gap, e.g. to $A$2:$E$10000, and in formula rule ignore blank rows like
=($E2=MAX(IF(YEAR($A2)=YEAR($A$2:$A$10000),$E$2:$E$10000,"")))*($A2<>"")