No video

Google Sheets - Conditional Formatting Entire Rows | Text or Dates

  Рет қаралды 116,863

Prolific Oaktree

Prolific Oaktree

Күн бұрын

Пікірлер: 166
@SurfingJDawg
@SurfingJDawg 2 жыл бұрын
Well edited, demonstrated, and most of all, superbly explained. The explaination (e.g. at 3:17) of what happens when you put only A2 and don't modify, was very informative. I've struggled to really understand how to highlight an entire row in spreadsheets, and you're video has helped me to finally 'get it'. Thank you for a tremendous video tutorial.
@user-uk9tg5lg9y
@user-uk9tg5lg9y 5 ай бұрын
The second example is EXACTLY what im looking for, thank you so much Sir, God bless you for sharing these knowledge for free.
@ProlificOaktree
@ProlificOaktree 5 ай бұрын
You are very welcome
@swedishguyonyoutube4684
@swedishguyonyoutube4684 2 жыл бұрын
Yes! I love the fact that you showed different iterations of how slight variations would affect the formula. Most tutorials just go "oh, and then you do a bizzlion things, just like this, and this amazing thing happens", but that's not very helpful. Thank you very much!
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
Anytime! Glad it could help you out.
@JewelBeetleLLC
@JewelBeetleLLC Жыл бұрын
Excellent video, I read their instructions several times, and couldn't figure out how to do precisely what you showed us. Fantastic!
@ProlificOaktree
@ProlificOaktree Жыл бұрын
Great to hear!
@chrismalone6651
@chrismalone6651 2 жыл бұрын
Thank you! I had been beating my head against the wall trying to get this to work. Of course, adding the "$" fixed it! Appreciate your video.
@izelennla
@izelennla 5 ай бұрын
Thank you, thank you, thank you!! That worked! I was having issues with date highlighting, so I'm so very glad that you did that second example at the end. I will definitely be watching more of your videos! :D
@ProlificOaktree
@ProlificOaktree 5 ай бұрын
Glad I could help!
@adrianomagno1039
@adrianomagno1039 3 жыл бұрын
thank you so much, you have no idea how you helped me! I wish all the happiness in the world to you and your family!
@kaylamcmillan7458
@kaylamcmillan7458 3 жыл бұрын
You are incredible! I am learning so much from you and it's help my business. Appreciate your brain!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Awesome, thanks!
@BGcomedian
@BGcomedian 2 жыл бұрын
3:20 was what I had trouble before this. Thank you!
@SalisburyAE
@SalisburyAE 3 жыл бұрын
Thank you! I was close but didn't realize I needed to add the quotation marks around the value in the formula - ah, nuance!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
No you've got it!
@ResearchForMyself
@ResearchForMyself 3 жыл бұрын
You are literally the Michael Pollen of technology, Appreciate it!!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Ooh yeah, thanks! I read a book written by him that told me to eat lots of green things.
@ResearchForMyself
@ResearchForMyself 3 жыл бұрын
@@ProlificOaktree Haha yeah np, Im reading his book that just came out few days ago on audible super phenomenal.
@mordonfreeman
@mordonfreeman 3 жыл бұрын
Fast, simple, accurate, Thank you! You save me 30 min of trying.
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Awesome, glad it helped.
@ReeWrayOutdoors
@ReeWrayOutdoors 10 ай бұрын
Fantastic. Solved my dilemma! Thank you sir!
@ProlificOaktree
@ProlificOaktree 10 ай бұрын
Excellent!
@emanuelaelias
@emanuelaelias 2 жыл бұрын
Thanks so miuch. Super simply explained and the examples of what happens if you use a different condition were helpful to see.
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
Glad it helped!
@takiparilimpossivel
@takiparilimpossivel 2 жыл бұрын
The formula itself didn't work for the issue I was having but the logics made me understand what I was doing wrong and I was able to fix the issue. Thank you!
@janjaylangelo4
@janjaylangelo4 22 күн бұрын
thank you! very detailed explantion.
@ProlificOaktree
@ProlificOaktree 22 күн бұрын
Glad it was helpful!
@NigelFairhurst
@NigelFairhurst Жыл бұрын
Good video, very easy to follow
@vianeylujan5380
@vianeylujan5380 Жыл бұрын
Thank you so much for this, I was really struggling and this solved my issues. I feel like a pro now!!!! Thank You!!!
@simoncelt5590
@simoncelt5590 Жыл бұрын
thanks - this helped me solve a formatting conundrum
@ChemoWarrior
@ChemoWarrior 3 жыл бұрын
Outstanding!! My new job log is over the top now!!
@primroseplants
@primroseplants 2 жыл бұрын
Thank you for saving me from many more hours of frustration! Was about to chuck my laptop out the window when I found this video.
@NotaYouTuber69
@NotaYouTuber69 2 жыл бұрын
Thank you so much, I was struggling to figure out this solution by myself. Didn't know about the $!!
@mitchcccdelegencia7280
@mitchcccdelegencia7280 2 жыл бұрын
Your videos are always on point. I always get what I'm looking for. Thank youuuu
@carlpohl1429
@carlpohl1429 Жыл бұрын
Thank you. This video was really helpful :)
@ashuribear3435
@ashuribear3435 6 ай бұрын
wow google sheets is so glitchy thank god for your video
@zubairkhalid685
@zubairkhalid685 2 жыл бұрын
Thank you for solving my problem. I appreciate it.
@origami.gardens
@origami.gardens Жыл бұрын
Very helpful and clearly explained! Thank you!
@kimmitchell9239
@kimmitchell9239 3 жыл бұрын
Thank you, this is a "lifesaver" right now!!!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Glad I could throw you a line.
@NicoleHam
@NicoleHam 3 жыл бұрын
THANK YOU! Building a work tracker and this was a MASSIVE help.
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Don't work too hard. Or maybe do. I don't know.
@e-commercesimplified6850
@e-commercesimplified6850 2 жыл бұрын
Thank You
@marutafrfx8910
@marutafrfx8910 2 жыл бұрын
that's really helpful for building my cool scoreboard and planning things out in a creative way
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
Score! Sorry, couldn't resist.
@ahsan.taqveem1
@ahsan.taqveem1 2 жыл бұрын
Well explained!!
@hai.1820
@hai.1820 2 жыл бұрын
Worked like a charm, thank you!
@jodycrowther4577
@jodycrowther4577 2 жыл бұрын
Many thanks - your instructions in this video were so clear, it helped me figure out how to do this for my purposes in less than one minute! (You even knew where I had gotten stuck, haha!)
@e.c3381
@e.c3381 Жыл бұрын
Thank you so much. This was so exactly what I wanted to achieve. Great tutorial that was well explained
@ProlificOaktree
@ProlificOaktree Жыл бұрын
Thank you, mission accomplished!
@florunsads5693
@florunsads5693 2 жыл бұрын
Thanks so much! Exactly what I was looking for!
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
Great to hear!
@StepanKotok
@StepanKotok 3 жыл бұрын
Great! You save my day!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Glad I could help!
@StepanKotok
@StepanKotok 3 жыл бұрын
@@ProlificOaktree thank you, best wishes
@waqarahmad1651
@waqarahmad1651 3 жыл бұрын
Thank you, you solved my problem i was looking since 3 days this trick
@zip_cs
@zip_cs Жыл бұрын
Very good!
@thesocialera
@thesocialera Жыл бұрын
Thank you for this!
@ProlificOaktree
@ProlificOaktree Жыл бұрын
My pleasure!
@ankitashastri4101
@ankitashastri4101 2 жыл бұрын
It helped a lot, made my work easy, Thank you so much 🙏
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
Glad to hear that
@leighasherman4491
@leighasherman4491 2 жыл бұрын
Exactly what I needed thank you!!
@ramgopalatreya7852
@ramgopalatreya7852 2 жыл бұрын
Nice explanation Thanks
@MichelleAiden
@MichelleAiden 3 жыл бұрын
Great video! You are a wonderful teacher!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Thank you so much!
@peachop100
@peachop100 2 жыл бұрын
Thanks! Great video, short and accurate! Congratulations! Works perfectly and solves many problems!
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
Glad it helped!
@demon562
@demon562 2 жыл бұрын
Thank you!!
@ezeezeji4010
@ezeezeji4010 Жыл бұрын
I need help: Based on a series of dates - I'm looking to highlight a single row based on the closest future date to the dates on my spreadsheet. However, it keeps highlighting ALL the future dates and I just need to know the closest ONE. How can I change it from highlighting ALL future/upcoming dates to just THE one upcoming date? Thank you in advance
@johnairsoftronin
@johnairsoftronin 7 ай бұрын
Thank you.
@ProlificOaktree
@ProlificOaktree 7 ай бұрын
You're welcome!
@ImpalamansGarage
@ImpalamansGarage 3 жыл бұрын
Excellent demonstration. Thank you.
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
My pleasure.
@FirehawkVFX
@FirehawkVFX Жыл бұрын
What if the line containing text is more complicated, and the formula should color if the cell contains the text, but is not equal exactly to the text?
@user-ub3nj3pq2x
@user-ub3nj3pq2x 4 ай бұрын
THANK YOU SIR.
@ProlificOaktree
@ProlificOaktree 4 ай бұрын
Most welcome!
@Maddy130986
@Maddy130986 2 жыл бұрын
Thanks a ton. I tried the same dataset with Sweden and with same range it is not working. It is highlighting the starting range by default.
@ashoksahu9546
@ashoksahu9546 Жыл бұрын
Nice video. Thank you sir
@irenezhen
@irenezhen 2 жыл бұрын
very helpful, thank you so much!
@ramiygr.3629
@ramiygr.3629 3 жыл бұрын
very useful, thank you!
@audiomemento
@audiomemento 3 жыл бұрын
Simple and useful! thanks very much!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Glad it helped!
@umarnurov2459
@umarnurov2459 2 жыл бұрын
Hello, Thank you for the great and well-demonstrated video. I still have some problems with conditional formatting. There is the table of everyday sales within a month and I need to show in seperate figure how much they are selling daily using Today function. For example, today they sell $100 and it should be shown in that figure automatically. I would appreciate if you can help in figuring that out.
@nush727
@nush727 2 жыл бұрын
Thanks so much, this was extremely helpful! Any tips on how to do this for a date range (say between 1/1/2022 and 1/31/2022? I'm trying to highlight areas of my yearly expense tracker by month). Thanks in advance!
@CRISTIOX3000
@CRISTIOX3000 2 жыл бұрын
It anyway you can highlight the road you are on as you hover over it so that I can see an example that I'm on row 45
@negatron5514
@negatron5514 3 жыл бұрын
for some reason, this doesn't work on mine everytime I add the "" .. It's okay with numbers cause I dont' need to put the "", but I'm making another condition for an exact text,
@maciejmelanczuk
@maciejmelanczuk 2 жыл бұрын
@Joshua Coleman good job explaining conditional formatting with ease. I've got a question. If I have a database with columns A: Index, B: name of an animal, C: animal type where column C contains text values f.e. "white cat", "black cat", "grey cat", "blue cat", "purple lion" (so not only cats are on the list) etc. and I want to colour rows with cats except for the "blue cat" and I want to use only one Custom formula instead of 1) "=SEARCH("black cat";$C2)"; 2) =SEARCH("white cat";$D2) ... Any solution?
@chandreshkathiriya4033
@chandreshkathiriya4033 3 жыл бұрын
Thank you, it's helpful
@alexawharton3892
@alexawharton3892 2 жыл бұрын
Instead of using a fixed date, could you change it to "today"? so say anything after today is greyed out?
@DiaborMagics
@DiaborMagics 8 ай бұрын
Hm, I know most of this already but what I can't figure out is how to make a custom formula with as condition "contains a certain text" - I can use the default "contains" option but this will not highlight a row. What I want is if a certain word occurs in column A, then the row in which it occurs gets highlighted. But the the cells contain multiple different words and numbers as text.
@jinjieliu7590
@jinjieliu7590 3 жыл бұрын
thank you so much!
@nssdesigns
@nssdesigns 2 жыл бұрын
Great workaround. Question: I'm working with Color Scale in conditional formatting and it works for percentages from 100 to 0 BUT if all the values are 0% it gives them the high value color AND this at first glance can be perceived as 100% NOT 0%. So I have a sorted column for DRAFT documents that ALL have an initial value of zero that are now being colored as 100%. Any work around for this?
@limibosi1785
@limibosi1785 3 жыл бұрын
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?
@aliamiri4413
@aliamiri4413 Жыл бұрын
It doesn’t work with the new sheet right now. The same exact code but the rectangle is red showing invalid code
@waqarahmad1651
@waqarahmad1651 Жыл бұрын
Sir, please help me, how i can highlight an entire row with 2 diff sheets with specific words
@DrumaticPro
@DrumaticPro 5 ай бұрын
Great video and format; very helpful! But what if the condition is simply any text or number is entered into the reference cell? For example, if a date is entered into H1 the row turns green. (any date, any format, or simply any text)?
@DrumaticPro
@DrumaticPro 5 ай бұрын
Okay, just figured it out lol. I used =$N1>0 ... ">0"!!!! That's it. haha
@ProlificOaktree
@ProlificOaktree 5 ай бұрын
Hey there. I’d use a combination of ISBLANK and NOT…maybe =NOT(ISBLANK(A1))
@vinodprajapat7040
@vinodprajapat7040 2 жыл бұрын
Can you tell me how can i use conditional formatting for text length E.g. if we put mobile number into the cell more than or less than 10 digit than the cell color should be change Pls help
@divyanshisinghal6136
@divyanshisinghal6136 3 жыл бұрын
This was helpful, though it doesn't solve for if data validation is there on a column that we want to apply condition on.
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Use the same logic but fix the row instead of the column.
@waqarahmad1651
@waqarahmad1651 3 жыл бұрын
How we can highlight new entry automatic on whole sheet(Mean where ever i want to entry any data that should highlight please guide me Thanks(without select any specific word)
@kraftyDevilX
@kraftyDevilX 2 жыл бұрын
How can I do this with an Apps Script macro? I will be moving rows to another sheet and I don't want to bring any conditional formatting
@HanhTran-dc8vp
@HanhTran-dc8vp 2 жыл бұрын
How do I copy this conditional formatting (highlight a row based on a cell value "India") onto the same sheet?
@NoorMuhammad-kd3sw
@NoorMuhammad-kd3sw 10 ай бұрын
What formula should I use, If I want to highlight a row and want to find the text that has India in it?
@bagginsbandb
@bagginsbandb 2 жыл бұрын
Is there a conditional formatting formula for date/time if time is between certain hours? For example - If time is between 8AM and 5PM highlight cell green.
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
I would start with this video: kzbin.info/www/bejne/b5e9oaetnNCji5o
@simplychi9418
@simplychi9418 3 жыл бұрын
Are you able to help. I would like to create a study tracker that will highlight the topics in one row red that were last learned (date in another column) 2, 4 then 6 days ago.. is this even possible? Do I need to put learning dates in different columns (I would Like to keep track of how often I study the topic) or should I just update the one row with the last date I studied?
@jordanp8151
@jordanp8151 Жыл бұрын
ty
@aymanshoity1200
@aymanshoity1200 Жыл бұрын
thanks man!!!!!!!!!1
@Shakeel1994
@Shakeel1994 2 жыл бұрын
Hi, How can you do it for "in between 1-100"? thanks.
2 жыл бұрын
I have a list that has values gotten from another neighbouring sheet , what i would like to do is to create a list only on those that have a value greater than cero, but i only want to show those that have a value in that list , i have like 20 diferent rows and i what happens to me now is that i show them all even when their adquired value from the othersheets is 0 , i would rather only make a list for those 20 items on those that have a value greater than cero , is that possible ?
@markvanpetten4956
@markvanpetten4956 3 жыл бұрын
How do I get it to search in a cell for a word that has other words? Example: in cell D2 I would have WWW CANCELED. I have to backspace over the WWW and replace it with Canceled now
@baden271
@baden271 3 жыл бұрын
What if you want to use the color scale? For example, if you want each row to be more green depending on how much revenue there is.
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
There are two tabs near the top of the conditional formatting menu. Choose the one on the right for the color scale options.
@TheJdumoulin
@TheJdumoulin Жыл бұрын
For some reason, this just doesn't work for me. It will just highlight a random row in the range that'll change color even if that specific row doesn't contain what the formula makes true. I can't get it to work at all unless I individual modify every cell I want to change.
@yosuateguhwaskito7830
@yosuateguhwaskito7830 9 ай бұрын
Thank tomaattts lop
@ProlificOaktree
@ProlificOaktree 9 ай бұрын
Huh?
@yosuateguhwaskito7830
@yosuateguhwaskito7830 9 ай бұрын
I mean thank you very much sir 🙏
@agoacweb
@agoacweb 2 жыл бұрын
Is there a way for Google Sheets to highlight the entire row where a cell is selected or mouseover?
@rebeccagould3146
@rebeccagould3146 2 жыл бұрын
I thought this video was very helpful but for some reason my spreadsheet is not correctly coloring the rows even if I do it exactly as you said. Not sure what I am doing wrong
@alpucatsu
@alpucatsu Жыл бұрын
Why does mine say invalid when I put " " on formula?
@nav6175
@nav6175 Жыл бұрын
Is there any way you can copy the conditional formatting to all the workbooks?
@ProlificOaktree
@ProlificOaktree Жыл бұрын
I don't think so.
@noumenondrift2522
@noumenondrift2522 3 жыл бұрын
SO what if i want to highlight a row or column by "TODAY" ???
@alphabeta01
@alphabeta01 3 жыл бұрын
Nice videos. Watch a lot of your videos. Is there a way for e.g. if I need to highlight multiple countries based on text match for instance highlight cell C for countries matching Indonesia, India, Chile, Belarus and Sweden. Formatting purely based on multiple Text matches? Thanks a lot.
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Thanks, I'm glad to hear that you like my videos! I plan to make a lot more just like these ones. As far as what you're asking about, you're probably best off to use the IFS function. It's built to have multiple conditions. You might have to combine it with the OR function.
@rjenkins7361
@rjenkins7361 3 жыл бұрын
You're videos are great, thank you! Question: I use a large spreadsheet everyday with a wide spread of information on it. One of the columns includes dates that are our deadline. Is there a formula I can put in that at one month out, the box will turn red? (3 months from deadline it will turn orange, 6 months out, yellow) So as I get closed to the deadline, I notice the color change. Any help would be greatly appreciated!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Yeah, start by using the TODAY function and adding 30 to it.
@rjenkins7361
@rjenkins7361 3 жыл бұрын
@@ProlificOaktree That would be 30 days from today though wouldn't it? I need the color to change 60 (Yellow), 45 (orange), & 30 (red) days out from specified deadline, showing we're getting closer to our deadline. Thank you again!
@joakimduckworth1482
@joakimduckworth1482 3 жыл бұрын
Thanks for a great video. I learnt a great deal watching it! However, I'm still trying to figure out how to use conditional formatting for my problem: I have a bunch of different dates in column B - these are for when something should be reviewed. If the cell next to that date, in column C, is empty after the specific date in column B, I want that cell to turn red Any advice on how to do this? Thanks!
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Look into the =ISBLANK function.
@joakimduckworth1482
@joakimduckworth1482 3 жыл бұрын
@@ProlificOaktree Thanks! I'll look into it and see what it can do.
@DaddyNesReviews
@DaddyNesReviews 3 жыл бұрын
instead of highlight, are we able to add a conditional formula for math? For example if cell A="name" then it will add up all the cells that meet the condition. Is this possible?
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Yeah, you need to start the formula with =IF( and go from there.
@jeremywp123
@jeremywp123 Жыл бұрын
I'm trying to figure out how to do this with a non exact match, I want to color lines based off if the notes contain "DNC", but this method seems to only work if the only thing in the cell is "DNC"
@ProlificOaktree
@ProlificOaktree Жыл бұрын
This deals with the concept of partial matches. Count Cells That DON'T Contain Certain Text - Google Sheets kzbin.info/www/bejne/fnS2kp97gJiHras
@arletterosario9636
@arletterosario9636 3 жыл бұрын
awesome video, I'm working with a sheet that I want to count every row in increment if condition is met, for example if column A="Name" then counts it, and add the count in the previous column, same rown, if it founds the same "Name" add the number and the counter should say #2, and so on.... I cannot figure it out
@DaddyNesReviews
@DaddyNesReviews 3 жыл бұрын
I am trying to figure out the same thing!! please help
@ProlificOaktree
@ProlificOaktree 3 жыл бұрын
Try out the COUNTIFS function. It should do what you need.
@cmirivalry
@cmirivalry 2 жыл бұрын
how about if you have 500 rows? is it possible for the cursor to toggle in to highlighted one
@ProlificOaktree
@ProlificOaktree 2 жыл бұрын
No, not with this technique.
@zaroonbilal4986
@zaroonbilal4986 3 жыл бұрын
Is there any way we can format the column??
@GuillermoRetamar
@GuillermoRetamar 3 жыл бұрын
Great.
Google Sheets - Conditional Formatting
15:01
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 106 М.
5 Google Sheets Tips Every User Should Know!
12:02
Simpletivity
Рет қаралды 671 М.
Little brothers couldn't stay calm when they noticed a bin lorry #shorts
00:32
Fabiosa Best Lifehacks
Рет қаралды 21 МЛН
КАКУЮ ДВЕРЬ ВЫБРАТЬ? 😂 #Shorts
00:45
НУБАСТЕР
Рет қаралды 3,5 МЛН
а ты любишь париться?
00:41
KATYA KLON LIFE
Рет қаралды 3,6 МЛН
5 Excel Secrets You'll Be Embarrassed You Didn't Know
17:32
Excel Campus - Jon
Рет қаралды 170 М.
Google Sheets - Conditional Formatting Based on Another Cell
5:15
Prolific Oaktree
Рет қаралды 615 М.
Google Sheets - Highlight Expiration or Due Dates
10:02
Prolific Oaktree
Рет қаралды 106 М.
Make Your Google Sheets Look PRO in Under 10 Minutes!
9:25
Next Generation Teacher
Рет қаралды 345 М.
Google Sheets - Highlight Expiration or Due Dates
6:10
Hash Ali
Рет қаралды 30 М.
Conditional Formatting based on another cell | Google Sheets
5:51
Work Smarter Not Harder
Рет қаралды 71 М.
Google Sheets BEATS Excel with THESE 10 Features!
16:31
Leila Gharani
Рет қаралды 515 М.
Advanced Conditional Formatting in Google Sheets
38:51
Ben Collins
Рет қаралды 20 М.
Advanced Conditional Formatting - Google Sheets - Use Formulas, Cell References
22:24
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 155 М.
Little brothers couldn't stay calm when they noticed a bin lorry #shorts
00:32
Fabiosa Best Lifehacks
Рет қаралды 21 МЛН