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.
The example below uses checkboxes to trigger different row colours. Tick one checkbox, another checkbox, or both, and the conditional formatting rules change the colour of the whole row.
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 and a tracker column to handle the different checkbox combinations.
The tracker column uses a single formula to cater for three checkboxes 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 any cells 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 the number in the tracker column can be 0-7 depending on which checkboxes are ticked. The conditional formatting formulas then check that number and colour the row either red, green, blue or yellow. If you check the strike column on its own, the entire row turns yellow. If you check Mags and strike, the row turns red with a strike. If you check Books and strike, the row turns 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.
