I'm trying to mark all the rows of this table:
AB-Table:
To this:
I want to do it via conditions (rules).
How can I do it? (if possible), Or – which rule I should write?
Thank you!
Excel – How to Mark Specific Rows with Conditional Formatting
conditional formattingmicrosoft excel
Related Solutions
Here is an answer with two helper columns (of course you can hide them):
- helper1:
=AGGREGATE(2,5,A2)- it just shows 1 for visible and 0 for invisible rows (of course you always see 1 :) )
- helper2:
=IF(C2=1,IFERROR(MAX($D$1:D1)+(COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0),1),"")MAX($D$1:D1)- looks for greatest group number so farCOUNTIFS($A$1:A1,A2,$C$1:C1,1)- checks whether current value is present in ABOVE VISIBLE rowsMAX(...)+(COUNTIFS(...)=0)- increases group number if it's a new groupIFERROR(...,1)- sets group number to 1 for first visible rowIF(C2=1,...,"")- calculates group number only for visible rows
Setting up conditional formatting:
- go to: Home - conditional formatting - new rule - use a formula...
- in formula enter
=MOD($D1,2)=1 - set your desired formatting
Best Answer
=$F1="A".This will be the result: