Conditional Formatting Formulas - Mystery Solved with 3 Simple Rules 

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

The picture Excel is key for understanding the meaning of conditional formatting! Thank you for your explanation Mynda.

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.

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!

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!

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!

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!

Thanks Mynda, you are truly one of the few "Fair Dinkum" Excel sites with down to earth teachings IMHO.

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.

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!

An unconditionally wonderful explanation of conditional formatting...thanks!

A great explanation of something so simple but yet I always struggle with. Thank you

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.

2:31 ❤ the way you articulated Excel and Your thought process, "Excel"lent. Love your work🙏

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!

Your lesson is incredibly amazing! Thank you so much for this video.

Thanks Mynda, as always, you hit the nail on the head

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

Hi Mynda.. great advice.. as always. Thanks for the tips. Thumbs up!!

the best proper advice on CF, thank you. It will help me a lot.

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.

You are one the BEST excel gurus. Thank you.

I like that short simple explanation. Thank you.

A very nice clear and concise explanation. Thank you.

Brilliantly useful and succinct. Thank you

I do really appreciate your efforts ❤️

My go to for Excel tips. thank you!

Loved it, it let us be the best on the basics

Great way to explain it and the three rules are super and easy to remember 👍

Agree wholeheartedly with Dave Goodmanson! Thanks

Yes it does Cleared up some confusion i had. a very good explanation Thank You !!!

Very helpful, thank you

Great tips thanks for sharing

Can you please explain through multiple examples of conditional formatting using AND( ) OR( ) formulas.

Hi Mynda!Really Great Tips..Thank You So Much :-)

Thank you for clarifying a confusing subject.

Thanks Mynda!

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 video!

Thanks..it was a great Tip

Thanks Very much you are a life saver

i am relieved i am not the only one who found it messy to use!

Thank you, thank you, THANK YOU!! 🙂

What a gem 💎 you’re, thumbs up 👍🏼 😁

Very good idea

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.

Thank you very much

Interested topic,

Well Done Mynda

I do not remember when I had learnt as much as I learnt from your explanation in less than 5 min.

thanks alot Mam for sharing

Thankyou so much..

Thanku u 😊 from India

Thanks Myndy. : ) : )

Thank you

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.

SO GOOD

Great explanation. What if I want to format based on values in a list regardless of which one matches?

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

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.

simplified with 3 simple rules, cleared confusion.

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

nice job. please share all the excel file.

G.O.A.T👍👍

Years of confusion vanished in 4 Mins. Greetings from India.

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

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.

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

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

Every tutorial is just so crisp and clear...i love you or excel or both 😂😂....jk

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.

Can we use IF formula in conditional formatting in new rule??

Thanks! Madam, kindly provide your POWERBI link to access

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!

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?

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.

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.

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...✌👍

🙏 🙏 🙏

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

Can you please upload the exercise file please?

All Australian book titles!

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.

Why does it have to be so complex for tho?

Unable to read screen, kindly zoom and thereafter explained.

Mam pls zoom the video pls

where is it practice file

Honey, The only thing that hard to understand here is your accent. Thanks for explanation

I HATE Excel!!!!!!

Thank you