Highlight rows in Google Sheets with conditional formatting

I use conditional formatting for individual cells quite a bit in Google Sheets, but there are times when it would be nice if I could highlight the entire row depending on the contents of a cell in that row. I found out it is possible! For this example, I’ll be using this spreadsheet:

Screen Shot 2016-01-21 at 6.28.38 AM

I would like it to highlight in red the students who have not turned in their forms. To start, go to Format and select Conditional Formatting… The Conditional format rules panel will display on the right side of the screen:

ConditionalFormat

Next, the range to format needs to be set. My data is in columns A through C, so I’ll set Apply to range to A:C. Now to set the Format cells if… In the dropdown, select Custom Formula is. Our custom formula will be the range we want to check and what we want to highlight. This is where it gets tricky. In this example, our formula would be =$C:$C=”N”. Why is that? Let’s break it down:

  • = : All formulas start with an equals sign.
  • $C:$C : The range we want to compare with “N”. The $ denotes that we want absolute addressing, not relative. This allows us to compare row by row.
  • =”N” : We want to compare the contents of the  row and format if it is an N.

Finally, we set the formatting style. Our completed conditional formatting would like like this:

FinalFormat

And the spreadsheet now looks like this:

FinalSpreadsheet

Pretty cool!

Start the discussion at discuss.eduk8.me