Search code examples
excelformulahighlight

how to propagate relative rows in formula?

If I want to apply conditional formatting to highlight the minimal value in a column (33 columns in total) of 4 rows I can achieve it as follows: =A1=MIN(A$1;A$2;A$3;A$4) (May be there is a better solution...)

The problem is that I have 95 such 4-rows combinations. I must do this comparison for rows 1-4, 5-8, 9-12 etc.

01  BAILLY  90  174 221
01  DHL     94  140 169
01  GEODIS  114 140 170
01  STAT    141 155 176
02  BAILLY  62  105 125
02  DHL     84  107 132
02  GEODIS  111 112 129
02  STAT    123 142 160

Is it possible to propagate somehow such a comparison?

Sample data image

Solution

  • Step 1: Apply your conditional formatting formula to 4 cells: A1, A2, A3, A4 without any $ sign (all relative): A1: =A1=min(A1:A4) A2: =A2=min(A1:A4) A3: =A3=min(A1:A4) A4: =A4=min(A1:A4)

    Step 2: Copy range A1:A4

    Step 3: Select the whole range you want to apply conditional formatting and paste as format enter image description here

    The result looks like this: enter image description here