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

100 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

      1. it didn’t work for me. does anybody else know how to format row if cell is not empty in a given column?

  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

    1. 1) Highlight J9:J17
      2) Conditional Formatting –> Custom Formula: =J9J8 – make it green

      This will make all cells in your selection which are smaller or equal to J8 red and all cells which are larger than J8 green.

      No need for $ as your example only uses one column

  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

  46. Very helpful article!
    Strangely, this worked for most of my rows but not all of them. Has anyone experienced that?

  47. Any way to to a conditional formatting for a whole row based on data validation drop down? I want to have a row with a client record change color upon changing the value in drop down menu; ie: change row to red if client marked as inactive and change row to green if client marked as a good lead. Any ideas?

  48. Hi and thank for for this – how do i use “text contains” in the custom formula instead of an exact text match? In relation to your first example?

    Thanks!

  49. Hello, I need to use conditional formatting as if the cell is repeated to change its colour, can I do that? if yes, how?

  50. I am trying to put a border on the bottom of every row where column A contains a date that begins with “Sunday.”

    I have searched through how to add a border to a row based on some factors, but can not find an example where the border for the row would be triggered when a certain column cell contains a certain word, but not just that certain word.

  51. Hello brilliant minds!

    I am looking for a formula where one cell will turn one of two colors depending on the info in corresponding cells. Case example: I am using a spreadsheet to collect info for contracting, and on the far left is a status column where someone can indicate they want me to send the offer. If they select “Send Offer” and not all the cells that need values have them, I want that cell and the ones missing data to turn red, so they know there is missing info. If they select “Send Offer” and all the cells that need data have it, then it turns green and the person knows the data is complete and ready to go.

    I’ve seen this in a document before but can’t emulate it. Any takers? 🙂 Thank you!

  52. Hi,

    I want to highlight rows based on the value in a column.
    Say if column H has value apple i need it to be colored red.
    If H has value berry i need it to be colored blue.
    If H has value grapes i need it to be colored green.
    But that all these happen in the same set of cells.
    Also if any of them are changed then I need the colors also to change accordingly.
    Please help me out…….please please please 🙁

  53. How do I do this where I want cells who are marked in a formula to be made a certain color. So I’m adding cells for one forumla that I want marked blue. I’m adding different cells for a second formula tha tI want marked green. Basically what’s the formula for “if in a formula” Thank you!

  54. Hi Ben! Thanks for the useful article.
    I just wanna know – can I somehow apply conditional formatting rules on a whole document? (not only the sheet it was created on).

    Thanks!

  55. Hi Ben!

    Do you also have a solution on how to highlight entire columns? I excel, the formula is =C$4=“Black”
    But how do you do this in google sheets?

    Thank you so much.

  56. Sorry to bother you but I can’t figure out how to use conditional formatting for a row/column cell based on the value of a previous cell.

    Eg. I have weight (numbers) displayed in a row. I would like to highlight cells which if are less as compared to the previous cell as green, red if their value is more and neutral if the value is the same.

    100|99|101|101|85

    So ideally

    100|99 (Green)|101 (Red)|101 (Yellow)|85 (Green)

Leave a Reply

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