Today I want to show you how you can use conditional formatting in Microsoft Excel. Now conditional formatting is a really valuable tool that you could use to visualize your data and to see trends and patterns in your data. It might sound a little terrifying but it's really not. Let's jump into it and I'll show you how to do it.
So here I am in Excel and just as full disclosure before we get going here, I work at Microsoft as a full-time employee. Okay well so how can you work with conditional formatting? Well let's say that I'm a teacher in school and And let's say that I just recently had everyone take a test on, I don't know, maybe conditional formatting. And so these are all the grades that came back.
We have some people who performed well and others who didn't perform so well. Of course, if I were the teacher, I would expect all the grades to be a little higher. But hey, we're just doing this as an example. So let's say I have all these grades and I want to see who performed below an 80. So what I could do is you have this button up here under the Home tab. called conditional formatting.
If you hover over it, you'll basically see what it does. So once again, you can see trends and patterns in your data. And so let's click on that and let's try it out. So the first item we see here is highlight cells rule.
And what I could do is you see options like greater than. So if the value is greater than something, you could highlight it. If it's less than something, you can highlight it. That's exactly what we want to do. We want to see people who scored below an 80. So I'm going to click on this option.
And what I want to do is, let's say if they scored below an 80, so I'll type in 80 there, and what I could do then is I could format the text in different ways. So I could do a light red fill, yellow, green. We're not going to do a green because it's not a good thing when you get a low score on a test. So we're going to go with the red, but I could also do, you know, red text, red border.
I could also do custom formatting. So let's say I wanted to bold it, I wanted to highlight, you know, pretty much anything I want to format it with, I could do within this view. But I'm going to cancel that and come back to here. And I like the red fill.
So I'll just click on OK. And so here very quickly now you'll see all these people. So Sophia, she got a 76. So she's working hard but didn't quite make it. Anise, I don't know what to do with her.
She just got a D equivalent. Deborah, she doesn't really seem to care that much. But she still got half the questions right at a 51. And so you can see how the data gets highlighted here.
So very quickly I can see who's struggling in my class. So I'm going to undo that and now we're going to go on to this next data set. Let's say that I run a restaurant and I had customers recently submit reviews on the food.
You know, some people love the food. It's great. Yes.
Awesome. Other people, ooh, someone told me the chicken doesn't taste like chicken. All right.
We might need to dig into that review. That sounds bad. And so what I'm going to do is let's highlight all the dates and I'm going to go back to conditional formatting, highlight sales rules.
And here you'd see a date occurring. So let's say that I want to read all reviews from customers within the last week. So I just want to see recent data.
I can click here and here you see all these different views and I'll say in the last seven days. And once again, I could format how I want to format it. So this isn't necessarily a bad thing that it's been in the last week. So I'm just going to go with a, let's just go with a yellow fill.
So I'll click okay. And then here you could see that, wow, I have some different conflicting points of view. One person said the food's great. The other person said that chicken, they taste like chicken. I don't know what's going on in my kitchen, but I'll probably need to talk to the staff there.
But here, very quickly, I could just see what all my most recent feedback was, thanks to conditional formatting. The next thing I want to do is, let's say I have all these students in my class. I typed this list late at night, and I might have typed in some duplicate values. How can I get rid of the duplicates without going through the list one by one?
Well, what I could do is, once again, conditional formatting. I'll click on Highlight Cells Rules, and there's something called Duplicate Values. So I'm going to click on Duplicate Values. And very quickly, you'll see once again, I could either say duplicate values, so if it occurs multiple times, or identify all the unique values, so basically the inverse. But I'll do all the duplicate values, and here I can see that I typed in Kevin Shaw and Kerry West more than once, and so I can delete those and get rid of those duplicates.
So I'm going to go back here, and we're going to go see what else we can do with conditional formatting. So let's go back to the conditional formatting view. They also have something called top-bottom rules. And so what you can see is I want to see the top 10 items or the top 10%. So let's see the top 10% or maybe let's say the top 20% of people.
So here that I can see John, Megan, Michael, Jacob, and Denise were in the top 10% of the class. And what I could also do is if I go back to the top bottom, I can do the bottom, say, 10 items, the bottom 10% above average or below average. So let's see all these people who are above the average. and then all the people who are below the average.
So are you in the top 50% of the class or the bottom 50% of the class? So conditional formatting lets me very quickly visualize the data in a way that I can make sense of it. Once again, I'm going to click in here. What I can also do is they have something called data bars. So you'll see how it just places bars that corresponds with the value of the test score that they had.
I can also do color scales. So here I can see who got the highest, which is in bright green, and then who got the lowest, which is in red. And then all the... Colors in between so you can very quickly visually see who did well versus who didn't And then they also have icon set so I could do it with arrows or I could do it with circles So if you did well, you get a green circle if you did, okay You get yellow if you really didn't do well, you get a red circle And then what I can also do is there's this thing called new rule And so this is now getting even giving you even more power and control over what you could do with conditional formatting So here I can format cells based on value And I could say, you know, the highest value, a number, a percent, a formula, a percentile.
So you can really define what you're looking for and then format it in a different way. You could say cells that contain and you could say between these values equal to the top or bottom ranked values, above or below the average, unique or duplicate values. Or you can even use a formula to apply conditional formatting. And if the formula resolves to true, then it'll apply that formatting. So there's really lots of powerful things that you can do with conditional formatting to be able to visualize and see patterns in your data.
This is just a very quick overview and a few kind of fun scenarios that you could use to be able to spot different trends in your data. If this was helpful and now you're using conditional formatting that comes with Excel, feel free to leave a like. If you want to see more content like this, hit that subscribe button. And lastly, if there are any other topics that you want to learn about, feel free to leave a comment down below and I'll add it. to my list of videos to make.
And once again, this version of Excel that I'm using, this is Office 365, so it's the latest version. It'll work with 2019. I can't guarantee that it'll work with older versions of Excel, but conditional formatting has been around for a while, so chances are it'll work for you. All right, well, thanks a lot, everyone, and I'll see you next time. Bye.