Forum Discussion
Conditional formatting for 3 rows at a time
Hello - I'm trying to add color every 3 rows in excel. I have a ROW for service and 3 environments per each service that I would like highlighted for ease of reading. The screenshot below shows an example. Row for Account has Prod, DR, Cert and I would like them highlighted in a light color. Then 3 rows for ACH which can either be left alone or highlighted in a different color. Repeat down.
- You can use a custom formula in conditional formatting (new rule -> 'Use a formula to determine which cells to format') by selecting the entire range let's assume that A5:Z100 then select conditional formatting then use a formula then enter:
=OR($A3:$A5 = "Account")
then choose your highlighting
The keys here are the $A means always look at column A and the 3 and 5 without the $ before each means move down rows accordingly. So when excel is looking at row 7 it will check values in A5,A6,A7. Also that formula must be relative to the most upper left corner of the range so you can't use A1:Z100 because the formula would have to use negative rows which isn't possible.
As for the other colors just create another rule accordingly.
Lastly I would say I do NOT like this arrangement because you clearly have filter options but if you filter by column A you will not get all 3 rows and if you sort by A you will also lose rows. So I would recommend you have "Account" listed on all 3 rows and the same for each of the others. (and in that case the formula could just look at $A5 and if you started on row 1 you can still look at $A1 and it would still be valid)
- You can use a custom formula in conditional formatting (new rule -> 'Use a formula to determine which cells to format') by selecting the entire range let's assume that A5:Z100 then select conditional formatting then use a formula then enter:
=OR($A3:$A5 = "Account")
then choose your highlighting
The keys here are the $A means always look at column A and the 3 and 5 without the $ before each means move down rows accordingly. So when excel is looking at row 7 it will check values in A5,A6,A7. Also that formula must be relative to the most upper left corner of the range so you can't use A1:Z100 because the formula would have to use negative rows which isn't possible.
As for the other colors just create another rule accordingly.
Lastly I would say I do NOT like this arrangement because you clearly have filter options but if you filter by column A you will not get all 3 rows and if you sort by A you will also lose rows. So I would recommend you have "Account" listed on all 3 rows and the same for each of the others. (and in that case the formula could just look at $A5 and if you started on row 1 you can still look at $A1 and it would still be valid)- I see your point about the filter options (and they are definitely used in this spreadsheet). I will re-think this before proceeding. Thank you.
