Conditional Formatting in Excel | Excel Tutorials for Beginners

what's going on everybody welcome back to another excel tutorial today we'll be looking at conditional formatting [Music] now if you've never heard of conditional formatting before that's okay i had never heard of it before i became a data analyst and so now that i've been using excel a lot of course i use it quite a bit and so i want to show you how to use it conditional formatting is basically just a way to see patterns and trends in data and that's a super simple way of putting it but it's very easy to use and

so hopefully i can show you how to use it really easily in a lot of the things that i use the most and some of the things that i use it for so that you can also know how to use conditional formatting now before we jump into the tutorial i want to give a huge shout out to the sponsor of this excel series and that is udemy you guys know by now that i absolutely love udemy i've been using them for years and i've taken literally hundreds of courses on udemy and i've learned so so

much especially when i was first starting out as a data analyst i learned a lot through their excel courses on udemy and so i have actually put the ones that i really like and i have taken and enjoyed and think you would as well in the description so if you want to take those be sure to check those out again huge shout out to udemy for sponsoring the series now without further ado let's jump onto my screen and get started with the tutorial alright so let's jump right into it on this home tab right here

if we go all the way over to the right there is conditional formatting and the description that it gives us is easily spot trends and patterns in your data using bars colors and icons to visually highlight important values and that is exactly how i would have defined it a really good job microsoft exactly how i would have done it so what you'll see right away is there's nothing too complex so we have some highlights cell rules we have some top bottom rules data bars color scales icon sets and then at the bottom we can create

a rule we can clear the rule and we can manage our rules so if you create a rule then you can manage it so we're going to start with these icon sets and i'm going to show you how to use those and we'll work our way to the top and then i'll show you how to create some rules yourself and how that all works so let's start off with the icon sets i'm going to go over here to sales [Music] and for this data we kind of have this you know trend or pattern that you

can kind of see over time so over the months so if we go right here and let's use that conditional forming let's use that icon sets and right here we can use these directional so you know we have this kind of time series of each month that shows us how much paper they're selling and if we do this right here it's going to show us if it's kind of average or if it's below average or if it's above average or if it's going up so at a really quick glance you can kind of see the

pattern of this data set it's kind of going mostly yellow and red there's only two months where it's going up significantly now we don't have to only do that for one row or one column you can apply to all of them but as you can see all these are red now why are they all red it's because they're using numbers for everything so they're comparing these 24s and these 50s and 65s against these 450s and 750s and so they're all going to be read but if we do it individually if we do it each row

if we take it just like this and then we go to icon sets and do it it's going to be much more representative of the actual printers not of all the numbers as a whole and you can do other things uh the arrows are ones that you'll probably see the most often that's the one i've used if i ever do use them um but you can you know do ones like this where they have you know kind of a trend upward or a trend downward and so there's just several more arrows this one only gives

you three as you can see this one gives you five and you can do you know colors or shapes or different indicators and all these different things um and honestly it's kind of whatever you want to use whatever makes sense for your data but you know i've really only ever seen like these colors being used i've never really seen these flags or anything like that but again it just depends on what industry you work in you might you might see that let's go right over here to the demographics and let's look at our color scales

now color scales are going to be the probably the most obvious thing that on data bars are going to be the most obvious things in here if you go right here and and you look at this color scale if it's high if it's among the top ones it's green the lowest it's red and you can change that to really any colors you want any colors that they offer you um and it it does exactly what it does it's a color scale a gradient of the colors from high to low or low to high and so

any color that you do you'll be able to kind of see you know what's good and what's not good that really is color scales in a nutshell data bars are again super super straightforward it's going to be either a gradient fill or a solid fill so let's look at the gradient fill if we do a blue gradient fill actually let's get rid of our let's go over here let's go to clear rules from selected cells we haven't looked at that yet but that's how you clear it let's go to data bars and we'll use this

blue gradient so with this blue gradient you know this one is or sorry this one is the highest one so it's going to be completely filled and this one is 36 000. almost half of this pretty close and so it's almost half this one again you know it's not used very often i you don't see these a lot to be honest you just don't but if you do see it that's how you use it that's how it can be done again pretty easy as i just showed a second ago if you want to clear the

rules you can clear from the selected cells that's what we're doing so i have column g selected and i'm going to i'm going to clear that if you want to clear the rules the entire sheet you can do that as well so it would affect every single column and row we'll just do this for now so now let's go look at the top bottom rules so this is the top 10 items top 10 bottom 10 items bottom 10 percent above average and below average and they're going to do exactly what you think they are going

to do if you select above average it is going to select or highlight the cells that are above the average in column g so let's look at the salaries that are above average all right and so uh the ones that are at the very top are michael scott's toby flendersons and dwight schrute no shock there i believe the average is somewhere around like 48 500 or something so i think this one just is just below it and so all these other ones are below average and that's just because you know michael scott and dwight schrute

are and toby are kind of bringing up that average quite a bit so everyone else is going to fall beneath that and so at a super quick glance you're able to just highlight the cells and you're able to see who is above average and you know you can do this in a lot of different ways in excel but this is just a really simple fast way to do that let's get rid of that real quick and let's go back up here and now we can oops let's go to top bottom rules and now we can

see the below average and it's going to highlight all the other ones and so it works exactly how you think it is going to work and this is the default way that it highlights these cells so it highlights them this kind of see through red and then it highlights the actual text or the um characters in there red as well now i'm not going to go through and show you every single one of these top bottom rules i think they're pretty self-explanatory i just kind of wanted to show you what happens when you do use

one of them it's going to highlight that cell so let's go up here to the highlight cells rules and honestly these are the ones that i use by far the most all these other ones combined i do not use more than this highlight cells rules and the one in here that i use more than any other conditional formatting rule is this duplicate values so i'll start with that really quick and i'll kind of show you a few of these other ones but this duplicate values to me is one of the most useful ones and so

let's kind of show you how that works if you go to the start date you can see that we have a duplicate value right here and if we go over here to conditional formatting highlight cells rules and duplicate values it is going to highlight um the duplicate and that says duplicate right here now we can go through here and click on unique and then it would highlight all the ones that are not duplicates so you can use it you know kind of in a similar inverse way it's just different different but i use the duplicate

almost always another thing that you can do is go over here and you can change the color or you can even do a custom which i never do that's not something i spend a lot of time doing i typically just stick with this one so you can do that and it's going to highlight you know something that has a duplicate value in there now why do i use this so much well i work with a lot of different types of data sets but one thing that you'll find in almost all of them is they have

some type of id and they're going to have some type of personal information whether that's a social security number or an address or you know or a cell phone number or something like that there is going to be data that is going to identify that person now i work a lot with pharmaceutical data a lot with pharmacy data as well as healthcare data so like names social security numbers addresses phone numbers all those things all that customer or client information and oftentimes when i get a new data set and i have it in excel or

i convert it to excel i will start using these duplicates to try to find issues with the data and i find them all the time either there's an employee id or some type of customer id or client id that has a duplicate in there that should not be in there or there's multiple social security numbers or there's an issue in some other way and i'm able to find those things and spot those patterns using this duplicates and i promise you i use this one almost every single time i open a new data set or i

work with a new client working with their data and so i wanted to show you this one i wanted to really press upon you that this one is a really really really good one to know and learn how to use it's not complicated it's not hard it just shows you you know you know if there's a duplicate value but i wanted you to know how i use it and how often i use it so that you can you know pick that up and put that in your toolkit in your back pocket so that you can

use that later on if you have if you have a similar need or if you're trying to do something similar to what i was just talking about so that is how duplicates work again super great it's obviously not super useful when you're only using ten rows but when you have you know fifty thousand a hundred thousand and there should be zero duplicates in there and you highlight it and then you come right here use the filter and we're gonna filter and we're going to sort by the color and it allows you to sort by the

color and you have duplicates in there then that's a problem and you identified a problem super quickly and you know some of those things they slip by because nobody checks it and so that's something that i i often check and if you go here and you sort by color and there isn't an option to do this this pink red color then that means there aren't any duplicates and that's a really good thing most the time that's a really good thing so let's go ahead and we're going to clear that as well as get rid of

our conditional formatting rules now another one that i use a lot is this one right here which is the text that contains honestly this one comes a lot in handy especially when you're looking for like a specific keyword in my uh case a lot of times i was using this when i was going through drug names i am not a doctor i do not pretend to be a doctor and so when i was looking for lorazepam or something like that um i would just search for like boraz or something and and not lorax but laura

you know i i would just search for it and then all the ones that contain that will pop up i can bring them to the top and i can see them and to me that's super super useful and i would do that all the time and so in this case we're looking at emails and let's say we all only wanted to pull all the ones that are gmail and so now we can go through and we can you know click ok and that's going to pop up or we want all the ones that have dunder

oops dunder mifflin and if we click on that all the ones that are dunder mifflin come up or have dunder mifflin in it and again we can sort by or we can um and so we can sort by right here and we can bring all those to the top and so super super useful and another use for it that you may not think of is something like if it's you know there's some incorrect data in there this happens often with phone numbers addresses start dates or dates in general date formats where you can go in

here and you can say text that contains and if you know you put in a oops a dash and it has it in there then you know that that is that is wrong now that is really all i wanted to show you in the highlight cells rules uh the duplicate values and the text contains are by far the ones i use the most all the other ones i have used um these ones not so much but in these highlight cells rules i use you know these two all the time um sometimes i use this between

i don't really use these other ones as much although i have used them and so if you got nothing else from this video i just wanted you to know that these two are super useful and if you haven't used them before to maybe try them out and see if you can apply them to your own data sets now we've looked at all of these pre-set ones in conditional formatting but you can also do a new rule and so if we click on new rule right here and we go down to use a formula to determine

which cells to format we can add our own formula in here that will then highlight exactly what we want and so if there isn't a preset rule that you like and it doesn't have the option that you want you can do almost any formula that you want in our formulas video that we did a few weeks ago and you can put it in here and then you can format what you want the cell to look like if it meets that criteria so let's take this right over here and before we start this formula i just

want you to note that you know i i have h11 highlighted that's going to come into play in just a little bit but i want you to be aware that h11 is the cell that we're highlighted so what we're going to do is we're going to create our formula now if you've never created a formula i highly recommend uh watching my formulas tutorial because that is going to show you how to do this um but we're all we're going to do is we're going to do equals that's how you start the uh how you actually

create a formula and we're going to give it this range right here and so it's going to take everything from g2 to g10 now these dollar signs are super important if you don't know how to use them or you don't know what they do you're going to mess up this formula a lot and so what this dollar sign basically does is it's basically hard coding it in there it is only going to look at g2 and is only going to look at g10 or through g10 because that colon and this can come into play because

if you have something selected like the h11 it's going to mess it up because now if you have h11 selected like we do you'll see this in a second it's not going to be applied to this um and again i'll show you that in just a minute but we don't want this hard-coded in there okay but we do have to select the proper range in a second um so we're gonna get rid of this we're gonna get rid of the dollar signs because we want to be pretty fluid and be able to apply to be

applied basically anywhere we want let's go into this formula if it meets our criteria let's give it um let's give it a border and we'll give it um we'll give it some color we're going to say if this is greater than 50 000. so let's hit ok and nothing happened so let's go back and see why so if we go to our manage rules you can see that so the g2 to g10 is greater than 50 000 but it only is being applied to this h11 cell which really makes no sense um so if we

had wanted to get it done the first time we needed to have basically selected that g2 to g10 right away but we can do that now so let's get rid of this and we're going to say g2 to g10 and that is hard coded in there that should be fine still but let's see what it does and so now every single thing is highlighted and why is that uh that's because when we changed it it also changed the format of it because we changed the cell that we were looking at so we need to come

back here and that's why again you want to do this the right way the first time we're going to come back here we're going to give it this range and we're going to get rid of these dollar signs [Music] and now we're going to hit okay and so now it's being applied g2 to g10 and g2 to g10 and we'll keep it like that and we'll apply it and now it works properly so now everything that's above 50 000 is being highlighted again if that was confusing um it is confusing it genuinely is and so

if you wanted to do this right the first time without having to make a bunch of changes you'd want to highlight these before you start and then you want to go in and create the rule we'll do this really quick just to kind of show you what i'm talking about we'll say equals we'll give it this range i'll get rid of these real quick because again i don't want this hard-coded in there it will ruin our formula and then we'll say greater than 30 and we'll give this nice green and so now if they're over

the age of 30 it will be highlighted and we didn't have to go back and change anything we didn't have to go back and fix anything like we did in the first one um that was all for demonstration purposes but again you need to really be aware of that that is something that i think almost everybody is going to mess up at some point if you don't already know about it then you definitely are going to make that mistake now if we come over here in this area we go to our manage rules and not

just the current selection but this whole worksheet then you can see that we have these two formulas now you can go in and edit any of these by double clicking or clicking on it and then hitting edit rule you can also delete these rules or duplicate these rules um i just wanted to show you what you're able to do with them but if we go ahead and we get rid of this so let's say we delete that rule and we hit apply uh you know the rule is going to go away that's that i mean

it's as simple as that so that is how you can create your own rule i want to be again very specific in the fact that that is a confusing piece and if you mess that up you're going to be you know fixing a bunch of different stuff and not understanding why your rule is not working properly it's just because it's confusing those dollar signs are are really important to watch out for and that is all there is to it with conditional formatting again conditional formatting is um you know it's not anything super confusing we've looked

at more complicated things but it's a really really useful tool to use to look at these patterns and trends super quickly and to find these outliers or these specific values that you're looking for very quickly and if you're looking at just thousands and tens of thousands or hundreds of thousands of rows this is one of the fastest ways to find these things without having to kind of wait and filter and use these um these filters right here because again this can just take forever um and so if you haven't or if you've never worked with

a ton of data and tried to use this before it can take honestly like 10 minutes for something simple that you could do with conditional formatting in like 10 seconds so definitely something to mess with and use when you are working with your own data sets i hope this was helpful i mean honestly i use this all the time so you know i hope that somebody out there can can use this for their own work that they're currently using thank you guys so much for watching i really appreciate it again huge shout out to you

me for sponsoring this excel series if you like this video be sure to like and subscribe below i'll see you in the next video [Music] you