CODEWITHSUNDEEP
google-sheets
Michael come lately
Michael come lately

Reputation: 9392

Visually group rows with conditional formatting

I have a spreadsheet to which I periodically add some rows. When I do so, I have a date column with the date it was added. I'd like to use conditional formatting to highlight groups of rows where the date is the same.

Date Foo Bar (Row color)
2016-07-13 4 0.2 🟥 Color 1
2016-07-13 12 3.8 🟥 Color 1
2016-07-13 5 3.1 🟥 Color 1
2016-07-29 108 11.5 🟨 Color 2
2016-07-29 25 6.4 🟨 Color 2
2016-08-04 8 0.2 🟩 Color 3
2016-08-04 37 2.3 🟩 Color 3
2016-08-04 3 5.1 🟩 Color 3

Sample screenshot

If it is possible to alternate two colors, that would be acceptable, but the ideal system…

I can make a simple discriminator column with =IF(A3=A2, B2, IF(YEAR(A3)=YEAR(A2), B2+1, 0)), but the only way I know to conveniently assign colors is with the "Color Scale," and it only colors the one cell, not its whole row.

Knowns

Upvotes: 8

Views: 11305

Answers (2)

MattKing
MattKing

Reputation: 7783

Not what you asked of course, but there's another option for visually "grouping" besides colors. Namely a simple pivot table:

enter image description here

Partially collapsed to show sums:

enter image description here

Upvotes: 1

Kishan
Kishan

Reputation: 1810

Alternating colors

If you want two alternating colors, you can set the following two rules (or only one if white is fine for the second color):

    • Apply to range: A2:H

    • Custom formula is:

      =isodd(match($A2,unique($A$2:$A)))

  2. Second rule (if you want another color too):

    • Apply to range: A2:H

    • Custom formula is:

      =iseven(match($A2,unique($A$2:$A)))

More colors

If you want all different colors for all 15 groups, you will need to set 15 rules, like:

=match($A2,unique($A$2:$A))=1
=match($A2,unique($A$2:$A))=2
=match($A2,unique($A$2:$A))=3
=match($A2,unique($A$2:$A))=4

and so on...

Upvotes: 17

Related Questions