How to apply conditional formatting across an entire row in Google Sheets

Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.

For example, you could use it to apply background colors to cells based on the value in the cell.

You can go further than this though, and apply the formatting across an entire row, based on the value in a single cell.

For example, if the continent is “Africa” in column C, you can apply the background formatting to the entire row (as shown by 1 and 2):

Conditional formatting across an entire row

Five steps to apply conditional formatting across an entire row

It’s actually relatively straightforward once you know the technique using the $ sign (Step 5).

Step 1. Highlight the data range you want to format

The first step is to highlight the range of data that you want to apply your conditional formatting to. In this case, I’ve selected A2:C13:

Conditional Formatting step 1

Step 2. Choose Format > Conditional formatting… in the top menu

Open the conditional format editing side-pane, shown in this image, by choosing Format > Conditional formatting… from the top menu:

Conditional Formatting menu

Step 3. Click on “Cell is not empty” in the sidebar

Google Sheets will default to applying the “Cell is not empty” rule, but we don’t want this here.

Click on the “Cell is not empty” to open the drop-down menu:

Conditional Formatting sidebar

Step 4. Choose “Custom formula is” at the end of the drop-down list

Scroll down to the end of the items in the drop-down list and choose “Custom formula is”. This will add a new input box in the Format cells if section of your editor:

Conditional Formatting custom formula

Step 5. Enter your formula, using the $ sign to lock your column reference

In this example, I want to highlight all the rows of data that have “West” in column A. In this new input box, enter the custom formula:

Conditional formatting across an entire row

The key point to understand is that you lock the column you want to base your conditional formatting on by adding a $ (dollar sign) to the column reference.

I start inputting the first cell of my highlighted range: = A2

Then I add the $ (dollar sign) in front of the A only: = $A2

Then I add the test condition, in this case whether the cell equals “West”: = $A2 = "West"

As the conditional formatting test is applied across each row, the value from the first cell in column A is used in the check.

More examples of conditional formatting across an entire row

Based on a threshold value

This is a super useful application of this technique, to dynamically highlight rows of data in your tables where a value exceeds some threshold.

In this example, I’ve highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:

= $C2 < 60

Conditional formatting across an entire row

Based on checkboxes

If you haven't heard of or used checkboxes yet you're missing out. They're relatively new to Google Sheets (mid 2018) and are super useful. Read more about them here.

When a checkbox is selected it has the value TRUE, and when it is not selected the cell has the value FALSE. So we can use that property in our custom formula:

= $B2 = TRUE

Conditional formatting across an entire row with checkboxes

5 thoughts on “How to apply conditional formatting across an entire row in Google Sheets”

  1. Hi, is there a way that in step 3 it could be “Cell is not empty”? like i want to if a cell in a column is not empty the whole row is conditioned…. hope that makes sense, thank you

Leave a Reply

Your email address will not be published. Required fields are marked *