Conditional Formatting with Two Conditions - Excel Tip
HTML-код
- Опубликовано: 2 июл 2024
- This video tutorial will show you how to use Conditional Formatting with two conditions. In the video will write a formula to test two columns and then apply the Conditional Formatting to an entire row.
Learn all the essential skills of Excel inc formulas, PivotTables, Charts and Macros - bit.ly/UltimateExcel
Using the skills learnt in this tutorial you will know how to test multiple conditions in a Conditional Formatting rule, beyond just two conditions.
Download the file used in the video - www.computergaga.com/_excel/f...
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
Too good, thank you so much!
PS if specific gridlines can be automatically highlighted (for example 0 in a chart containing positive and negative numbers) that would be awesome if you also show.
Thank you.
Thanks a lot, for your informative video.
It was interesting formula. I am waiting for your next video to use condition formatting. Thx a lot.
Thanks, saved me hours of playing around :)
Fantastic! You're welcome.
Nice! With this video I figured out how to highlight a cell that matches a row and column value in a matrix using dropdown selectors. Huge help, thank you!
Awesome! Happy I could help.
Perfect! And it was the first video I found too! Must be my day! Thanks for the tutorial Mate! I subscribed and saved this in my tech tutorials folder.
Awesome, thank you!
this video has helped to have a thorough understanding on conditional formatting feature compared to other videos of tutorial.Thank you so much!
Thank you 😊
I was able to figure out my data science assignment by watching this video!! Much thanks and new sub here!!!!
Welcome aboard! Thanks, Clarence.
I have been searching for this method for months now. And now I found it.
Three cheers to you sir.
Awesome! I am very happy to be able to help Arvind.
Right on, this helped me accomplish my goal! Cheers from Los Angeles.
Glad it helped! 👍
This was a huge time saver for me :) Thank you for the tutorial!
Glad it helped! My pleasure.
Thank you very much. Explained very nicely in short and simple way.
Thank you Vishal.
You helped me with providing the hint about way of thinking, thanks !!
Excellent! Thank you Kamil.
Hello !
I know you posted this video a while ago, but I'm dropping a comment to thank you :). I just used your video on a report at work and your tips were very helpful. All the best and thanks a lot for sharing this !
You're very welcome. Thank you Laetitia.
Thank you, great explanation!
You're welcome 👍 Thank you.
Thank you very much for this!
You're very welcome, Jonathan.
Thank you sir. You helped me so much in my home work.
Happy to help.
Excellent, thanks muchly.
My pleasure Neil.
Great Tutorial. Thanks
You're very welcome. Thank you.
Thanks Alan very useful tutorial
Thanks Mohideen
Thank you! Time to impress the office again :D
Excellent!!
I’ll apply this to my lottery spreadsheets tonight. Awesome!!
Thank you 👍
Fantastic
Thank you so much 😀
so helpful
Glad it was helpful!
thank you!
You're welcome Brent.
Thank you bro really helped
My pleasure Vinamr.
Thank you
You're welcome Dwight.
First of all, your videos have been so helpful so thank you! Secondly, question. If I wanted to do conditional formatting with two conditions and one of those conditions is not a value it's either blank or not blank (but the content will vary), I thought I would put "*" to reference if there is or isn't something in the cell. Is there something else I would need to put instead?
You can check if a cell is blank by either using B2="" or ISBLANK(B2). If you find not blank easier to handle you could use B2"" or NOT(ISBLANK(B2))
Thank you for your kind comments.
@@Computergaga Fantastic! I got it to work with B2"" . Thanks, again!
Nice video.
Could you clarify, what is the problem with Formula:
=and($F2
Thank you, Stella.
The formula you show looks great, assuming you have dates in column F and the strings in column I.
When you say it does not recognise it, what do you mean exactly? What does Excel do?
Maybe the issue is with the double quotations around done i.e., "Done". Ensure they are double quotations and not two single ones. Also ensure they are not copied from somewhere. Type them directly into the formula. Quotations copied from apps like Word do not work.
This is good, but if I want the highlighted records to appear at the beginning of the table, how we can do it ...? Thank you
Thank you. You can sort the rows based on the cell fill colour.
Is it possible to be a dynamic formula..? thanks...
can we apply different conditional formatting for data in rows and columns?
Sure. I see no reason why not.
@@Computergaga I meant data in a table , so different formatting for rows and different for columns, if the data in rows has an increasing or decreasing trend the icon arrows show accordingly and same for columns . Tried it due to the ref works weirdly.
Hi - How would i do multiple colour? ie Platinum can be green, Gold can be yellow and silver can be blue?
You can repeat the process 2 more times Johnnie. You can have unlimited Conditional Formatting rules per cell.
How to conditional formatting by comparing dates on 2 different columns & with conditions on 3rd column?
Depends on the conditions you need etc. But it sounds like you need the AND function. I have another vidoe on this topic here, which hopefully helps - ruclips.net/video/5_b-nyHFg8w/видео.html
if in a sheet having three coloum 1st containing easy,medium,diffcult in continues manner, 2nd containing wrong ,right,blank in continues manner, in 3rd coloum having topic name how to print topic name of blank and wrong questions using easy , medium,hard colum
what if i want to change the color of white cells as well?
You would need a second condition to handle the reverse of the condition.
Hi. I have 4 cells with percentages in them. I want for format the first cell only, if the value of that cell is greater/lesser than the value in the other cells. Blue if it is greater, and yellow if it is lesser. This should only be done if the first cell is greater/lesser than all the other 3 cells, not if it is greater/lesser than one or two other cells only. And then I will need to copy this conditional formatting to other cells in the same column.Please help.
You will need 2 Conditional Formatting rules. 1 for each colour/condition.
To determine if the cell value is greater than all others we just need to know if it is the largest of all other values, therefore is bigger than all of them. Same for the lesser than scenario.
So from that first cell create a greater than rule with this formula - =MAX($B$3:$B$5) assuming the other 3 cells are B3,B4 and B5.
Change MAX to MIN and use the lesser rule for your other scenario.
Tks for your help, Sir, but I regret that it does not work.1, The cell where I need the formula is A3, and this would be compared to C3, E3, and G3. 2.The highlighting should be done only on A3. 3.Cells B3,D3, and F3 have other data from which the %ages in C3, E3, and G3 are derived. 4.Highlighting to be done only if A3 > or < C3, E3, G3.Meantime, I really do appreciate the time and effort you have taken to help.
Yes that is absolutely fine. As long as you select A3 only, then that is the only one to change colour (point 2).
If you want 2 different colours then you need to different rules. One for A3 > C3, E3, G3 and on for A3 < C3, E3, G3. Which is what I promised previously.
If you want the same colour then highlight A3 and in the Conditional Formatting use a formula for the rule and enter - =OR($A$3>MAX($C$3,$E$3,$G$3),$A$3
Got it, works like a charm. Thanks very much for your valued guidance
how many maximum conditions can be given in conditional format in ms excel
There is not really a limit Jayesh. The AND and OR functions are set at 255 conditions as a limit (which noone will meet). And you can use IFS and Lookups in there, so I would not worry about the maximum.
Hi, how would the formula look like if you had (1) Platinum, (2) Silver, (3) Gold, (4) Bronze, (5) Diamond and (6) Basic memberships and you want to highlight the following memberships over 10 years? (1) Platinum, (2) Silver, (3) Gold. Thanks a lot :)
I would just create 3 different rules following the guidelines in the video. One for Platinum, silver and then gold. You could then use different colours. Otherwise with one formula you could wrap the conditions all in an OR function.
I can do one but when I do two it won't work, I am using 2007, is that maybe a factor ?
2007 should not be factor Kimberly. I don't think you can reference other sheets within a CF rule in 2007. But the functions for multiple conditions are fine.
not able to use this formula in office 2013. Please help
This should be no problem. These functions have been in Excel for way before 2013.
I wish to calculate like:
Cell1: lets guess that there is a total amount of money in cell 1.
Cell2:- if Cell 1 is more or equal to 400000, then show only 400000, if less than 400000, then show the subtract amount.
Cell3:- if Cell 1 - cell 2 is more or equal to 500000, then show only 500000, if less than 500000, then show only subtract amount.
Cell4:- subtract amount of Cell1 - (cell 2 + cell 3)
🕶🎁
Row 17 ???
He has rounding in place. If you notice at the beginning of the video, when he clicks on column F at around 40 seconds in, the cell reads 15 but in the formula bar the actual amount is ~14.73. Row 17's years are between 9.5 and 9.99999, but rounded up to 10. Alan could use the ROUND function in the equation, =AND(ROUND($F2)>=10,$G2="Platinum"), if he wanted to round up those numbers to match the visible number in the cell.
Thank you Michael. I'm just seeing these comments a year later. The workbook that can be downloaded from the description has an integer value, so row 17 works as expected.
Im tired
Oh no! Grab a coffee.