Understanding Conditional Formatting Rule Precedence

Quick reference

• 00:05 It's really important when you're working with conditional
• 00:08 formatting rules to understand how the rule precedents and
• 00:11 order of the rules can actually affect the results.
• 00:14 Now, I'm using the exact same rules that we set up in the past example, however,
• 00:19 I flip the order to be exactly opposite so they apply in a different way.
• 00:23 Now, what I'm gonna do is I'm also going to update the data here to show you
• 00:28 how this can actually affect things.
• 00:31 So if I go and quickly flip things out here, what you're gonna see is that we've
• 00:34 got some funny and some wonky results going on.
• 00:39 In a case of row eleven here, this meets only one rule.
• 00:44 This is just the orange rule background that we actually set up.
• 00:47 In the case of row ten, this one actually triggers true for both rules one and
• 00:53 three, this is the orange rule as well the green rule with white text.
• 00:58 Row 12 actually meets both the orange rule and
• 01:02 the rule with red text with both in italics.
• 01:06 If we go down a little bit further even, we can see that Row 23 here,
• 01:11 this one the rule that's actually being applied is only the third rule,
• 01:14 which is the green with white text.
• 01:17 Row 24 is the green rule but also the red text rule.
• 01:22 And on row 36 going down a little bit further, we've got the individual rule,
• 01:27 just the white text or red text on a white background.
• 01:30 So we've got all kinds of crazy
• 01:33 things going on in here that we didn't really expect.
• 01:36 So why?
• 01:37 Well he's another secret when you go in to manage your rules, you only need to click
• 01:41 one cell within the entire range in order to manage the rule set.
• 01:44 Because, when you go into Manage Rules, it will tell you
• 01:47 where these rules actually apply to you so you can always change them.
• 01:52 Now, I wanna show you what happens with these.
• 01:55 The way that a conditional formatting rule is applied, is it always applies
• 01:59 the first rule on the list first and then if that's true, it will apply it.
• 02:04 And then it moves to the next rule and attempts to apply that.
• 02:07 However, there's a problem here.
• 02:10 The challenge is that you can only set an element once,
• 02:13 a rule will never override an element.
• 02:16 So in this particular case, what we can see is we set the color of orange but
• 02:22 what we didn't do, is we didn't actually change the font at all.
• 02:26 We never said give us a black font.
• 02:28 We just left it as default.
• 02:29 In the next rule we said I'd like to make my font red and italic and
• 02:34 bold but I don't wanna do anything with the background,
• 02:37 I never changed anything there.
• 02:38 So it's not like it has any white background rule set,
• 02:40 it doesn't have a background rule set at all.
• 02:43 The third rule, we set both the font to white and the background to green.
• 02:48 So the challenge is if this rule triggers is true it sets the background to orange.
• 02:53 If this rule triggers to true, it also changes the font.
• 02:57 So, by the time this rule comes along it can only change the background.
• 03:01 If the background wasn't set here, and
• 03:03 it can only change the font if the font wasn't set here.
• 03:06 So the way that these rules apply is important.
• 03:10 So now we need to say, okay, well, what happens if we make some changes?
• 03:14 Well, we've got this cool little flag called Stop If True.
• 03:17 What Stop If True will do, is it will actually allow you to
• 03:21 apply the first rule, in this case, it doesn't stop if that's true.
• 03:25 It keeps on going.
• 03:26 It will then make another test and apply this.
• 03:28 If this rule triggers as true.
• 03:30 It will eject and get out immediately, so it won't even try and
• 03:34 apply this green font.
• 03:36 Why this is important?
• 03:37 Let's go say OK.
• 03:39 Doesn't really look like a ton changed here until you go down to row 24.
• 03:45 Row 24 had a green background because this rule has been triggered as true,
• 03:49 it says no more processing.
• 03:51 I never set an orange background on this,
• 03:53 which meant the green background could be applied.
• 03:56 We're just going to eject right away and don't really worry about it.
• 03:59 So now we get back to our red with no background without the green being applied
• 04:03 and you won't see that happen anywhere else in the data set.
• 04:06 No more green actually happening on here with the red text.
• 04:09 Can we also do anything different with this?
• 04:12 Well, yeah, we can play with the order of the rules as well.
• 04:16 So, if we go back in and say, all right,
• 04:19 well, what if we put a Stop If True on rule number one?
• 04:22 What you'll see is that every one of these items Look at
• 04:27 that they all stop cuz as soon as it hits this is true it ejects and says no more.
• 04:31 I'm just gonna take that one off.
• 04:33 The other thing that I'm gonna do is I'm gonna say well what if I did this?
• 04:36 What if I right click, not right click, sorry.
• 04:39 What if I go click here and say move to this one to the end.
• 04:42 Now this will only apply if these other rules haven't been set.
• 04:46 Apply, and OK.
• 04:49 So now what we can see is that my green and white rule.
• 04:52 The white applies to the orange, I've got a bunch of greens, and the reds,
• 04:56 again, based on the rule precedent order only trigger if the orange and
• 04:59 green rules are not true.
• 05:01 So, it's kind of an interesting wave.
• 05:03 The rules order makes a difference.
• 05:04 The Stop If True can make a difference.
• 05:06 The big key to remember on this is that if an element has been set,
• 05:10 it can never be set later on, by a different formula.

Lesson notes are only available for subscribers.