Search code examples
excelrowhighlight

highlight rows for different values in excel

I have

enter image description here

I want to highlight each row that has a different value to the one above it in a different colour. Makes it easier to view all the category 3, for example, especially when I have lots of columns, and it is easier than filtering on each value to view

enter image description here

Solution

  • You can use an extra column to determine what rows to highlight:

    id category highlight
    1 1 =IF(B2=B1,C1,IF(ISTEXT(C1),1,C1+1))
    2 2 =IF(B3=B2,C2,IF(ISTEXT(C2),1,C2+1))
    3 3 =IF(B4=B3,C3,IF(ISTEXT(C3),1,C3+1))
    4 3 =IF(B5=B4,C4,IF(ISTEXT(C4),1,C4+1))
    5 4 =IF(B6=B5,C5,IF(ISTEXT(C5),1,C5+1))
    6 4 =IF(B7=B6,C6,IF(ISTEXT(C6),1,C6+1))
    7 4 =IF(B8=B7,C7,IF(ISTEXT(C7),1,C7+1))
    8 6 =IF(B9=B8,C8,IF(ISTEXT(C8),1,C8+1))
    9 6 =IF(B10=B9,C9,IF(ISTEXT(C9),1,C9+1))
    10 6 =IF(B11=B10,C10,IF(ISTEXT(C10),1,C10+1))
    11 3 =IF(B12=B11,C11,IF(ISTEXT(C11),1,C11+1))
    12 3 =IF(B13=B12,C12,IF(ISTEXT(C12),1,C12+1))
    13 3 =IF(B14=B13,C13,IF(ISTEXT(C13),1,C13+1))

    Then you can use conditional formatting with this formula:

    =ISEVEN($C1)