Conditional Formatting Formulas - Mystery Solved with 3 Simple Rules
ฝัง
- เผยแพร่เมื่อ 30 ก.ย. 2024
- Conditional Formatting with formulas in Excel can be frustrating, but in this video I uncover how Excel works under the hood and give you 3 simple rules to make writing formulas for conditional formatting foolproof.
View my comprehensive courses: www.myonlinetr...
Connect with me on LinkedIn: / myndatreacy
Spent 20 minutes arguing with Excel over why it wouldn't conditionally format like I wished. Spent 4 minutes watching Mynda, smacked my forehead, apologized to Excel, and now my rows look just as they should.
😂 I'm sure Excel appreciates your apology! Glad I could help.
Yes!
Can you please explain through multiple examples of conditional formatting using AND( ) OR( ) formulas.
I’ll keep it in mind. If you have a specific question you’re welcome to post it in our Excel forum: www.myonlinetraininghub.com/excel-forum
Thanks for this Mynda!! Can’t believe you were able to explain this so succinctly in just over 4 mins. You have cleared up A LOT of confusion I had as to how exactly this conditional formatting works. I will from now on always apply these 3 simple rules. It’s goodbye to the guesswork😁😁. Thanks again!
So pleased you got so much from a 4 minute video :-) Happy Conditional Formatting!
@@MyOnlineTrainingHub I think a great follow up to this video would be to explain how the 'Stop If True' function works and creative ways it can be used.
A great explanation of something so simple but yet I always struggle with. Thank you
So pleased I can help :-)
Absolutely, this video is being bookmarked. Every time I need to do this I have to re-learn it, this will fix that. Thank you!
Great to hear, Julian :-)
I’m a CF fanatic & use it on almost every spreadsheet. 95+% are formula-based. Love that flexibility. But, oh, it was a painful learning curve (3-4 years ago). Hair-pulling is an apt description (except that my hair loss is mostly natural). Great lesson on a very useful feature!
Hope I can prevent people going through that pain :-)
I never really understood conditional formatting with formulas, and consequently found it frustrating and iterative. This video makes it so clear! Thank you for sharing it.
Glad it was helpful, Richard!
Thanks Mynda, you are truly one of the few "Fair Dinkum" Excel sites with down to earth teachings IMHO.
Thanks for your kind words, Dave! It's great to know I am producing useful content :-)
Brief and at the same time to the point, explanative and precise! Your knowledge and command of excel is admirable! Thank you dearly, for sharing it with us dummies!
Thanks for your kind words, Anastasios!
The picture Excel is key for understanding the meaning of conditional formatting! Thank you for your explanation Mynda.
Glad it was helpful, Ivan :-)
An unconditionally wonderful explanation of conditional formatting...thanks!
:-) cheers, Dough!
I had practically given up on formula based conditional formatting. Just too much hassle and didn't make it work half of the time. But a year ago I read about that TRUE/FALSE-way in the sheet and it became simpelt over night. Now I'll rather say I'm over using it. It's just not a "formula friendly enviroment" inside that little box that seems to enter what it wants and all in black characters. So I'm with you. Construct in the SHEET and make the formatting work on the first try.
Great to hear 🙏
Honey, The only thing that hard to understand here is your accent. Thanks for explanation
Everytime you upload a video, that makes my work more easy and I lean a new interested thing everytime.
Thank you, I literally love this channel more than any other.
Wonderful to hear, Sourabh! Please share my channel with your co-workers too.
i had been trying to find the right formula for conditional formatting in a gantt project management spreadsheet all day on friday. 2.51 minutes of your video and I did it.... thank you
So pleased I could help 😊
Good video. Another annoying thing about CF is 'rule creep'. A range or field in a Table that gets new rows or columns often makes a new rule for the new cells. After working for a while in the workbook, the CF dialogue box ends up with dozens of new rules all doing the same darned thing! It's a nightmare to straighten it out.
Rule creep sure is a pain. It happens when you paste into a table range that contains CF. You're effectively pasting over existing CF, so the range with CF applied is now split above and below those pasted cells. My mantra is to always paste special > values when pasting to tables containing CF.
i am relieved i am not the only one who found it messy to use!
Hope my video helped demystify conditional formatting formulas for you.
Im looking for a formula or conditional formatting that highlights a large set of data table based on Purchase Order Numbers eg ( PO124589) > The table has 12 columns and i would like each PO# and the row all to have there own colour. I cant find this anywhere. If you could help that would be amazing.
Thank you.
You could have to set up a separate rule for each PO number, which would be unwieldy to manage. A better option would be to just alternate colour bands when the PO number changes, assuming they are grouped together. If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I am struggling. I made 1 rule if column D ="MTB, then pink, Rule 2: Column F = PCR+, then pink, Column B ="3", then pink, Rule 3: =Search("*transferred*",$R2) then highlight entire rows. So the rules 1, 2, 3 already existed, when add the Rule 3 did the job highlight entire rows as green, but after that the pink colors disappeared from the other rules, and the color has to stay there for future filter about MTB, PCR, etc. What went wrong? How do I move the rule down or up, but what's the purpose to put rule first on last. Does make any difference.
Yes, the order of rules dictates which rules when where multiple rules are true. Try moving the order of the rules to get the desire result. If you're still stuck, you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi - I have watched your videos re: conditional formatting, but haven't been able to solve my problem -- I am attempting to link cells (including cell formatting). When linking cells, in addition to retrieving the contents from the source cell, I want the linked (dependent) cell to also follow the same formatting as the source cell (as the source cell formatting may change). Any suggestions are appreciated. Thank you!
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello,
Could you please help me how to conditional formatting cells with Right function?.
I use this formula and work well « =COUNTIF($C$3:$C$8;B3)>0 » to conditional formatting my cells. But now I want to lookup not the entire data in the cells but only the 3 last digit of the data in the cells. I couldn't found the right way to use "Right" function in a lookup formula.
Thanks in advance...✌👍
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Mynda, this is fantastic! I created a spreadsheet for when my medicines are due to be refilled... and it's set up almost similar as your book due spreadsheet. This conditional formatting would help me to visually see which meds are running out first. TY and I made sure to share your video!
Glad you can make use of it :-)
Very Nice, Mynda. How would you make sure that your Conditional Formatting Retains whether you refresh or Check Uncheck Fields in Pivot Field List and also how do I make sure that my Conditional Formatting remains the same when I create Show Report Filter Pages. I have been trying but somehow it does not retain. Thanks
You can't with either of these scenarios. If you check/uncheck fields in the PivotTable field list you are essentially changing the PivotTable layout and the conditional formatting would need to be reapplied to account for those new fields. And Show Report Filter pages doesn't bring through any formatting.
@@MyOnlineTrainingHub Thank you Mynda! Now, I know that there is no solution so I will not waste more time trying to make it happen.
Hi Mynda. How do you conditional format a range of cells based on a range of other cells. Eg I want my cells formatted when my range of other cells could be R, A, G?
Hi Francis, that depends how your data is structured. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great explanation. What if I want to format based on values in a list regardless of which one matches?
Not sure I follow the example. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Your lesson is incredibly amazing! Thank you so much for this video.
Glad you enjoyed it, Sean!
Yes it does Cleared up some confusion i had.
a very good explanation Thank You !!!
Glad I could help!
Thank you, this was extremely helpful.
Glad to hear it!
you are ..superb
. lov the cute way you speak..❤❤
Thank you so much 😀
Years of confusion vanished in 4 Mins.
Greetings from India.
So pleased to hear that 😊
Thanks! Madam, kindly provide your POWERBI link to access
Not sure what you mean by "Power BI link to access", sorry.
@@MyOnlineTrainingHub Madam, if you are providing session for PowerBI. So, kindly provide to access.
Thanks
I’m stumped trying to remember how I created a sheet for multiple due dates in the same row with conditional formatting. Hopefully, this helps 😊
Hope so too! If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub thank you so much! It’s been years since I’ve used excel as much as I am now, and I’ve forgotten a lot. Your videos are absolutely amazing. Thank you!
I am looking for a way to format some dependent tasks if the trigger is checked done. Is there a way to do that in excel?
Yes, you can do that. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
The third rule was the best. Great stuff Mynda
Glad you liked it!
Thank you, thank you, THANK YOU!! 🙂
Glad I could help 😊
How can I conditional format a whole workbook at once without VBA or Macro ? e.g. to color all cells in a workbook ( No a sheet at a time)n with the same cell value like Apple, Orange,....?
There's no way to do this without using VBA, sorry.
@@MyOnlineTrainingHub thank you
Can we use IF formula in conditional formatting in new rule??
Yes.
Brilliantly useful and succinct. Thank you
Glad you enjoyed it!
I HATE Excel!!!!!!
I do really appreciate your efforts ❤️
Great to hear!
Remember to press F2! I nearly always have an incorrect cell reference in my formula because I've used the arrow keys expecting to be in edit mode.
Interesting fact about numbers not equal to zero. Never knew that, and am now desperately trying to think of somewhere I'd use it. Basically, you're saying that:
=MAX(0,$E$1-$C$2) would give the same conditional formatting result? Interesting....
Indeed, F2 is super handy in dialog boxes like these. You can also use the logical test result 0 for IF formulas ;-)
Wow! Learned something new today about Conditional Formatting, all thanks to this video. 😄 Thankyou very much.
Awesome to hear!
Can you please upload the exercise file please?
There's no file for this video.
Very helpful video. Trying true false version before applying formula is a great way. I just used it and it worked. Very simple explanation of 3 rules. Thank you!
Glad it was helpful! 😊
Hi, I am trying to create a conditional format to highlight cells within a column of dates so that all date entries longer than next month are highlighted with a colour. I am currently using this formula : =AND($E15>TODAY(),$E15>=(TODAY()>30))
I also have similar conditions for last month and this month, and next month, but when trying to use the above formula assuming greater than 30 days it just will not work. So if I have dates for this month (June) then Next month July, I want to highlight all dates beyond July into the future.
I would appreciate some advice.
Please post your question on our Excel forum where you can upload a sample Excel file and we can help you further: www.myonlinetraininghub.com/excel-forum
2:31 ❤ the way you articulated Excel and Your thought process, "Excel"lent. Love your work🙏
Thank you so much 😀
Excellent! Cutting and pasting the formula was what I needed to know - trying to get the format right has been driving me mad! Thank you very much for your very clear and concise video.
Glad it was helpful!
I would add #4 - don't forget to actually pick a format. I can't count the number of times I've entered my formula in the CF dialog and then clicked OK without actually selecting any format options, and then I can't figure out why my text looks the same. :) One of these days I'll learn..... (BTW thank you for your videos. They are all very helpful.)
Yes! I've done that occasionally too :-D
All Australian book titles!
Taken from my book club 😉
Very helpful, thank you
Glad to hear it!
Thanks Very much you are a life saver
Happy to help :-)
Hi Mynda.. great advice.. as always. Thanks for the tips. Thumbs up!!
Cheers, Wayne :-)
Thanks again Mynda. By the way, where can i buy that shirt you're wearing? ... i wanted to show my passion for excel by wearing a shirt just like yours...thanks a lot
Unfortunately, the t-shirts aren't available anymore :-(
Why does it have to be so complex for tho?
Hopefully my video helped you master it.
Loved it, it let us be the best on the basics
Glad I could help 😊
thanks alot Mam for sharing
Most welcome 😊
This is amazing!
Thanks
Glad you like it!
Great one, actually know it and was able to use but the last step. how excel copy my formular across the cells in the background, is very helpfull to explain others.
Thanks, Ricardo! I'm glad you think it's a useful explanation and hopefully it'll help others struggling with Conditional Formatting.
Every tutorial is just so crisp and clear...i love you or excel or both 😂😂....jk
Happy to hear that, Parth!
That clip where you pasted what each cell sees when running a conditional format helped so much. I was spinning my wheels trying to get my format to work, thanks alot
Great to hear it was helpful, Obi!
Hi Mynda!Really Great Tips..Thank You So Much :-)
Cheers, Darryl!
I appreciate it very very much Mynda, you opened my eyes for ever. Was such a pain that why always I forget what to do.
So pleased it was helpful, Farzan!
Thanku u 😊 from India
My pleasure!
Mam pls zoom the video pls
Sorry, Sudhakar, but it's already zoomed in...I'm only showing half of the Excel window. What device are you watching on?
Unable to read screen, kindly zoom and thereafter explained.
It is zoomed in. May I ask what size device you're trying to watch the video on and is this the device you use for Excel?
@@MyOnlineTrainingHub on my mobile (one plus 5), unable to read formula/other cell ref./values when you type there. Also use mobile to read excel file with officesuite app.
Ah, that explains it. I'm trying to find a balance between those who want HD viewing and those viewing on a mobile device, but since most of the tools I'm covering in my videos aren't available in the mobile apps, I'm not really catering for them, sorry.
where is it practice file
No practice file for this video, sorry.
Agree wholeheartedly with Dave Goodmanson! Thanks
Thanks so much, Erica :-)
I do not remember when I had learnt as much as I learnt from your explanation in less than 5 min.
Wow! That's awesome to hear 🙏
Thanks..it was a great Tip
Glad you liked it, Gopi :-)
Thank you very much
You are welcome :-)
Thankyou so much..
My pleasure, Ravi :-)
Thank you for clarifying a confusing subject.
Glad it was helpful :-)
simplified with 3 simple rules, cleared confusion.
Glad it was helpful :-)
the best proper advice on CF, thank you. It will help me a lot.
Great to hear it was helpful, Jamie!
Wow, Mynda! Sorely needed here. No clue here about rule # 2. Have been setting up just one row then using Format painter to replicate the rest. Question: Is there a way to quickly replicate rules? I have custom shades for 3 different indications on a specific row. Doing 3 separate rules for each of 10 different rows gets tedious. Cutting and pasting helps up to a point, but all the clicking is a pain. Any suggestions? Thank you for all of your and Philip's contributions to the Excel community.
Hi Fred, there's no easy way to duplicate a CF rule, sorry. Once your rule is set up, you can change the 'applied to' range to include non-contiguous rows/cells though.
@@MyOnlineTrainingHub Can you not use 'Duplicate Rule' in the CF Rules Manager, and then change the 'Applies to' references, and the Rule details? Seems to work for me.
Interested topic,
Hope you found it useful.
Thank you, could you, please, provide an example of formula usage in built-in conditionals, there is an option to change percentage to number or formula, that part was always mystery for me...
I'll add it to the list :-)
@@MyOnlineTrainingHub I will be very grateful, that part - formula usage instead of percentage and numbers is my personal mystery:)
I like that short simple explanation. Thank you.
Glad it was helpful!
My question is: why does Excel keep adding new ranges for a specified rule to be applied to? For one, when I specify that a rule should apply to a fixed range, I can see, after some time of using the spreadsheet, that there are multiple ranges the rule is ascribed to. This is a mystery to me. BTW: I love your videos, I can learn a lot from them. Thank you for your work.
It happens when you paste into a table range that contains CF. You're effectively pasting over existing CF, so the range with CF applied is now split above and below those pasted cells. My mantra is to always paste special > values when pasting to tables containing CF.
@@MyOnlineTrainingHub Thanks a lot for your kind reply. This may be a possible explanation for some of my CF tables, however, I am quite sure the problem occurs also in those tables where I don't paste any data - they're just typed in.
A very nice clear and concise explanation. Thank you.
Glad you enjoyed it, Dennis!
Very good idea
Glad you liked it, Daniel!
You are one the BEST excel gurus. Thank you.
Thanks so much!
What a gem 💎 you’re, thumbs up 👍🏼 😁
Cheers :-)
Thank you
My pleasure 😊
G.O.A.T👍👍
Thanks so much!
My go to for Excel tips. thank you!
Great to hear!
Hi Minda, thanks for sharing.
I have 2 questions
.
1- I can use the same technique with a pivot table?
And how i can do it with a reference in de same column, let's say apply e conditioning format if all the value in a column's is less than 0..
2- wich program do you use to record and edit your videos ?
Thanks
Hi Eduardo,
1.1 PivotTable conditional formatting works the same way. There are some nuances to it which are explained here: www.myonlinetraininghub.com/conditional-formatting-pivottables
1.2 I recommend you write your formula in a cell first to make sure you're getting the desired result. One way to check that a column contains values that are less than zero would be to =SUM(column)
@@MyOnlineTrainingHub
Thanks for your prompt response, it works fine, but as you say in the article above, every time I apply a filter the range changes.
Yes, CF in PivotTables is only designed for the value fields where it will automatically adjust as the filters change. Best to avoid CF on the row/column labels for this reason.
Great way to explain it and the three rules are super and easy to remember 👍
Glad you think so, Henric!
Thank you for this! I tried to create a new rule for weeks and just gave up in the end. Now I think I'll be able to do it. Thanks a million for your explanation, very helpful!
Great I could help, Mary. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub The new rule worked wonderfully today! I'll have a look at the forum too. Many thanks.
Great to hear, Mary!
SO GOOD
Glad you found it helpful :-)
Well Done Mynda
Cheers, Shakira!
Thanks Mynda!
My pleasure, Luciano!
Thanks Mynda, as always, you hit the nail on the head
Cheers, Hans :-)
🙏 🙏 🙏
Thanks, Nikos :-)
nice job. please share all the excel file.
No Excel file to share this time, sorry.
Great tips thanks for sharing
Glad you think so, Abdul!
Thanks Myndy. : ) : )
My pleasure, John!
Great video!
Cheers, Chris :-)
Thank you
My pleasure!