Conditional Formats with Custom Rules

Quick reference

• 00:04 In this video we're going to use some conditional formats again, but
• 00:07 we're gonna set up our very own from scratch.
• 00:10 So what I'm gonna do is I'm going to set up
• 00:13 three different rules across these numbers here.
• 00:16 And to do this we'll go to Conditional Formatting.
• 00:18 And we're gonna go to New Rule.
• 00:22 We'll then click User Formula to determine which cells to format.
• 00:26 And one of the things we always want to do when we're setting up these rules is
• 00:29 start our selection so that it's in the top.
• 00:31 In that way when we're writing our formulas to look at something and
• 00:36 say, you know is this cell C4, I can remove the dollar signs from this.
• 00:41 That's gonna be important, because I want this to be relative.
• 00:44 But if I start building my formula from the top cell
• 00:47 it makes it a lot easier to sort of understand what's going on.
• 00:50 So we'll say, is C4 less than 25,000.
• 00:55 And what we'll do is we'll go to Format.
• 00:57 And I'm gonna go to Fill.
• 00:59 And we'll select a red font and say, okay.
• 01:05 And say, okay.
• 01:07 And you'll notice that it highlights a couple of my cells here.
• 01:10 So let's go set up another rule.
• 01:11 We'll go to Format, New Rule, use a formula.
• 01:16 We'll go and we'll select the cell again.
• 01:19 This time I'm gonna press F4 to toggle the absolute and relative referencing off.
• 01:24 There we go.
• 01:25 We'll say, is it greater than 50,000?
• 01:30 And we'll go and save format and we'll apply a yellow background,
• 01:36 and we'll go and we'll set our font to a dark red color.
• 01:41 And we'll say okay, and okay, now it looks pretty sharp.
• 01:46 Let's go and select one more rule, we'll say New Rule, use a formula.
• 01:53 Once again, we'll say equals, we'll grab C4,
• 01:57 press F4 three times to toggle it to relative.
• 02:02 We'll say, is it greater than 75,000?
• 02:05 Gonna go to Format, Fill, and we'll select a green background.
• 02:12 Here, how about this one?
• 02:13 And say okay, and okay.
• 02:17 And now the big question that we have to ask ourselves is everything looks like
• 02:21 it's fine except that I didn't choose a red font for this green background,
• 02:26 I thought I chose a black font.
• 02:29 So what is going on with this?
• 02:30 How did I end up getting a set of rules that looks like this?
• 02:35 So at this point.
• 02:36 In order to understand what's happening here.
• 02:38 We need to go into Conditional Formatting.
• 02:39 And we need to go to Manage Rules.
• 02:41 And you'll notice that I have selected one cell.
• 02:43 And it's gonna bring up all three of the different rules that I created.
• 02:47 The key that we need to understand here is that rules are applied in a certain order.
• 02:51 And they start with the first rule.
• 02:53 And then it applies the second rule.
• 02:54 And then it applies the third rule.
• 02:56 So what's gonna happen here is, as it goes through these rules,
• 03:00 it says all right, what is the rule that you've select for
• 03:04 this cell that actually has the $75,000 in it?
• 03:07 It says as a rule is it greater than 75,000, yes it is.
• 03:11 So let's apply the green background.
• 03:14 Remember, when I set this, all I chose was the green background.
• 03:17 I did not set the font.
• 03:19 So then, the next set of rules is applied to all of these cells.
• 03:23 And it says all right, is the cell greater than 50,000?
• 03:27 So in these cases, it's absolutely greater thank 50,000, so
• 03:29 it applies the yellow background with the red font.
• 03:32 But it also, because the green rule is executed first,
• 03:36 Excel then steps down, and says, I'm gonna try and apply the yellow rule.
• 03:40 And it says, is this number greater than 50,000?
• 03:43 And of course it is.
• 03:46 It then tries to apply its format.
• 03:47 And it says, well,
• 03:49 I can't apply the yellow background because you already applied a green one.
• 03:53 So I can't override it.
• 03:55 But you never chose a font color.
• 03:58 This rule has a red font declared, and therefore,
• 04:01 we get a green background with a red font.
• 04:04 So that's what's going on, and it's a little bit weird.
• 04:07 In order to stop that, what we need to do, is we need to say, if these rules
• 04:10 are applied in the order that they're shown here, we can either change
• 04:14 the order, move it around by using the up and down arrows to set it differently.
• 04:17 Or, we can say stop processing rules if this rule is true.
• 04:24 When we do that and we say apply.
• 04:26 What happens now is as soon as it hits this rule and it applies the green and
• 04:30 black, it stops applying any more rules in the chain of rules that have been set up.
• 04:36 Okay, so that's one of those things that you wanna be little bit careful of
• 04:40 when you're actually building custom conditional formats as to how
• 04:42 they're applied.
• 04:43 And that's how stop is true or
• 04:45 stop if true can actually help you control the orders in what's actually happening.

Lesson notes are only available for subscribers.