How to Colour the Entire Row using Conditional Formatting in Google Sheets?
This tutorial is about how to colour the entire row using conditional formatting in Google Sheets? That's right! The secret to using custom formulas in Google Sheets in relation to conditional formatting is using absolute references, or dollar signs. Now that you know this some of you are ready to go off and create conditional formatting perfection with this new found knowledge. Others, well, you might need a little help so here's a little tutorial to give you a hand up.
If you want to see an example of this using checkboxes take a look at the following Google spreadsheet. Check one box, check another then check both and see the colors change.
Conditional Format an Entire Row in Google Sheets Formulas
Red Formula: =AND($E1=TRUE,$F1<>TRUE)
Blue Formula: =AND($F1=TRUE,$E1<>TRUE)
Green Formula: =$G1>0
Formula in column G: =IF(AND(E2=TRUE,F2=TRUE),1,0)
Striking through a column
Say you wanted to add a strike through to a column. For instance, maybe you wanted to add a column before date and have the columns (that are not check boxes as you can't strike through them) struck through.
Conditional Formatting Entire Row in Google Sheets with Multiple Checkboxes
Say you have multiple checkboxes, as was asked by someone on YouTube. I had to scratch my head for this one, and maybe there's a better way but basically I used nested if statements. The results can be seen here:
So I use tracker column with a whopper of an if statement in to cater for 3 check boxes and their variations. As you check each checkbox the entire row changes either to yellow, blue, green or red. There's also a strike option which will strike through anycells in columns A:G that are not checkboxes. First let's look at the formula:
=IF(AND(A2=TRUE,F2<>TRUE,G2<>TRUE),1, IF(AND(F2=TRUE,G2<>TRUE,A2<>TRUE),2, IF(AND(G2=TRUE,F2<>TRUE,A2<>TRUE),3, IF(AND(F2=TRUE,G2=TRUE,A2<>TRUE),4, IF(AND(A2=TRUE,F2<>TRUE,G2=TRUE),5, IF(AND(A2=TRUE,F2=TRUE,G2<>TRUE),6, IF(AND(A2=TRUE,F2=TRUE,G2=TRUE),7,0) ))))))
What's happening here is that number in the tracker column could be 0-7 depending on which checkbox is ticked. Then the conditional formatting formulas check which number is in the tracking column and colour that row either Red, Greed, Blue or yellow. If you check the strike out column you would then have a strike in the colour of the row you checked. So if you checked the checkbox in the Strike column on it's own, it will turn the entire row yellow, if you check Mags and strike then the strike out would be red with a strike. If you checked the Books and the strike column. The row would turn blue with a strike and so on.
To cater for that you can create a number of various rules to check which number is in the tracker column.
