Transcription
Today I want to show you how you could 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 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 Rules. And what I could do is you see options like greater than. So if the value is greater than something, you can 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. Uh, 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 can 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 that 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. Uh, Anise, I don't know what to do with her, she just got a D equivalent. Um, Deborah, she doesn't really seem to care that much, but she still got, you know, half the questions right at a 51. And so you can see how the data gets highlighted here. So very quickly, I could 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, someone told me the chicken doesn't taste like chicken. All right, we might need to dig into that review, that sounds bad.
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 Cells Rules. And here you can 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. Um, so this isn't necessarily a bad thing that'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 OK. And then here you can see that wow, I have some different conflicting points of view. One person said the food's great, the other person said the chicken didn't 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 can 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, you know, if I could, 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 could see that I typed in Kevin Shaw and Kerry West more than once. And so I could 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 percent or maybe let's say the top 20 of people. So here that I could see John, Megan, Michael, Jacob, and Denise were in the top 10 percent of the class. And what I could also do is if I go back to the Top/Bottom, I could do the bottom, say 10 items, the bottom 10 percent, 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 percent of the class or the bottom 50 percent 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 correspond with the value of the test score that they had. I could 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 Sets. 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, percent, a formula, 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, 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, uh, this version of Excel that I'm using, this is, this is Office 365, so it's the latest version. It'll work with 2019. I can't guarantee that it will 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.