 What's going on everybody? Welcome back to another Excel tutorial. Today we'll be looking at conditional formatting. 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 this tutorial, I want to give a huge shout 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 on to my screen and get started with the tutorial. All right, so let's jump right into it on this home tab right here. If we go all the way 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. 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, self rules. We have some top bottom rules, data bars, color scales, icon sets, and 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 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. And for this data, we kind of have this 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 of 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 red. 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. 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. 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. 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 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 in data bars are going to be the most obvious things in here. If you go right here, 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. And 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. 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. And 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 it from the selected cells. That's what we're doing. So I have column G selected and 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% 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 the ones that are at the very top are Michael Scott's, Toby Flandersen's 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 Michael Scott and Dwight Schrute and Toby are kind of bringing up that average quite a bit. So everyone else is going to fall beneath that. 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 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 characters in their 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 we'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's going to highlight 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 kind of in a similar inverse way. It's just 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 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 am 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 the new clients working with their data. And so I wanted to show you this when 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 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 10 rows, but when you have, you know, 50,000, 100,000 and there should be zero duplicates in there and you highlight it. And then you come right here, use the filter and we're going to 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 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, and 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 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, I would just search for like Loraz or something and not Lorax, but Loraz, you know, I would just search for it. And then all the ones that contain that would 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 okay, 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 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 put in a dash, and it has it in there, then you know that that is wrong. Now that is really all I wanted to show you in the highlight cells rules. The duplicate values in the text contains are by far the ones that I use the most. All the other ones I have used, these ones not so much, but in these highlight cells rules, I use these two all the time. Sometimes I use this between. I don't really use these other ones as much, although I have used them. And so if you've 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 you can apply them to your own data sets. Now, we've looked at all of these preset ones and 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 have H11 highlighted, that's going to come into play in just a little bit. But I wanted you to be aware that H11 is the cell that we're highlighted. So what we're going to do is we are going to create our formula. Now, if you've never created a formula, I highly recommend watching my formulas tutorial because that is going to show you how to do this. But we're all we're going to do is we're going to do equals, that's how you start the, 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. 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. So we're going to get rid of this, we're going to 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 a border and 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 it still has the G2 to G10 is greater than 50,000, but it only is being applied to this H11 cell, which really makes no sense. So if we had wanted to get it done the first time, we would need 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? 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 and we're going to give it this range and we're going to get rid of these dollar signs. And now we're going to hit OK. 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, 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. 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. 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 about 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. 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, 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 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 really important to watch out for. And that is all there is to it with conditional formatting. Again, conditional formatting is, 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, these filters right here. Because again, this can just take forever. 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 can do with conditional formatting in like 10 seconds. So definitely something to mess with and use when you are working with your own datasets. I hope this was helpful. I mean, honestly, I use this all the time. So, you know, I hope that somebody out there 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 to me for sponsoring this Excel series. If you liked this video, be sure to like and subscribe below. I'll see you in the next video.