Today's video is about
Conditional Formatting in Excel. I'm going to show you
how you can use formulas in Conditional Formatting and how you can conditionally format a range based on the
value of another cell. So for example, conditionally
format a row in Excel based on a sales value in that row? This way you can highlight a row or adjust the font color of
a row based on another cell. It's going to be fun. Let's get to it. (upbeat music) In this example, I have lists of names and the
yearly salary for each person. I want to format the values order rows if the salary is greater than or equal to the salary I type in here. So I'm going to do two
different versions of this. In the first version, I just want to highlight the values, in the second version I
want to highlight the rows where this condition is true because I'm adjusting the
formatting based on a condition, I need to use Conditional Formatting. So first thing I need to do is highlight the range I want to format, go to Conditional Formatting and apply a new rule. I need to use a formula to
determine which cells to format because the formatting of these cells is dependent on the value of another cell, and I can check that with a formula. I input the formula in here, Conditional Formatting
is going to check whether the result of this
formula is true or false. If it's true, it's going to apply to
formatting that I define here, if it's false, it's going to
leave the formatting as is. So it all comes down
to the formula in here, so I'm going to start
off with the equal sign. Now what type of formula do you write? Well here's the other rule when using Conditional Formatting, the formula that you type is always from the point
of view of the first cell in the range that you've highlighted. This is from the point of
view of B6 in this case. I want to check if this is
greater than or equal to B3 here, notice as I'm doing the
cell references here, Excel is automatically fully
fixing the sign reference, is putting the dollar sign for
both the column and the row. So is this going to work? Well let's try it. I'm going to apply the
formatting that I want, once you go to Format,
you can select between Number, Font Formatting, so you can adjust the font color or make the font bold or italic, you can adjust the border
and the fill color. So in this case, I'm just
going to go with the fill color and click on OK. That was my formula, right? Actually I'm just going to copy it as well and click on OK. Nothing happens, why? Because B6 is not greater than 80,000. If I change this to 80,000 or 81,000, because my formula was
greater than or equal to everything is green, why? Because all of these cells
are looking at this cell here. I'm just going to press
Control+Z to go back? This is the confusing part
when you use a formula inside Conditional Formatting because you're basically typing a formula, that's applied from the point
of view of the first cell but then you have to imagine how that formula would
be if you pull it down to the other cells and not all of us can easily imagine this. So here's a trick you can use. I've seen Mike Girvin use this. It's a great way of visualizing
everything under grid, I really love the solution so I'm going to share it with you here. What you need to do is type
in your formula under grid right beside your values. So I copied it before so I'm just going to paste it in, that's the formula we had
in Conditional Formatting. Now the result of this is
false because 60,000 to 70 is not greater or equal to 80,000. When you pull this down
the result is also false because they're always
looking at the value in B6. I want to make this dynamics, either I have to remove
the dollar sign for the six or completely remove the dollar sign. So let's just send this down, and now we get true wherever the value is greater than 80,000 and false where it's not. This means all I need to do is go back and update my
Conditional Formatting rule by applying this formula
and everything should work. The only change was to remove
the dollar signs for B6. So I'm just going to go back
to Conditional Formatting. Now anytime you want to edit a rule, you can go to Manage Rules. Let's go and edit the rule and adjust this by removing the dollar signs. Is this going to work? We'll know when we click
on OK but before I do that, take a look to see if
you're already subscribed to this channel, and if you aren't and you like the type
of videos you see here, consider subscribing. So now let's go with OK
and everything is dynamic. I don't need this help anymore, I'm going to remove it. Let's double check if this goes to 80,000, it automatically gets highlighted. I can also adjust my number here. So if I go with 90,000, my formatting updates as well. Okay so I'm just going to go
with Control+Z to go back. Now let's figure out how
we can highlight the rows instead of just the values. Well here again it comes
down to the correct fixing. Let's do it on the grid first before we copy it to the
Conditional Formatting dialog box. Now remember the Conditional Formatting is going to be applied
to both of these, right? So for both name and yearly salary and I'm just going to
bring the titles here, so we know where we are at each stage. The formula that I type in, in the box is going to be applied
from the point of view of the first cell here
which is going to be E6. Now E6 needs to always
look at the value in F6 and compare it against this one here. Now I know that this is the
number I need to fully fix, so I'm going to press F4 to fully fix this but what about F6? Can I leave it like this? Well we can quickly check if
I bring this over and down, it doesn't really work
because here I get true and here I get false. Why is this false here? Well because it's moving
to the next column. Why is it true here? Because it was on the right column. This means I can't
leave this fully dynamic like I did before. I actually have to fix the column to make sure it doesn't shift, so I'm going to press F4 a few times, make sure F is fixed, that's not going to go to
G as this is pulled across and sent down. Now I get true for these rows. Okay so this is the type
of trick you can use to get Conditional Formatting right every time you're using a formula. Now all I have to do is copy this, highlight my range here, go to Conditional Formatting, New Rule, Use a Formula, pasting the range and apply the type of format I want. I'm going to go with green. We can also make the font bold and OK, and OK. I don't need these helper cells anymore, so I'm just going to remove them. Now when you use Conditional Formatting, there is something you
need to be aware of. Every time you copy a range
that has Conditional Formatting and you paste it somewhere else, you bring the Conditional Formatting with. If you don't want that, you can remove it by going
to Conditional Formatting, Clear Rules and clearing the
rules from the selected cells. You can also clear the
rules from the entire sheet and it will remove all of
your Conditional Formatting. Now another way of copy and pasting so that you don't have to do this step is to copy your range, go to the site here, Right Mouse click, go to Paste Special and select Formulas and Number Formats in case you have formulas
in your cells or values and Number Formats. And then after you click on OK, you get your number
formatting and your values but you don't get your
Conditional Formatting come with. A case of something to keep in mind if you're copying and pasting your ranges. So that's how you can use formulas in Conditional Formatting. You can use it to highlight
a row based on another cell but you can of course use it also with more complex formulas. Just remember that Conditional Formatting formats those cells where
your formula returns true. So if it's not working
double check on the grid, so basically on your
Excel Spreadsheet directly to see where the problem might be. I hope you enjoyed this video. If you did, don't forget
to hit that thumbs up, and if you haven't subscribed
to this channel yet consider subscribing so that I can see you in the next video. (upbeat music)
