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

12 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

  2. Hi,
    Great and helpful post. Is there a way to set several ‘conditionals for the same column? For instance, I want the entire row to turn green if the text contains ‘go’, or turn red if the text contains ‘stop’ then turn yellow if the text contains ‘caution’.
    Also, would I be able to set all these same conditionals in 4 different columns? the same text is automatically filtered into 4 different columns on the same sheet
    Thank you!

    1. Yes, just add multiple rules. The order of the rules in the sidebar determines the order of precedence (which order they are applied in), although it shouldn’t matter in the case you describe.

      You can use the AND/OR functions in the conditional rule if you need to check multiple columns.

      1. GREAT! Thanks.
        May I ask another question?
        How do I get the entire row to highlight based on *some* of the text in that cell? For instance the information dropped into every cell [generated by AutoCrat] starts with “Document created via… [then there are 4 different word that follow ‘created’ – time, form, manually, starting]. I want the rows to highlight 4 different colors, based on those 4 different words]. Thanks again

  3. Thanks for this Ben.

    I’m having trouble implementing this strategy.

    Are you able to use cell references in your formula as I am trying to do?

    In this case I want to highlight rows only if their values in column C match any of the values in Column AA:AA.

    Example custom formula for range C6:J33 :

    $C6= AA:AA

    Thanks!

  4. This is great stuff. I change the color of the first cell ( contains Date) based on the month…so all the Sept deals are Red, Oct are Green… using this formula =left(A1,1)=”9″ The “9” is the first digit of the date.
    But….when I hit Nov, Dec, Jan…the formula thinks it’s all the same.
    Any suggestions? I just want the first cell to change color.

  5. I use a color scale for a field that contains a %. I would like this color scale to apply either the entire row or just one other cell. I can’t see how to do this.

  6. Hi, How can I highlight an entire row based on if one column is between two dates?
    My column B has dates and I want to highlight the entire row for those rows whose dates (column B) fall between November 19 and December 9. Thanks in advance for your help!

Leave a Reply

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