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):

## 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`

:

### 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:

### Step 3. Choose “Custom formula is” rule

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:

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:

### Step 4. 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:

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`

### 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`

Can you conditionally format the rows that do NOT contain “West” in column A?

This is regarding your first example.

Hi Sue,

Yes, you can do this by changing the formula to:

`= $A2 <> "West"`

Cheers,

Ben

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?:(

Hi,

I think this formula works good:

=AND($D1=”not started”,$G1<TODAY())

What about multiple text? (eg, if there was a table of data with North, South, East, West in column A and you want only North and East conditionally formatted?

Thanks!

Use the OR() syntax e.g.

=OR($D1=”north”, $D1=”east”)

What if I want to change want happens in the row, based on activity happening in individual cells in the same column?

And example would be and order called in, data is entered and the row turns yellow

Then the ordered is pulled, data is entered and the row turns green

Then the order is complete, data is entered, so the row turns another color

Is that possible?

This is brilliant, thanks so much Ben, you’ve saved me a headache and a quarter!

You’re welcome! Thanks, Marty.

I just want to thank you for this. Normally I don’t spend time…but now I feel to do ๐

you save me alot of time!

Regards!!

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

Just use a null field (I think):

=$A2$= “”

Basically if it’s not empty, format the row.

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

It should work as =$a2=””

This doesn’t work for me. Any other suggestions?

Maybe try selecting the cell and pressing backspace? I sometimes have to retype things in order for the conditional formatting I added after it was typed to take hold.

This just does not work for me. I want – if O2 is not empty, highlight row.

=$o2$=”’

doesn’t work

You need to use

`=$O2=""`

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!

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.

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

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)

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?

Currently looking for this exact thing! If there are any answers, please update soon.

The SEARCH command should, I got it to work just fine.

Perhaps you were not applying it to the entire range. If that still doesn’t work you could always try

=REGEXEXTRACT($C1,”.*(cancellation).*”)=”cancellation”

it is a roundabout way of getting there but also worked for me.

Oddly, neither of these are working for me:

range: A1:Z359,

custom formula is:

SEARCH(โexpensifyโ, $E1)

OR

REGEXEXTRACT($E1,โ.*(expensify).*โ)=โexpensifyโ

Any clues as to what I might be doing wrong?

I think the problem with the SEARCH function is that it’s returning the starting position where “cancellation” is found in the cell. I used the MID function in my custom formula to do what you wanted and it worked great. Try using this: =MID($C1,1,12)=”cancellation”

where ,1, is the position in the cell to start searching and ,12 is the exact length of what you’re looking for.

Is there a way to formulate a row so that only one box can be checked in that row?

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!

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.

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.

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!

I want to highlight an entire row if a cell is blank (data not entered)?

Can you help

Thanks in advance

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

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.

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

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

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.

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

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

I have this same question. I have a list of coupons and want to cross out the entire line when the date in B2 is before today. I would also like to remove the expired item after two days.

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

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

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?

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!

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

Gilbert did you get this to work?

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

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.

I want to highlight entire columns where a cell can be found with text starting with “Sat”. Any ideas?

I am also looking for an answer for this but unable to find online. Any help or answer so far?

This isn’t the same formula that is presented above but it basically achieves the same thing that you want.

=SEARCH(“Sat”,$A1)

The set up is the same. Highlight the table you want to format. Use a custom formula. $A1 represents the column that is being searched to find what is between the “”

I hope this helps.

Hi Jeremy, Felipe, Jason

I’m also trying to highlight a column based on the text in the header row. Any luck finding a solution?

I found it!

Custom formula is =C$4=โBlackโ

$ after the letter, not before. SO simple

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

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.

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

Hey Ben,

Using this formula (modified to fit my doc) in Google Sheets is applying it to the row above the cell with characters in the column.

So to clarify, formula is now =$L2″”, characters are in cell L11, row 10 is now highlighted. (including L10)

Is there something I missed?

Try removing your header column from your formula.

“A1:H30” – this gave me the same issue you were expereincing

“A2:H30” – this fixed the issue.

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

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!

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

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.

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!

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

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?

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?

How do you replicate the conditional format to additional rows

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!

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

I have this question as well

Try this:

Apply to range: A3:D20

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

Try the following:

Apply to range: A3:D

Format cells if…:

Custom formula is =AND(exact(โWestโ,$B3),$D3>500)

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!

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

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.

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

Try the following:

Apply to range: B3:B20

Format cells if…: Custom formula is =$B3=$A2

I have a similar question, basically: I want to format column U if it does not equal the corresponding row in column T .

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

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?

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″)

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!

How do I highlight the entire row if I choose text from different columns.

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?

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

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).

Banging my head against the wall.

How can I simply highlight a row if text in column D starts with “14.”?

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.

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?

Thanks Ben

This was my custom formula and it worked for me :

=$A1=”>”

regards

S

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

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!

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!

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.

thank you very much Ben!

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??

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?

Hi Junior,

You can use the Alternating Colors option under the Format menu to do just that ๐

Cheers,

Ben

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.

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?

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

Select the A4 till CP34, then

Format > Conditional formatting > Custom formula is > =weekday($A4, 2) > 5

Set the formatting is prefered

This should work

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

Very helpful article!

Strangely, this worked for most of my rows but not all of them. Has anyone experienced that?

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?

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!

I would like to know the same

I would also like to know if this is simply not possible, so I can stop searching. Thanks!

I was looking for the same. Figured out you can with the search function in the custom formula is section. If you’re looking to highlight any row that contains the letter “X” in column A:

=search(“X”,$A2)

Just make sure to set the range for how far you want to highlight in the the row.

Example: Range set to “A2:E100” to highlight the row as far as column E

Hope this helps

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

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.

Hi Marti,

Unfortunately you can’t control the borders with conditional formatting. You would have to write some Apps Script code to do this, using the Border class (https://developers.google.com/apps-script/reference/slides/border). Maybe I can do this as a future blog post…

Cheers,

Ben

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!

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 ๐

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!

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!

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.

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)

Hello, I need the same help as Marcus. How can I format a cell to get colored only if the value in it is bigger than another cell? For example, turn J4 yellow if its value is bigger than I4.

Than yhou very much for your help.

Hi Ben

You seem very clued up, this might be a long shot but let’s see if you can help me:

I created a price list with all my suppliers prices so all I do is input the quantity and it works out the prices and so on…quite a normal thing, so what I’m trying to do is for the row that has a quantity in it I would like to copy that whole (horizontal) row into the next sheet so when I look at the next sheet I can see only the rows that I need to see for that specific quote, summing it all up after the last row in the new sheet.

This is probably an easy one (I hope) this way I can just print the next sheet and insert it in the client’s file which will only be 1 sheet of paper and not 10 with lots of useless entries.

Let me know if you don’t understand

TIA

Claudio

I want to use conditional formatting in google sheet such that if I write – P KRCA 10-07 and hit enter, the data in same cell should now appear as P KRCA 10-07 21-07 (added 11 days to initial date).

Similarly if I write CC KRCA 03-07 and hit enter, the data in same cell should now appear as CC KRCA 03-07 01-08 (added 30 days to initial date).

Thanks

This is exactly what I needed. Many thanks, Ben.

I am writing a form for educational lessons plans. IF I have the SAME lesson standards for a consecutive day, I want to skip having to re-answer questions. I have a column in SHEETS to give a true/false logical answer from whether or not the lesson is a “repeat” or not of the previous day.

If the conditional formatting is “true” is there a way to have cell information copied from specific cells in the previous line ?

Hello there,

I would like to know how to apply two conditional formattting rules simultaneously. Currently i have two two applied on the same range of data, but the cells where first rules holds true, the second rule is not getting applied.

Thanks.

If column C content is YES or NO, is there a conditional format that I can use to get a number in column D, like: If C8=YES, then D8=1?

Hi! Thank you fore this very clear explanation as to how to highlight whole rows based on the value in one cell in that row. It was very helpful!

I got it to work and highlight the row based on the value of one cell being less than a certain cost, but I can’t figure out how to high light the row based on the cell being between two costs or in a range of two costs.

Ex: Worked = Highlighted row based on the cost in one cell being less than $25,000.

Ex: Didn’t Work = Highlighted row based on the cost in one cell being between $25,001 – $50,000.

Any advice?! Thanks again!

Could you, please, advise how to use more than one word in the formula, e.g. “West”, “East” etc.

I tried “West|East”, “West;East” but it didn’t work.

Thank you in advance!

Assuming you’re looking in column B, try this:

`=OR($B1="West",$B1="East")`

It worked! Thank you for your advice.

how to —->

when i strike through a sentence, then the box will change color to blue.

I have a list with one column having either Yes or No. I want to color the rows with No in gray. When I use this formula (=$D2=”No”), it is selecting random rows, some with Yes and some with No and leaving some rows with No un-grayed.

Any idea what is throwing off the formatting?

Hey Scott – check that the range you’re highlighting for the conditional formatting is starting on row 2… if it’s not then it throws off your highlighting. You can adjust the $D2 to $D3 or $D4 or whatever you need to match your start row.

How do I paint the entire row of the active cell?

For example, clicking on a cell would paint the entire row.

Hi,

I have a table that I want to color all Blank cells only if the first cell on the row (A1, A2,A3…) is not empty. (meaning I don’t want to apply the condition for blank cells for the whole sheet, because then the whole sheet is colored although I didn’t write anything in the row).

Hi

How can i color the whole row only if i have 9 checkboxed true ?

for example A2:I2 is all true, then apply the CF , is there is any emptu just leave it unpaint ?

Thanks!

This was a huge help! I’ve actually wondered for years how to do this, and just quietly accepted that it probably wasn’t supported. Your step-by-step tutorial was amazing.

Great! You’re welcome ๐

Hi Ben,

I read all but i could not figured out,

I have 2 tables in one google sheets,

e.g 1st table: B2:E10

2nd :I2:L10

When i put number or any datas on B2, on opposite I2 has to be highlighted. If i fill the C5, J5 has to be higligted.

How can i do this, it should be something regarding with =$G2><"" formula which you mentioned above before but i could not find it,

Many thanks in advance,

I’m trying to make rows change when a value changes, but I want all the same values grouped together.

It’s a list of addresses, so based on the building number, the format should change

So all building 3 should be shaded then all building 5 should be unshaded then all building 6 shaded then 7 unshaded, etc; I can’t do odd/even because some of the numbers don’t exist, so there might be two odds or evens next to each other…

Alternatively when the building number changes I could do a line, but I don’t see lines as a format option

Hi

I have rows with Date, Time, Email1, Email2, Email3

I want to highlight the email if the email is used more than once for the dame date and time. Is this possible?

Hello!

I want to highlight a row if one column is empty but other columns in that row are filled. Can I do this?

Thanks for the tip!

Hi, is there a way to filter rows that contains multiple specific texts? like for example, I want to filter texts that contain “.ru”, “.uk” “.eu” etc.. so that I can select/filter them out on my list whenever I wanted to.

hi how can i condition a entire column to color depending on the value of another column which it is aligned to? example current qty of stock will turn red if the restock point inputed beside it is a certain number?

Can you conditionally format the rows that do NOT contain “anything” in column A?

Hi. I’m managing a google sheet attendance record and I want to conditionally format each student (1 row) to highlight three absences in a row. Now the absences can be coded N, V, I, C which are all unexcused and any combination of 3 these in a row would need to be highlighted. Thanks!

It works well. Thanks for sharing =)

What if I want rows B and C to change when D is changed to cancelled? I can get row D to change but not the others. (rows 15-29).

Hi,

I read all but i could not figured out,

I have 2 tables in one google sheets,

e.g 1st table: B2:E10

2nd :I2:L10

When i put number or any datas on B2, on opposite I2 has to be highlighted. If i fill the C5, J5 has to be higligted.

How can i do this, it should be something regarding with =$G2><"" formula which you mentioned above before but i could not find it,

Many thanks in advance,

I have a Gantt chart with a task bar highlighted based on a date range. However, I’m trying to also highlight the same area based on a date range of the days completed. When I add a second condition on the range with the adjusted date range nothing happens. Any ideas?? Here is my text:

TASK

Range:

N8:ABP1002

Formula:

=and(N$7>=$F8,N$7=$F8,N$7<=$H8)

Hi,

I’ve been trying to conditionally format based off a percentage that I manually input, specifically in terms of investment rates. For some reason, I con’y get the appropriate row to highlight based off of what rate percentage I’ve input.

See my Google Sheets link with more details:

https://docs.google.com/spreadsheets/d/1I77UfKwCeqEf7ecKij4PKyweWbD-tvgCdipK3HEJqvY/edit?usp=sharing

Any suggestions?

I would like to create a new row if a particular text is selected in a column.. Is that option possible in google sheets.

Hi ben, thanks,

what I don’t get yet, from both the article and “field-test” is how (and why) does this apply to an entire row.

I replicate the same example and when the checkbox checked, the entire range changes i’s color…

I want a row to change color if a checkbox is checked (TRUE) in a certain column. I have done everything but it only will change the color of the cell and not the row.

What am I doing wrong?

Choose Range

Format rule “is equal to”

TRUE

Style – changed color

HELP

Ben, all super helpful tips, thank you.

Is there a way to format all NEW rows in a particular column with 2 decimal spaces?

And all NEW rows in a particular column to format the #s as currency?

I could not figure out a way to do that.

Can anyone help?

Gina

This is great. Thank you!

This is a great post, Ben!

I am having a problem with a conditional formula on a to-do sheet. My goal is to have the row be shaded and crossed out if I have marked it as done.

However, as you can see in this screenshot, row 129 is not styled even though there’s an x in F129 and row 134 is styled even though there is nothing in the F134:

https://www.dropbox.com/s/krqhxg32r9qwyof/gtd%20to%20do%20system%20%E2%80%94%20sheets%202019-12-16%2010-38-44.png?dl=0

I have tried removing all conditional formatting and starting over, but it doesn’t work.

I look forward to your recommendations!

Thanks,

Josh

I have 2 columns that I’m trying to keep the duplicates between both columns. Those duplicates in each of those columns I need to keep the adjacent cells in each of the rows to those columns. How do I eliminate the cells that don’t have duplicates in both those columns along with those adjacent cells in those rows??

Hi Ben,

This is a great post.

I am having a case like i want to use in between color heat map on the basis of percentage color condition over actual value.

Example:

A

Jan-500

Feb-600

Mar-300

Apr-650 (Base Month)

Condition is:

– Condition 1: If Apr-Jan difference percentage is in-between (-10% to 10%) then the actual table which mentions above shows RED color.

– Condition 2: If Apr-Jan difference percentage is in-between (11% to

30%) & (-30% to -11%) shows Ambur color.

– Condition 3: If Apr-Jan difference percentage is in-between (-100% to

-31%) & (31% to 100%) shows Green color.

After meeting with the above condition we will apply to all the tables.

If you can solve this on google app script also it will work for me.

Please Ben or anyone help me to resolve this issue.

Regards,

Rahul

This is very helpful! I have a Google Sheet with 3 columns of checkboxes and I’m trying to program it so that if all 3 columns in the same row are checked (=True) the entire row turns green. I’ve made that work for just 1 check box, but now I’m struggling with the “AND” function. Any help?

=COUNTIF($G1, “=TRUE”) is what I used for just one checkbox, how should I convert it to use AND?

Hi Ben, thanks so much for your skills, teaching and insights. I am new to Google Sheets and have a problem I am hoping you can help me with. I need to set up formatting for rows with certificate dates. I am trying to use conditional formatting to notify me of when a certificate is out of date, ie being notified when a certificate is 11 mths out of date – (giving me 1 mth to chase the person) to ensure their certificate is always less than 12 mths old. I am able to put in a fixed date ie 12 mths from today, but it needs to be 12 mths from the date of the certificate… I do hope this makes sense. Thanks in advance. Debra

Hey! Thanks for sharing the info! But I got 1 quick question.

I wanna use “Conditional Formatting” to highlight specific information on my sheet. My question is, is it possible to use “Conditional Formatting” and have it act like a “Vlookup/Hlookup” formula?

A Reply would be highly appreciated

How about this:

If column O is a date more than 1 year old, color green.

If column N has a value, color white

If J has a value, color white.

How do I get the white rules to override the green?

To put it another way, I want to see the people who owe dues. Col O is 2019 dues, col N is 2020 dues, col J is no dues.

Hi Ben

Thanks for sharing!

I was also wondering how to disable the ability to fill in a certain colomn if another specific colomn is not filled in yet.

Is this possible?

I can’t figure out a good way to ask this without presenting the example, so here goes.

On my spreadsheet, I want columns B and C to turn a certain color if the value in column E is one of two specific values. This is the formula to change an individual row:

Range: B5:C5

Formula: =OR($E5=”x”,$E5=”-“)

I’ve already figured out how to apply multiple ranges to a single formula, so is there a variable I can insert into the formula in place of the row number so that it will automatically match the row of the the range? Like, say:

Range: B5:C5,B6:C6,B7:C7,etc.

Formula: =OR($E#=”x”,$E#=”-“), where # represents a variable to auto-fill the row of the range into the formula.

Nevermind, I figured it out. For anyone else who needs the information….

Range: B:B,C:C

Formula: =OR($E:$E=”x”,$E:$E=”-“)

Hi Ben,

I am looking to add conditional formatting to an entire column that will colour the cells if the value in the cell is greater than the value in the cell to the left of it. Is there a way to do this all at once, or do I have to add the conditional formatting to each individual cell?

Here is an example of what I mean:

Column 1 Column 2

Row 1 23 35

Row 2 54 51

Row 3 35 56

Row 4 63 66

Row 5 14 10

In this sheet, the 35, 56, and 66 in Column 2 would all be coloured as their value is greater than the value of the number in Column 1 of the same row.

I am hoping that there is a faster way to do this than conditionally formatting each cell individually.

Thanks!

Lisa

This was exactly what I was looking for. Excellent content and great presentation. Thank you very much!

I am looking to have cells C:Q highlight red if they are less than the number in column B. I know how to do it for a single row (=C20:Q20<$B$20), but I have several rows I need to do this for. Do I have to go through one by one changing the row number in the formula or is there a quicker way?

Hi Ben, great content here. Can you help me further?

I’m trying to apply the following to 164 consecutive rows: format if the cell has the lowest value in its row.

I can do that manually, row per row, but I’m wondering if I can do that to the entire spreadsheet at once. Thanks!

Say I have A1:A10 formatted to turn Red if A11=0. How can is put that on rows B – Z without having to set each row up.

When I copy & paste or drag I get the other rows looking at A11. Want them to look at their corresponding 11.