I usually prefer written article over videos because most of the people just ramble endlessly before going to the point. But for this particular query, there is no match. There are tons of videos and articles that show you comparing cell values like 20 etc but nothing on = value. Most notably "string" value. Conditional formatting based on another cell's "string value"? There is none except this video. And the creator understands well how and when to slow down the mouse pointer so noobs like me can follow where you're going. Best tutorial in all category. Period.
Thank you! I recently got a promotion to Operations Coordinator at my job, and we work in Google Sheets daily. I was nervous due to the video being 3 years old, but Google Sheets hasn't changed, and you've made my job much easier with your great tutorials. Thanks again!
A six year old video which exactly solved my problem... plus the original video creator is still answering comments. You sir have won the internet today.
I was looking for a training video for a colleague and I found this one. I've been using spreadsheets for more than 20 years. This is the most comprehensive and enjoyable video I've ever seen on the topic and I learned a few things!
Spent an hour tinkering with the sheets help section and watching other videos. I quickly learned that watching anyone but you is a waste of time. Liked and subscribed, thank you for your work in helping educate users on how to use Sheets!
I've seen lots of Google Sheets video but usually don't learn much because they are too fast for someone learning it. This I find is one of the clearest videos I've seen. You should develop and sell a Google Sheets online course if you don't already.
Came here troubleshooting The $ thing to stop the formula from moving a variable was my problem, didn't know about it. God sent video Wish you an excellent day!
EXACTLY what I needed. Thank you, thank you And THANK YOU for just diving right in and solving the problem pronto. You had a clear video name and did that exact thing within the first few minutes. You are a rockstar and your video just saved me from throwing in the towel.....you're a lifesaver!!! THANK YOU!
This is a great video! I have worked for years with Sheets and I was unable to format cells based on other cells, and now it is a piece of cake. Thanks for sharing this great content! Cheers.
I've watched several of your tutorials and I'm not a teacher or a student. Excellent for any person using the program. Thanks for your time and keep up the good work!
After more than an hour with google, your video was the first page that gave the answer I needed. All others missed to specify that custom formula should be preceeded by = to function correctly. Fixed my problem immediately! Great work. Thank you #prolificoaktree
Excellent video! I learned that formatting based on cell B4 would involve highlighting the cells you want based on B4, and creating a custom conditional formatting rule with the syntax: =b4 followed by whatever. As an example, if you want any input into B4 to cause a change in font color, the sytax would look like this: =B4"" The is syntax for not equal, and "" is just double quotes around nothing. Good stuff :)
EXACTLY what I needed. Thanks dude. And THANK YOU for just diving right in and solving the problem pronto. You had a clear video name and did that exact thing within the first few minutes. THANK YOU! I hate it when I know the answer I need will take a few seconds and all the videos I find are 15 minutes long. So thanks again.
Thanks for taking the time to let me know that you liked it and why. I have played around with longer intros but I almost always delete them because they seem boring me when I go back to edit them.
Ah, my issue was the $ missing from the referenced cell. I didn't realize it was moving down from the specified cell every time you drag the Conditional Formatting down a row. The app makes it seem like every cell is still referencing the same cell as listed in the first row! Thanks!
I appreciate your videos, they have helped me. I'm looking for a video that will show me how to automatically enter text, "yes or no" into a new column based on the value of column in that row.
@@ProlificOaktree yep. If you want people to engage with you don't stop them. No one likes the credits on tv shows and YouTibe ads annoy people. Love the fact that you just grey straight to the point. Not many people understand online training like you do - we can pause and rewind if we don't understand something so there is no need to re-explain something. Well done :)
Gosh, you made this so easy! Thanks tons! And you have a great FM radio deejay voice too, so that helps. You're kinda the Bob Ross of Google Docs, right? 🤓
Great, thanks. It solves one part of my challenge - I want a checkbox to appear according to a certain value in another cell... Conditional formatting can only elicit color changes?! ):
Thank you for this video. I taught excel for a number of years and was really good and conditional formatting and functions. However, I could not get it to work well in Google Sheets.
Very Helpful. Just what I wanted, thank you. Is there a formula for other cells in the row to change colour based on cell colour rather than value please? Thank you
Nice video I have question please How can I make the other values in the same row appear .I have one cell in each row get it value from ifttt and nodemcu and I put condition that if the value equals to 1 for example in the same row 2 cells value will appear. Can you tell me how to do it please
Very helpful. So basically: As the conditional formatting evaluation is moving through the range, cell by cell, each cell being evaluated is considered the new relative A1 (first column, first cell) to the formula with B2. Therefore: B2 indicates that the second column, second row (B,2) *relative* to the current cell being examined. A1 through G1, A2 through G2, all the way to A1000 to G1000 (upper max of 1,000 rows examined). However, we can break that *relative* processing. $B2 indicates that regardless of the current cell being examined (A1 through G1000), the formula should always only apply to the sheet's B2 cell, and NOT relative to the current cell being iterated. OP: Does that sound accurate?
I am looking for a solution to this issue I have a spreadsheet with a series of dollar figures in column B. There is a formula in place to add those values together. The issue come with column C, where I need to be able to write "yes" or "no" basically making the items to be added in column b optional. However I cannot figure this out. If you have a video on the subject could you please link me to it? Thanks Andrew
I need help with something.... I have a league table that automatically updates when fixtures are played. I have created a new column beside the team names and inserted images into the cells next to the team names showing their badges. Is there a way to link the two cells so the badge moves with the team name once a fixture has been played??
Can you do this for AND? That is set cell color either in the same column or another column based on two values within the cell. E.g. if the text in the cell is 5 Day Diet Guide Ebook, I need to highlight the cells containing BOTH the words "Diet" & "Ebook". Is that possible?
Yeah, you write it with a similar method but put an AND at the beginning and then write two functions. Try it in an empty cell in the spreadsheet first.
@@ProlificOaktree Thank you for your response. It didn't work, I mean the formula in the video. I tried that on the spreadsheet and it returned FALSE. I typed on a different cell =A5="order", which returned false but that word was in cell A5. And the same for the formatting, nothing happens. What am I doing wrong?? :/
Thank you much. I did this a couple of years ago but just could not remember how I did it. But now I have a new twist. I would like the text to change in the target cell. So with your help the target cell turns red when the reference cell is less than zero. But I would also like the Text to change; i.e. say Amount Due or Over-payment etc. Any suggestions? Thanks in advance.
Love your videos, they have taught me so much! Is there any way to make the color from 'sheet 1' to then match the drop down selection of 'sheet 2'?? TIA!
I am using the google sheets for inventory. I am trying to figure out how to get one column to automatically highlight the next column when it reaches a certain threshold in order to let me know when to reorder that product. EX.....F2 cell is saying 400 pcs. When it gets to 300 pcs, I need G2 cell to automatically highlight to yellow.... Is this possible. I hope I explained it correctly. Thank you.
I wanted to have the cell change colour every day. Fir example today blue, tomorrow green and next day different colour and so. Without having a date put in any column. Is this doable?
Came here looking for a refresher on what formula syntax to use to get conditional formatting based on cells above or below the current row. I know it's possible, because I did it before, but understandably not a common application. Thanks!
If it is a fixed number above or below, then just make the row number that amount higher or lower in the custom formula. However, I have a feeling that I'm missing the crux of your question but I'm not sure.
I have a spreadsheet that gets rows automatically added every day or so. In a column (D) with steadily increasing whole numbers (1,1,2,3,3,3,4,5,6,6,7,7,7 etc.), I wanted the very last (most recent) to be highlighted using conditional formatting, omitting duplicates. In other words, the first "7" of my sample list, not all three "7's". Here's how I got that to happen this morning, after posting my comment to you. I set up a rule that applies to "D7:D1007" (the range should automatically expand as rows beyond 1007 get added), and gave it a "Custom Formula" of =AND(D7=MAX(D:D),D7D6) This formula will only match (that is, become TRUE for and consequently highlight) a single cell in Column D, namely the number that is both bigger than all the others AND not equal to the value in the cell immediately above it. Thanks for heading me off in the right direction!
How can I make an exception? For example if I want to format row 2, A-G but then have row D another color. I added another formatting but it seems as if the first formula overrides it.
Hi. New subscriber here. Im just curious if it is possible to put a automatically put the date today when I click on another cell. Because I use sheets for studying. For example I clicked A2, A1 will show the date when I last edited or click. What I do now is manually putting dates to track when I last studied a specific row. Thank you.
Thanks for subscribing. Glad to have you on board! I have been asked that question multiple times but I don't know if a good way to do it. I'm going to look into it again though and I'll post a video if I can figure anything out.
I am learning a lot with your videos. Thanks a lot sir.... great job. You are the best teacher!!! I want to know if you have a video which explain if the hour that you have scheduled in a cell has passed using the hour of the computer. An example: you had to call somebody at 9:00 am and wrote it in a cell but now the time is 10:00 am. How to use the format to change the color of the cell if the time has gone by.
I usually prefer written article over videos because most of the people just ramble endlessly before going to the point. But for this particular query, there is no match. There are tons of videos and articles that show you comparing cell values like 20 etc but nothing on = value. Most notably "string" value. Conditional formatting based on another cell's "string value"? There is none except this video. And the creator understands well how and when to slow down the mouse pointer so noobs like me can follow where you're going. Best tutorial in all category. Period.
Whew, thanks! I love that this video is still helping people after so long.
Very helpful. Millions of videos on conditional formatting but yours was what I was looking for.
indeed
Excellent, within the first 2 min you taught me exactly what I needed. Bonus, I learned A1:A highlights the whole column. Thanks!
For what it's worth, I learned the A1:A technique for the first time as I was researching this video.
Those birds and nature sounds in the background was oddly soothing and perfect as background noise for something technical like this. Well done.
Thank you! Those are Pennsylvania birds...
Thank you! I recently got a promotion to Operations Coordinator at my job, and we work in Google Sheets daily. I was nervous due to the video being 3 years old, but Google Sheets hasn't changed, and you've made my job much easier with your great tutorials. Thanks again!
Glad it was helpful!
@@ProlificOaktree Hi, how can I make it, so it colors whole row.
Close enough to what I was looking for, to point me in the right direction. Thanks!
A six year old video which exactly solved my problem... plus the original video creator is still answering comments. You sir have won the internet today.
You're observation is so 🔥 bro.
This wasn't *exactly* what I needed, but it definitely helped me make the logic jump to get to what I needed. Thank you!!
You're welcome!
I was looking for a training video for a colleague and I found this one. I've been using spreadsheets for more than 20 years. This is the most comprehensive and enjoyable video I've ever seen on the topic and I learned a few things!
That's a great compliment, thank you!
Really Cool Video. Five Years Later, and still helping!!
Thanks Alex. I still remember making this one. Glad you're still using it.
Spent an hour tinkering with the sheets help section and watching other videos. I quickly learned that watching anyone but you is a waste of time. Liked and subscribed, thank you for your work in helping educate users on how to use Sheets!
That's a great compliment, thanks! It's good to have you around.
I've seen lots of Google Sheets video but usually don't learn much because they are too fast for someone learning it. This I find is one of the clearest videos I've seen. You should develop and sell a Google Sheets online course if you don't already.
Oh, thank you. I have done that and develop was the easy part! Still working on the sell side of things.
When I couldn't figure it out by reading 3 pages of Google search, you did it in 3 minutes. Thanks!
Thanks, I'm glad it helped you out.
Came here troubleshooting
The $ thing to stop the formula from moving a variable was my problem, didn't know about it. God sent video
Wish you an excellent day!
First person to explain the use of $ sign in a way I understood. Didn't even come here for that. Thank you.
Game changer
I tried figuring this out a month or two ago and gave up. Your video finally reveals how! Thank you thank you!
You're welcome! I'm glad it helped you out!
EXACTLY what I needed. Thank you, thank you And THANK YOU for just diving right in and solving the problem pronto. You had a clear video name and did that exact thing within the first few minutes.
You are a rockstar and your video just saved me from throwing in the towel.....you're a lifesaver!!! THANK YOU!
This was the exact answer I was looking for! Thank you very much.
Many thanks!!! I've been spinning and spinning looking for a solution. Thanks again
This is a great video! I have worked for years with Sheets and I was unable to format cells based on other cells, and now it is a piece of cake. Thanks for sharing this great content! Cheers.
Glad it was helpful!
Thank you so much! This solved my issue perfectly!
I've watched several of your tutorials and I'm not a teacher or a student. Excellent for any person using the program. Thanks for your time and keep up the good work!
Hey, thanks! I appreciate the thoughtful feedback.
After more than an hour with google, your video was the first page that gave the answer I needed. All others missed to specify that custom formula should be preceeded by = to function correctly. Fixed my problem immediately! Great work. Thank you #prolificoaktree
it's rare for me to find exactly what i was looking for in the first video, but this was it - awesome, thank you!
Wow, that's a great compliment! Thank you.
Excellent video! I learned that formatting based on cell B4 would involve highlighting the cells you want based on B4, and creating a custom conditional formatting rule with the syntax: =b4 followed by whatever. As an example, if you want any input into B4 to cause a change in font color, the sytax would look like this: =B4"" The is syntax for not equal, and "" is just double quotes around nothing. Good stuff :)
Problem solved and straight to the point video! Thank you!
Glad you liked it!
I use this video EVERY time I need to do conditional formatting. Thank you so much for sharing.
I know what you mean. The whole thing with fixing the column reference is not an obvious solution.
EXACTLY what I needed. Thanks dude. And THANK YOU for just diving right in and solving the problem pronto. You had a clear video name and did that exact thing within the first few minutes. THANK YOU! I hate it when I know the answer I need will take a few seconds and all the videos I find are 15 minutes long. So thanks again.
Thanks for taking the time to let me know that you liked it and why. I have played around with longer intros but I almost always delete them because they seem boring me when I go back to edit them.
Ah, my issue was the $ missing from the referenced cell. I didn't realize it was moving down from the specified cell every time you drag the Conditional Formatting down a row. The app makes it seem like every cell is still referencing the same cell as listed in the first row! Thanks!
Thank you so much for this video, I thought this was only possible in Excel
the perfect answer to the question. Thank You!
You are welcome!
This is EXACTLY what I was looking for! Thanks!!
Boom!
It's so easy when you know how. This has been a great help. Thanks.
Glad I could help out!
thank you for this!! I got the gist within 2 minutes. Diolch!!
Thanks for this video - I was struggling with the syntax to get the custom formula to run across the whole row. Nice video!
Thank you for this to the point video!
:)
Seriously you are a life saver!!!
I appreciate your videos, they have helped me. I'm looking for a video that will show me how to automatically enter text, "yes or no" into a new column based on the value of column in that row.
Thanks for sharing! Exactly what I was looking for.
Thank you sir, very helpful in making my report better.
Very well explained - great stuff. Not many videos are this easy to follow. And thanks for not blowing your own trumpet with pointless graphics.
Thanks, I'm glad you like it. You're making me question the short intro graphics that I have on my newer videos now!
@@ProlificOaktree yep. If you want people to engage with you don't stop them. No one likes the credits on tv shows and YouTibe ads annoy people.
Love the fact that you just grey straight to the point. Not many people understand online training like you do - we can pause and rewind if we don't understand something so there is no need to re-explain something.
Well done :)
I spent a fortune designing up a super cool intro (and outro) for my videos. I loved it - but no one else did.
Gosh, you made this so easy! Thanks tons! And you have a great FM radio deejay voice too, so that helps. You're kinda the Bob Ross of Google Docs, right? 🤓
Ha, I love that compliment! Bob Ross is wonderful.
Love the birds chirping in the background
youre a wizard for guessing why i came to your video haha
wish I could give you more than 1 like. thank you
Great, thanks.
It solves one part of my challenge - I want a checkbox to appear according to a certain value in another cell...
Conditional formatting can only elicit color changes?! ):
This was extremely helpful. Awesome job walking through the steps.
Wow - thank you!! SO helpful, thorough and timely.
Wonderful video, shared with someone that may try it themselves. Learning is all about trying over and over, it till you get it or close enough.
Yeah, just start doing it.
Super helpful video! Thank you for providing thoughtful context and explanation of how to make the custom formulas work for your needs.
Thank you for this video. I taught excel for a number of years and was really good and conditional formatting and functions. However, I could not get it to work well in Google Sheets.
Glad it was helpful!
I like your conditional formatting tutorial on google sheets. Thanks.
I'm glad you liked it, thanks.
wonderful explanation - cheers mate!
This is concise, to the point and useful.
Thanks, glad it worked for you.
Very Helpful. Just what I wanted, thank you. Is there a formula for other cells in the row to change colour based on cell colour rather than value please? Thank you
Not that I'm aware of.
Nice video
I have question please
How can I make the other values in the same row appear .I have one cell in each row get it value from ifttt and nodemcu and I put condition that if the value equals to 1 for example in the same row 2 cells value will appear.
Can you tell me how to do it please
Thank you very much! great feature that you show here
WTF I search for hours till I found your video. TY so much
Thank you. Perfectly explained
Glad it was helpful!
this was extremely helpful, thank you
Glad it was helpful!
Very helpful. So basically:
As the conditional formatting evaluation is moving through the range, cell by cell, each cell being evaluated is considered the new relative A1 (first column, first cell) to the formula with B2.
Therefore:
B2 indicates that the second column, second row (B,2) *relative* to the current cell being examined. A1 through G1, A2 through G2, all the way to A1000 to G1000 (upper max of 1,000 rows examined).
However, we can break that *relative* processing.
$B2 indicates that regardless of the current cell being examined (A1 through G1000), the formula should always only apply to the sheet's B2 cell, and NOT relative to the current cell being iterated.
OP: Does that sound accurate?
Yeah, that sounds like the right concept to me.
Perfect dude. I learned something new about using dollar signs with cell references
Cool. Fixing cells comes in handy a lot so you'll probably be using it often.
Thank you
THANK SIRR, YOU HELP ME A LOTT!
Exactly what I was looking for, perfect video.
Thank you, helped me a lot to know how to compare to one cell in particular
Great video, saved me a ton of time reading support docs.
Cool, glad to hear it.
This was so helpful, clear and simple. Thank you
Thank you so much for taking the time to do this, you're a lifesaver!!!
Well, thanks. I'm glad it helped you out!
AMAZING video!! Hero!! Thank you so much!!
You're welcome!
I am looking for a solution to this issue I have a spreadsheet with a series of dollar figures in column B. There is a formula in place to add those values together. The issue come with column C, where I need to be able to write "yes" or "no" basically making the items to be added in column b optional. However I cannot figure this out. If you have a video on the subject could you please link me to it?
Thanks Andrew
how we can highlights new entry automatic on whole sheet(Mean where ever i want to entry any that should highlight please guide me
Thanks
This helped me. Thank you
Thanks now understood how it works.
Great!
Incredibly well explained! Thank you!
BINGO! Very easy and to the point videos. Keep it up! I love learning something new with excel!
Glad you liked it!
Exactly what I was looking for! Thank you!
The dollar sign is magical!
I need help with something.... I have a league table that automatically updates when fixtures are played. I have created a new column beside the team names and inserted images into the cells next to the team names showing their badges. Is there a way to link the two cells so the badge moves with the team name once a fixture has been played??
Brilliant, helpful. Thank you.
You're welcome!
Exactly what I needed explained. Thank you!
Thanks for the video, very helpful
How do you set the condition to be the text of the adjacent cell *_starting with_* certain characters?
Can you do this for AND? That is set cell color either in the same column or another column based on two values within the cell. E.g. if the text in the cell is 5 Day Diet Guide Ebook, I need to highlight the cells containing BOTH the words "Diet" & "Ebook". Is that possible?
Yeah, you write it with a similar method but put an AND at the beginning and then write two functions. Try it in an empty cell in the spreadsheet first.
@@ProlificOaktree Thank you for your response. It didn't work, I mean the formula in the video. I tried that on the spreadsheet and it returned FALSE. I typed on a different cell =A5="order", which returned false but that word was in cell A5.
And the same for the formatting, nothing happens. What am I doing wrong?? :/
Absolutely brilliant piece of work. Thank you
Great tip, but how do you do that to find if a value does not equal another value. Using it to highlight differences in receiving quantities.
"B3" if it's B3
Thank you much. I did this a couple of years ago but just could not remember how I did it. But now I have a new twist. I would like the text to change in the target cell. So with your help the target cell turns red when the reference cell is less than zero. But I would also like the Text to change; i.e. say Amount Due or Over-payment etc. Any suggestions? Thanks in advance.
Put an IF statement in the cell which displays one value IF the other cell is whatever and another if it's something else.
Love your videos, they have taught me so much! Is there any way to make the color from 'sheet 1' to then match the drop down selection of 'sheet 2'?? TIA!
Thx master!! Gracias MAESTRO!!
You're welcome! Glad you liked it.
I am using the google sheets for inventory. I am trying to figure out how to get one column to automatically highlight the next column when it reaches a certain threshold in order to let me know when to reorder that product. EX.....F2 cell is saying 400 pcs. When it gets to 300 pcs, I need G2 cell to automatically highlight to yellow.... Is this possible. I hope I explained it correctly. Thank you.
I wanted to have the cell change colour every day. Fir example today blue, tomorrow green and next day different colour and so. Without having a date put in any column. Is this doable?
Came here looking for a refresher on what formula syntax to use to get conditional formatting based on cells above or below the current row. I know it's possible, because I did it before, but understandably not a common application. Thanks!
If it is a fixed number above or below, then just make the row number that amount higher or lower in the custom formula. However, I have a feeling that I'm missing the crux of your question but I'm not sure.
I have a spreadsheet that gets rows automatically added every day or so. In a column (D) with steadily increasing whole numbers (1,1,2,3,3,3,4,5,6,6,7,7,7 etc.), I wanted the very last (most recent) to be highlighted using conditional formatting, omitting duplicates. In other words, the first "7" of my sample list, not all three "7's".
Here's how I got that to happen this morning, after posting my comment to you. I set up a rule that applies to "D7:D1007" (the range should automatically expand as rows beyond 1007 get added), and gave it a "Custom Formula" of =AND(D7=MAX(D:D),D7D6)
This formula will only match (that is, become TRUE for and consequently highlight) a single cell in Column D, namely the number that is both bigger than all the others AND not equal to the value in the cell immediately above it. Thanks for heading me off in the right direction!
Exactly what I needed. Thanks!
Excellent work, thanks
Thank you!! Exactly what I was looking for! Explanation was very clear.
Thanks for the kind feedback. It makes it more rewarding for me.
Thanks for a clear concise tutorial!
How can I make an exception? For example if I want to format row 2, A-G but then have row D another color. I added another formatting but it seems as if the first formula overrides it.
You need to do it with two as long as they don't contradict eachother you should be find.
Hi. New subscriber here. Im just curious if it is possible to put a automatically put the date today when I click on another cell. Because I use sheets for studying. For example I clicked A2, A1 will show the date when I last edited or click. What I do now is manually putting dates to track when I last studied a specific row. Thank you.
Thanks for subscribing. Glad to have you on board! I have been asked that question multiple times but I don't know if a good way to do it. I'm going to look into it again though and I'll post a video if I can figure anything out.
Great upload! Thanks
How about the check box, I want to formatting cell A by referring from cell B (check box)
I am learning a lot with your videos. Thanks a lot sir.... great job. You are the best teacher!!! I want to know if you have a video which explain if the hour that you have scheduled in a cell has passed using the hour of the computer. An example: you had to call somebody at 9:00 am and wrote it in a cell but now the time is 10:00 am. How to use the format to change the color of the cell if the time has gone by.
Put this is the custom formula box in the conditional formatting menu =IF([cell_with_time]>NOW())
@@ProlificOaktree waaaaaaaaaaoooooo.... this is fantastic. Thank a lot sir. You are a master.!!!!! It is working now.