If you've ever wanted to format your data based on specific values that are
happening within it then this is exactly the video you need. We'll start with
conditional formatting cells based on their own value and then we'll look at how to
conditional formatting based on another cells value. The data we have today is the past
four years of avocado sales - both price and volume information for the US market.
Let's look at the price. Let's say we want to see high and low prices or above
and below a certain amount. Let's highlight all of the prices that are
below $1 per avocado. To do this we're going to select our data. Because all of
my column B has this price information I'm going to go ahead and click on
column B. Now I'm going to go here and click on conditional formatting and
select highlight cell rules, less than, and here I'm going to type in $1. I'm
going leave the light red fill with dark red text though you can go ahead and
format that however you'd like and click OK.
Immediately we see here we have one value already in 2019 where the average
price was below a dollar. If I scroll down more through my data looks like
last May, last February and a few other times in the past the prices have been
low. 2017 looks like it was a great year if you like eating avocados. Let's say I
also want to know when the price seems a little bit on the high side. Let's go
back here, click on this to highlight column B. Go to our conditional
formatting and add a rule that we want to see any value that's greater than $1.50 per avocado and in this case let's highlight those with a green
fill so that we can see some difference. Click ok. Alright, now we don't have
anything on our first page here that's over a dollar fifty but if I scroll down
it looks like September and October of 2017
were particularly expensive months for avocado eaters. And if we go through our
data we're likely to find some other times when things were high. Actually it
looks like back to 2015, we had more times where avocados were below $1 than
they were above $1.50. That's great if you want to go ahead and format
those specific cells, but let's say I want to change what I'm looking at each
time. I'm going to go here and clear all my conditional formatting by going to
conditional formatting > clear roles and I'm going to go ahead and clear them for
the entire sheet so I don't have anything formatted. Let's say instead of
highlighting those individual cells and manually plugging in $1.50 or $1
as my rules, I want to put a low price and a high price and I want to be able
to plug in what those are. Let's use the same amounts. I'm going to use a
dollar for my low price and a dollar 50 for my high price. Let me go ahead and
format these quickly as money so they're easy to to see and let's say I'm going
to go ahead and format price based on these values. It's a very similar process. We
highlight column B and first we're going to do our low price. Come over to
conditional formatting, highlight your cell rules that are less than and
here we'll go ahead and click this arrow beside this dollar 21 that automatically
pops up and we're going to click on our cell in this case it's M3
that has that dollar price in it. You can already see it started highlighting
something red and we're going to keep that with light red fill with dark red text.
Click OK. Now we have that same value but if we want to change - it let's say we
want to say a low price is actually below 90 cents I can quickly change that
and my formatting changes. Maybe we want to say a low price is below $1.20.
In that case most of my cells start changing color because there's a
lot in that range. I'm going to put this back at $1 and let's do the same thing
for the high price. So again go back here, click on column B. Go to conditional
formatting > highlight cell rules > greater than and we're going to do greater than
m4. Hit enter and let's change that formatting just so we're not
highlighting our lows and highs the same and click OK. You'll notice sometimes
texts will format a little strangely with those financial figures. That's not
really something you need to worry about in here. As we scroll down again we
have that one period back in September/ October of 2017 where the prices were
above a dollar fifty but maybe a high price for me is if it's above a dollar
twenty-five. If we go and put a dollar twenty-five in that high price again we
start seeing more values that are exceeding that limit that are getting
highlighted. It's really that straightforward. In about five minutes we
were able to format in a few different ways. If you want to take those rules off
again just click on those cells > conditional formatting > clear your rules
and you can clear them from the entire sheet or maybe you have other columns
formatted that you want to keep or other cells formatted that you want to keep
you can just clear the rules from selected cells and now everything's back
to blank. Thanks so much for watching let me know what questions you have about
doing conditional formatting in the comments below.