6

I would like to alternate the shading of a group of rows each time the text value of a column changes. So all rows with "abc" in column B, will be shaded blue, when the value of column B changes to "def", the rows will be shaded green. The the next time the value of column B changes, the next group of rows will be shaded blue, etc. Seems like it should be easy, but I have not figured it out! I am on a Mac using excel 2008.

4
  • Thank you, but that is not exactly what I am trying to do. I have sorted by the value in column B. So all rows with "abc".are grouped together followed by all rows with "def", then "xyz". So I would just like to easily be able to see the grouping of rows where the values in column be are the same and, using just two colors, alternately delineate the change. So if I have a list of names, address and phone numbers and sort them by everyone that lives in the same city, then I can easily see the groups when the city changes. Jul 9, 2015 at 14:23
  • So regardless of how many cities, you want only two colors, and you want them to alternate whenever the city changes. Am I understanding that correctly?
    – Clif
    Jul 10, 2015 at 23:03
  • I have updated my answer.
    – Clif
    Jul 11, 2015 at 0:05
  • For what it's worth, I've submitted a feature request to Microsoft—requesting an easier way to do it: Highlight groups of rows based on common values
    – User1974
    Jan 18, 2022 at 18:05

2 Answers 2

11

I think that you are looking for something similar to this: enter image description here

Notice that you need to:

  • Use a helper column (which could be hidden),
  • Populate the first cell of the helper column with the number 1,
  • Populate the next cell with the formula =IF(B3=B2,E2,E2+1), and copy down,
  • Choose the "Use a formula to determine which cells to format" option in Conditional Formatting,
  • Use the Rules as shown in the picture,
  • Apply both Rules as shown in the picture.
3
  • Just to add onto this. You can convert all this into a table, make 1 the header for the helper column, populate the formula in the first cell and Excel will auto-populate the formula for you anytime you add a new row.
    – RiverHeart
    Aug 5, 2020 at 17:08
  • Another addition: if you want "every other row" stripes within your "group" stripes, you can do conditional formatting formulas like this: =AND(ISEVEN($A1), ISEVEN(ROW())), =AND(ISEVEN($A1), ISODD(ROW())), etc. Dec 10, 2020 at 16:39
  • For what it's worth, I had to make the conditional formula be =ISEVEN($E1), not =ISEVEN($E2). I don't know enough about conditional formulas to know why the former only worked for me, not the latter.
    – User1974
    Jan 17, 2022 at 22:14
1

If you don't want to use a helper column:

You can use the below in your conditional formatting. Replace $B with the column that has your preferred groups. I have an extra check at the beginning to make sure it's not blank, that way your data can grow.

=AND($A2<>"",ISEVEN(COUNTA(UNIQUE($B$2:$B2))))

color rows by group

Should keep things a little cleaner without the helper column!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .