excel automatic formatting This is a topic that many people are looking for. newyorkcityvoices.org is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, newyorkcityvoices.org would like to introduce to you MS Excel – Conditional Formatting Part 1. Following along are instructions in the video below:
“In this video. We are gonna see conditional formatting part 1. Let s see what what are the topics here we ll be looking for highlighting cell rules. We ll 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.
Thank you for watching all the articles on the topic MS Excel – Conditional Formatting Part 1. All shares of newyorkcityvoices.org are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.