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

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

      1. Hi, I’m learning how to use the “Tasks control” sheet and I need D row and G row to turn, for example, blue, if the text in D row says “not started” and G row contains a date “before today”, how can I do that? Can it be done?:(

  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

        1. You’d probably want to make use of the SEARCH(search_for, text_to_search, [starting_at]) function. If it’s the third cell in the row that potentially contains the text you’re looking for, your custom formula might look something like this:

          =SEARCH(“text to search for”, $C2)

          1. I did this and it is only highlighting the one cell. I used =SEARCH(“cancellation”,$C1) and it’s only highlighting the cell and not the row. Any thoughts?

  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!

    1. Hey Ajay,

      You can do this with Conditional Formatting > Custom formula option and then use a formula like this:

      =$A1=""

      where A is the column you’re checking for blanks (can change).

      Cheers,
      Ben

  7. What if I want to base the row conditions on a cells data equalling 0?

    I tried this, but the blank cells would inherit the conditional formatting as well as the cells containing 0.

    1. Hey Jon,

      One way you could do this is to check for a number in that cell, as well as the 0 check, something like:

      =and(isnumber($c1),$C1=0)

      Or you could check for not blank, like this:

      =and($C1=0,$c1<>"")

      This assumes your conditional column is column C, and you’re starting on row 1, so you’d need to adjust them.

      Hope that helps!

      Cheers,Ben

  8. Ben,

    Is this type of conditional formatting able to be done if a particular column is filled in with a color?

    For example, I have football teams in both column C and D. Then in columns F-J people have picked their winners. If I manually go thru and highlight the winning team in either C or D, can I use conditional formatting to automatically highlight when people correctly selected the winner, in their respective columns?

    Thanks,
    Josh

  9. Hey Ben,

    I was wondering if you had a trick that allows a row to be completely highlighted if one cell in that row has a specific date?

    For example, I have ten rows, but only one has a date that is important and I want that whole row to be highlighted.

    Of course, I want more than one row highlighted, but for an example.

    1. Yes, you can do that. For example, this formula will format whole row if the date in B2 is today’s date:

      =$B2=today()

      whereas this one does a specific date:

      =$B2=date(2019,3,19)

      You can adjust the column from B to match whatever you’re using for dates.

      Hope that helps!

      Ben

      1. Hi Ben,

        Understand that if want to highlight the whole row the formula is this: =$B2=today()
        If i want to highlight whole row for dates before today, what is the formula? Thank you!

        Rgds,
        Lee

  10. I have applied the correct formula, however it’s not working/updating.

    I have 9 rows (A-J) the row that dictates the formula is D. The formula I have entered is =$D20=“Pending”

    Is this correct?

    If so, would you be able to explain why it is not working on google sheets.

    Thanks,
    Jessica

    1. Hey Jessica,

      This should work if your data starts on row 20. Otherwise you should change the 20 to your start row e.g. $D2.

      Hope that helps.

      Ben

  11. Is there a way to make the row fill color ‘permanent’? I need to shade certain rows but want to be able to use the ‘fill handle’. Thoughts?

  12. Is there a way to change the color of a title cell if any of the check boxes in that row have been checked(true). For instance Cell “C14 D14 E14” (all one cell) turn green as long as any of the boxes in F14:N14 are checked(true)? Also a way to make them red (false) until something in that row is also checked. Thanks!

  13. I have a value at the row J8 of a number, for example, let’s say 50, then from J9 to J17 I have values that range from below 50 to above 50. If a value is above 50 I want the cell painted green and if the value is below 50 i want the cell painted red.

    I have been trying to get this to work with the offered drop down for Less than or Greater or equal than, but I dont get the cell to refresh with the proper color, and was hoping you could show me other ways to do it, with a custom formula.

    Thanks,

    Gilbert

  14. Hi Ben,

    Very good work. My question, I think complicated, I have a lot of information in columns; ID, Date, Target, Server, Operator.
    I want to highlight an entire row, every time the ID, Target and server are the same (to avoid duplicates if another operator takes the same ID in other date). It might be possible?

    Thanks.

  15. Hi Ben, I have set the custom formula to check against 3 columns all matching and then apply highlight. This works when I apply the format but then if I delete the data and import different data it stops working. I have a script to extract data from emails, if the data matches in 3 of the 5 columns I want to highlight it. The code I am using is below, any thoughts?

    =COUNTIFS($B$2:$B, $B2:$B, $C$2:$C, $C2:$C,$D$2:$D, $D2:$D)>1

    Regards,

    Neil

  16. Hi Ben,

    I’m having a little trouble sussing out a formula to highlight a row within a table if the cell in column G has ANY text / numbers in it – this way the row is highlighted upon completion to aid with my dyslexia. If the cell is blank, I need it to remain blank.

    Can you help please.

    1. Hi James,

      Try a formula like

      =$G2<>""

      where you should change the number 2 to match the first row where you apply the conditional formatting. This rule will highlight all the rows where G is not blank.

      Hope that helps!

      Ben

  17. Great article! Is there a way to highlight the row if the information entered in the cell matches the information from a different column? Basically, I would like the row to get highlighted if the infomation I just entered in the cell is found in anywhere in a different column.

    Thank you in advance!
    Fernando

  18. Hi Ben,
    thanks for this article! very useful!
    But I still need your help because I’m facing an issue about conditional formatting for Google Sheet.
    I need to apply/replicate a customized conditional formatting from another sheet and I can’t figure out how to do it.

    When I copy the rule to another cell it creates a new rule for that cell and also creates a cell range that I don’t want to.

    My formula now is:

    =SUMPRODUCT(–(ISBLANK(INDIRECT(“Q1!AZ24:BF24”))=FALSE)) > 0

    What I need is that this formula replicates to other cells changing for the range automatically.

    (“Q1!AZ25:BF25”))
    (“Q1!AZ26:BF26”))
    …………..

    Any idea how to solve that? I’ve been reading a lot but for now my brain can’t figure out how to solve it.

    Thanks!

  19. Hi Ben

    Can you help me with conditional formatting?

    For example
    Column A:D, I want to highlight the entire ROW when there’s more than one same value on Column A

    Thanks,
    JR

  20. Hi Ben,

    I am just wondering if the upper() formula works inside the conditional formatting?
    I am using this formula =UPPER($C$4:$C) but it won’t apply. Thank you.

  21. Hi Ben!

    I have a gantt chart and I’m trying to show coverage in main headers for the gantt chart when there is a different color cell. So for example,

    Row D5:BD7 and I want to highlight row 5 when there is a colored cell in rows 6 and 7. So let’s assume that row 5 will be orange, and there is orange colored in G6:P6, AM6:AQ6 and there is orange colored I7:M7, R7:Y7, AG7:BC7.

    How do I put in conditional formatting to make this happen in row 5?

    Thanks!

  22. Hello,

    Is there a way to format it so that instead of changing the color of the row, I could actually lock the row with a certain input?

    I’m trying to make it so if I write “approved” in a specific column that whole row will be locked.

    I also am often using filters to change the order of the content and there’s constantly new information being added so I could just lock for example rows x-z.

    Thanks,
    Elia Rae

  23. I am trying to use the conditional formula = $I2 >0. It’s not working because I2 is also a formula (=if( (D7 + 100) <= today(), "0", "1"))

    Does the data in I2 need to be a number?

  24. Hey all,
    this is a helpful start, but, does anyone know how to do this if
    1) first Column is last names (A-Z)
    2) I want to distribute different last names to 6 different users
    (i.e. A-E (User 1), F-L (User 2) …etc…)

    I started by getting the Last name row to have it’s own conditional formatting to highlight based on “in between”

    Now i want to highlight across THOSE row.

    Any ideas?

  25. Hello Ben! I’m a researcher at a university and need to have a column with IP addresses to keep track to make sure the same ones do not show up twice, (to make sure someone isn’t double dipping on a survey) or if they do, to highlight them. Is this possible? Thank you!

  26. Can you do do two conditions, for example in your example, if it is West and the value is greater than 500 highlight the entire row

    1. Try this:
      Apply to range: A3:D20

      Format cells if… : Custom formula is =AND(exact(“West”,$A3),$C3>500)

    2. Try the following:

      Apply to range: A3:D

      Format cells if…:
      Custom formula is =AND(exact(“West”,$B3),$D3>500)

  27. How about if the vlookup of a cell against a table of results isn’t found? For example the following works if the value is found (applied to V2:V).

    =$V2=vlookup($V2,X:X,1,0)

    But this doesn’t work to highlight values that aren’t found

    =$V2vlookup($V2,X:X,1,0)

    Side question, but do you happen to know why it requires the row designation? Seems like it would make more sense if it were
    =V:V=vlookup($V:V,X:X,1,0). Does the row reference always have to be the first row of the conditional formatting range? In this case if I change v2 to v3, it doesn’t work right.

    Thanks!

  28. Hi,

    Ben, Wanted to put that if a value is within 3% of the cell value of the adjacent cell it should be highlighted in green. I have put the following formula: =and(isnumber($D3),0.97*$E3<$D3<1.03*$E3). However it does not work and it also does not indicate that the formula is invalid. Would appreciate your help in this.

    Regards,

    Ashwin

    1. Your formula is close. You just need to break up the second expression into two:

      = AND( isnumber($C3), (0.97*$D3)<$C3, $C3<(1.03*$D3))

      It appears that google calc does not support multiple equality operators in a single condition expression.

  29. Hi Ben,

    As a non-developer, this is a great article to help me get started with conditional rules. However, I’ve been looking do so some more complex formatting and I can’t find any articles anywhere to help me figure it out.

    I’m looking to conditionally format the cells of one column based on the value of cells in another column. For example, in a budgeting sheet: I want to highlight all values in column B that are greater than their corresponding values in column A.

    Any suggestions?

    Thanks!
    Greg

  30. Hi Ben-

    I like to apply the conditional formatting of a row (turn colour upon checkbox via =COUNTIF($A$1, “=TRUE”) but want to apply that to an entire sheet without having to type this for every single row- how would I do that please? Thanks a lot- B

  31. Hi!

    I was able to apply conditional formatting on a specific data using this custom formula: =$D:$D=”2019-02-25″

    Do you have any suggestions how I can add another date into the formula?

    1. You can use the OR operator, so in your case it would be something like this:

      =OR(D:D=”2019-02-25″, D:D=”2019-04-03″)

  32. Hi Ben, great article! Thanks for sharing. I have one question, which is proving difficult for me, but likely easy for you.

    I need to hide an entire row based on conditional formatting. For example, if the date in column 3 falls within the next 14 days I want that entire row to be visible, but if it’s prior to today or more than 14 days in the future, I’d like the entire row hidden. Can that be accomplished? Thanks again!

  33. Thanks for the article,
    My use case is one that I’m surprised there is not a built in option.
    I would like the frozen cells in the row (my current example has 2 frozen columns), highlighted when any cell in the row is active. Indeed highlighting the entire row would be ok too!
    I’ve seen https://www.thesmallman.com/blog/2016/9/15/auto-highlight-the-active-row-when-a-cell-is-selected on hwo to do in excel but I can;t get working in sheets. Does a anyone have any tips?

  34. Hi, can a formula be used to create a condition for the checkbox where it shows and hides specific columns, if checked?

    1. Not the way you’re thinking but you can make it so when a given checkbox is checked (when the cell value is TRUE) then you can set the foreground and background colors to the same color for a given range (or specific columns within that range).

    1. Select column D, go to Format -> Number -> Plaintext. Then select the part of the spreadsheet that includes the text you are looking for in column D, spread out to the width that you want to highlight horizontally. Go to Format -> Conditional formatting… The range you selected should already be indicated in the Apply to range input. Under Format rules, change the dropdown to Custom formula is, and then type the following:

      =REGEXMATCH($D1, “^14\.”)

      where the “1” in “$D1” is the starting row referenced in the Apply to range. That should do it.

  35. Hello,

    This isn’t working for me and I’m not sure why.

    I want a row to use gray text if the entry in column B is the same as any previous entries. I set the Apply to range as A3:G and set custom formula as =countif($B$3:$B3,B3)>1

    Only column A is being conditionally formatted – not the whole row. What am I doing wrong?

  36. can i use conditional formatting to say the number 2 = ‘west’.
    right now I know how to say ‘west’ is marked with the colour green, but i want the number 2 in collum a4 to make the word west appear in collum a5

  37. I have two columns I’m trying to format similar to what you’ve shown. But I’m looking to have them highlight only when two specific conditions in each column are met.

    For example, If the first column says “Yes”, there will be no highlighting, and if it says “No”, that is the first check mark that it needs to be considered for highlighting.

    Then if the 2nd column has choices for numbers 1-4, and only if the number is “3”, then both columns will highlight. If it’s any other number, neither column highlights.

    Is what I’m describing possible with Google Sheets? Let me know if I need to clarify anything.

    Thank you!

  38. Hi Ben and repliers!
    I want to check a cell against a master cell within that same row, and highlight the cell if it matches.
    So, instead of using a custom formula in conditional formatting to look for one value (=$B1=”YES”) I want it to search for an exact match in the text (but not change the value of the cell, just the color).
    Basically: “if the text value in the cell B1 is exactly the same as this cell, change the color of it.”
    Is that possible? Thanks!

  39. Really interesting formulas for my conditional formatting. Is it possible to high an entire row, based from the content of a cell. Sample cell is “4/4/2019 Total”. I want all cells that contains the word “Total” will be highlighted the row.

  40. What if I want the row to be hidden?
    For example, if =$A=TRUE , then hide row
    If this is possible, what about clearing the row first??

  41. How would I conditional format my rows to offset color. I just want my rows to go dark, light, dark, light without having to highlight ever other row to manually change the color. Any advise on this?

  42. Hi,

    Is there any way where i have five columns and the formula should check if all the five rows are entered. Even if any one column is not entered it should not allow to enter the entire row.

    Thanks in advance.

  43. How do you check between two values:

    For example I tried this:

    =AND($I3>J3, $I3<J4)

    Basically the conditional formatting should check to make sure that the value in Column I is greater than J3 but less than J4. But it doesn't work. Does anyone know how to get this to work – between two values?

  44. I have a spread sheet that in column A4:A34 date/day is linked to a month in a merged row 1,2/column B and a year in row 2 column CP. I want to highlight rows that have Saturday and Sunday in them. I want the rows to move with the weekends. I know it needs to be conditional but unsure of what the formatting would look like. Please help.

    Thank you

  45. hi
    if i wish to colour any cell that has a number greater than 0 after todays date – how would I do that – the dates are in A and the numbers are in GHJK

Leave a Reply

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