MS Excel - Conditional Formatting Part 1

hi in this video we are gonna see conditional formatting part 1 let's see what are the topics here we'll be looking for highlighting cell rules we'll be looking for top/bottom rules and how to be clear rules on the screen if you see there's some data given here there's a number text end date purpose we have written different data let's see what is that conditional formatting normal formatting is just applying a formatting without any criteria or predict use it but conditional formatting is you have some criteria or you have some condition based on which you are putting some formatting let's see how it is I have some numbers if you see I want to highlight those numbers based on some criteria so let's say I want to highlight numbers which are greater than 50 so first thing is I will select the cells in which the numbers are there and then I'll go to Home tab and in the Home tab if you see I have something conditional formatting in the conditional formatting and select the option as highlight cell rules greater than once I click on is greater than I'll get this dialog box which says which cells you want to highlight so I have the condition as 55 given so I'll type here 50 and once you crater is done with which formatting do you want to format so the format is given as light red fill with dark red so you see on the screen it is already colored okay if you want to change the formatting you can select from these options or else still if you don't like it what is given here you can go to custom format so from the custom format you can select the formatting you want for yourselves like what type of color what type of border or the number format you want right now I'll not go in detail for that I'll just click on OK so you see greater than 50 all the numbers have been highlighted imagine tomorrow this number becomes greater than 50 let's say this cell becomes it is right now 10 if it becomes as 58 so you see this it is automatically colored and if the number 80 is decreased to less than 50 or you can see as 25 so the color will automatically go away so this way you can put different options here what are the different options available conditional formatting highlight cell rules so I have shown you how to go for greater than similarly you can go for less than between and you can for equal to all the four options let's see the other method now the other one is X that contains so on the screen if you see I have written some random text that is a days Monday to Sunday and again I've repeated now I want to highlight only that text in which Monday occurs so if you see in the screen Monday and the second one is Monday so I want to highlight those cells so the same method I'll select this cells in which the data is there that is a text and I'll go to conditional formatting and I'll go to highlight cell rules and you see this option as X that contains so once I click on this dialog box it says which text you want to highlight so it has taken automatically as Monday if I want I can just delete it and you can just change it to Tuesday so you see all the Tuesdays are highlighted and you can keep on changing as per your requirement and also the same like formatting which you have seen earlier so this way you can select if I want I can just change it to Venice day like this and see you can see this two are highlighted and you can just click on okay so this way you can put some condition and highlight those cells if this Sunday becomes s minus t if somebody changes it so you automatically that color also changes according to your requirement or criteria next this this was for the numbers this was for the text and let's see how about the dates I want to highlight only those cells which occurs in the next week I'm just giving an example so I'll select this entire data the column and I'll go to this Home tab conditional formatting highlight cell rules and you can see there's a date occurring so I'll select this date occurring and I'll sell a and I'll select that option date occurring for next week so you see next week it starts from 72 July today's 13th of July so from 17 July all the cells will be highlighted and I'll click on OK similarly if this date changes to 17 to July or 18 July this will automatically highlight so this way you can highlight dates also next we have something known as duplicates and unique values let's see what is that in this column and now you see I have applied the formatting now I want to clear the entire formatting so there's one option known as clear formats so you can go to the conditions for and you can see the last option clear rules and there are two options available do you want to clear rule from selected cells that is the selected cell or do you want to clear rules from the entire worksheet so right now I will prefer the second option entire worksheet and I will click it click on this option so wherever the rules were applied everything will go away so the all the rules formatting will be gone next is if I want to highlight some duplicate values or sometimes unique values I can use this method now in this column Monday Tuesday and Wednesday these three cells are repeated data duplicate values so I'll select this cells and I'll go to conditional formatting highlight cell rules and you see the last option which is duplicate values I'll just click on this duplicate values and if you see by default automatically it has selected three duplicate values and that those are repeated that is Monday Tuesday Wednesday and again Monday Tuesday to two times minus T now this is if I click on OK this will be applied now if you don't want for duplicate values if you want for unique values how we can go it you can select the option from here let's say unique values and you can select the unique values from here yeah so these are the three unique values in the entire column so imagine this is a very small data imagine the database is very large that time this will be much more useful and you can just click on OK now I remember there's one thing which is hidden though they have said as duplicate values but there's a feature which I can use as unique values also I'll just click on OK and click outside so that rule is applied now if I type here something some other text enter so this one is also a unique value which will be highlighted automatically now I'll just clear all the rules from the sheet itself conditional formatting clear rules from the entire sheet next I have next I have that what you call highlight cell rules was done that is the next one is conditional formatting top/bottom rules now if you see this entire column what are the top percentage or you can say stop items I have 10 200 the first item which is stopped that is 100 so what we'll do is I'll select this number and with the help of this conditional formatting conditional formatting highlight Jules sorry top/bottom rules and I have top ten items so I want to select top ten items if I click on this option it says I have 10 items so all the 10 items are top 10 items but I am looking for only one item so what I'll do is I'll just reduce this number and I'll say I want top 1 item so in this entire column this one number which is 100 it is the top one item in this column itself and if I click on ok so that number is highlighted so this way I can go till 10 numbers and more than that so like this I can select if this 50 becomes as 100 so in this entire column there are 2 items which are top which is the unique number that is 100 and this is captured as similarly if I want top bottom 10 items similarly I can go for it but before that I'll just clear options clear rules from the entire sheet now I'll select this numbers and I will go for conditional formatting top/bottom rules and I'll select the second option as bottom 10 items so if you see bottom 10 items all other 10 items but the most bottom item is number 10 so I'll click on this bottom 10 items and from here I'll just select the lowest item that is bottom 1 item yeah so this is bottom 1 item if I increase to 2 that becomes bottom 2 item bottom 3 item and this way you can keep on expanding and if you want to can change the color from this place also right right now I'll click on OK so this rule is applied now if I want to clear rule the same method again you have to go to conditional formatting clear rules Creole rules from entity let's see the next one I have something known as top-10 percentage in this entire column top 10 percentage belongs to this 100 next level becomes to 80 and 90 so now if I go to first I need to select everything I'll select the column and then I'll go to conditional formatting top/bottom rules I am talking about top 10 percentage so once I click on this top 10 percentage it says the top 10 percentage are given to this 100 number if I ask them to increase for top 20 percentage top 20 percentage yeah you can see for top 30 percentage they have included 90 and it keeps on increasing with every number yeah if I have saved top 40 percentage it also includes the number 80 so this way you can include top 10 top 20 40 percentage and all though the number is given as top 10 percentage but it gives you till 100 person also so I'll just click on cancel similarly if you have seen for top 10 percentage I hope you'll also be able to understand for bottom 10 percentage same way so the bottom 10 percent will include for the number 10 then 20 and then 30 keep on going it next is the last one we have known as above average so in this place first I will show you what is the average given here I'll just expand this yeah if I have selected this data now what is the average for this numbers are how much it is 55 now I want to highlight only those cells which are above average so that time I have to use the same option first to select the data conditional formatting top/bottom rules and you see there are two options that is above average below average I have the above average as 55 which has calculated automatically I'll select this option as above average so above 55 I have the numbers as 6070 till 100 and those have been captured same way I can change the formatting from this place there is no other option apart from this there is no other place because automatically it has calculated above average that's all so I'll just click on OK and the rule is applied and based on that rule the condition is also formatted that's all so now in this video we have seen how to go for highlighting cell rules and we have seen multiple options we have seen how to go for top/bottom rules and clear rules that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 731,152

Keywords: MS Excel, Excel, Microsoft, MS, MS Excel - Conditional Formatting, Conditional Formatting

Id: 7iKoccSTNZA

Length: 10min 42sec (642 seconds)

Published: Mon Jan 15 2018