Conditional Formatting in Excel Tutorial

Data Translates
219 followers
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
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 an 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 okay 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
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 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 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 could do is once again conditional formatting. I'll click on highlight sales 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 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 percent. So let's see the top 10 percent or maybe let's say
the top 20 percent of people. So here that I can 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 can
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 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 sets. So I
can 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 can say
you know the highest value a number of 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 can 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.
