Conditional Formats with Custom Rules

Quick reference

• 00:04 Built-in conditional formatting rules are awesome, but
• 00:06 sometimes I want something a little bit different.
• 00:08 For example,
• 00:09 I want to highlight an entire row based on what a value is in the data set.
• 00:15 So, how do we do that?
• 00:16 We're going to build our own conditional formatting rule using a formula.
• 00:20 The first part that you want to know, though, when you're going to set the cell,
• 00:24 never select your cells from the bottom or from the right or
• 00:27 from the bottom right to try and build your conditional formatting setup.
• 00:31 This white cell here is the active cell.
• 00:34 We want that in the top left of our data set.
• 00:37 If you don't, you are going to hurt your head trying to work out the offsets and
• 00:40 why your conditional format is not triggering the way you want.
• 00:43 Always start on the top left.
• 00:46 Once we've done this, we're going to go and say New Rule,
• 00:49 and we're going to choose to use a formula to determine which cells to format.
• 00:53 What I'm interested in is where C6 is less than $25,000, but here's the thing.
• 01:02 This is going to trigger from A6 to begin with, and
• 01:05 we're checking if $C$6 is less than 25,000.
• 01:09 When I get to B6, that's fine, it will do the same thing.
• 01:12 But when I move down the row, I certainly don't want to targeting here,
• 01:15 I want targeting the next row.
• 01:17 So I need to make sure that this is not an absolute row, it is a rows of row,
• 01:22 that column needs to stay anchored.
• 01:24 Once I have that in place, I can set up my rules.
• 01:27 So we're going to go and choose.
• 01:28 So we're going to make this a bold font.
• 01:30 We're going to make it white on a dark red background because these are the records
• 01:34 we don't want to see.
• 01:35 I'm going to say, OK and OK.
• 01:38 And there we go, we've highlighted our entire row for both the records,
• 01:42 where the sales dollars are less than 25,000.
• 01:45 Now, let's find out our next tier on this.
• 01:48 So we're going to go to Conditional Formatting > New Rule.
• 01:51 We're going to make another conditional formatting rule in the same data set.
• 01:55 Again, we're starting from the top left, so we're going to go a C6,
• 01:58 we'll just press F4 a couple times to toggle the absolute cell reference.
• 02:02 And this time, I'm going to check if our sales are greater than $50,000.
• 02:08 And if they are, I'm going to put on a yellow cell here,
• 02:12 and I'm going to make my font a dark red.
• 02:16 There we go.
• 02:17 We'll say, OK, we'll say, OK.
• 02:19 And it looks a little garish, but it is definitely working.
• 02:23 One more rule, Conditional Formatting > New Rule, and use a formula.
• 02:30 Once again, we're going to check C6.
• 02:33 We're going to toggle the absolute and cell referencing there.
• 02:37 And this time, we're going to check if the value is greater than 75,000.
• 02:41 If it's greater than 75,000, we're going to go and
• 02:44 we're going to change the fill, and we're going to use a light green fill.
• 02:47 But I'm not going to bother changing the font at all.
• 02:51 We'll say, OK, and we'll say, OK.
• 02:54 And now, what you should notice is on this row,
• 02:59 the green worked, but it has a red font.
• 03:04 I did not set up a rule for a green background with a red font.
• 03:08 I set up a rule for a green background.
• 03:10 And the font, I think, should stay black.
• 03:14 So what is going on?
• 03:16 Well, this is where we get into rule collisions.
• 03:19 And this is one of those things that you've gotta be a little bit careful about
• 03:22 if you're building your own rules.
• 03:23 So let's go into Conditional Formatting and Manage Rules and
• 03:27 see what's going on here.
• 03:29 The way that your conditional formatting rule is applied isn't always applied from
• 03:34 top to bottom.
• 03:35 And you can move these rules around and change the order just by clicking on
• 03:39 a rule and choosing to move it down or move it up and see what happens.
• 03:42 The challenge is if I move this rule down and apply it, you'll notice that I don't
• 03:46 get any green, even though there's supposed to be green here.
• 03:49 So it definitely needs to be coming first and Apply.
• 03:53 Why am I getting a green rule with a red font, though,
• 03:56 when that hasn't been configured?
• 03:58 And the answer comes into what's actually been configured in this rule.
• 04:02 If I go into Edit, I configured from the format just the color,
• 04:07 but I didn't do anything with the font at all.
• 04:10 What happens when you're actually looking at these rules is it applies the pieces
• 04:16 that you've actually done here, and the next rule can only apply new things.
• 04:22 It cannot override the color, but because we never defined a font,
• 04:26 it can change the font color.
• 04:28 So this is a little bit weird, okay?
• 04:30 And this is where you get into these conflicts where two different things
• 04:33 are happening on one rule that you didn't configure.
• 04:35 The secret to stopping this is to actually stop rule processing if this
• 04:40 rule is positive, okay?
• 04:42 So if it triggers this one, say, Stop if True, and don't actually go and
• 04:46 run any of the other rules down below.
• 04:49 You can do this for all individual rules.
• 04:51 In this case, the only one that's absolutely necessary is this one.
• 04:54 When we click Apply and OK, what you'll see is that we now have green with black.
• 05:00 And it stops processing here.
• 05:01 The yellow rule will continue on, but there's no conflict there.

Lesson notes are only available for subscribers.