Conditional Formats with Custom Rules

Locked lesson.

Upgrade

About this lesson

Learn to create custom conditional formatting rules based on formulas.

Quick reference

Conditional formatting – custom rules.

Description

Creating custom conditional formatting rules based off formulas.

Where/when to use the technique

Conditional formatting is used to highlight key pieces of data that meets certain conditions.  Unfortunately Excel’s defaults won’t cover every scenario we need, so we have the ability to build our own rules to suit.

Instructions

Creating Custom Rules

  • Select C4:C19, go to the Home tab --> Conditional Formatting --> New Rule
  • Change the 2-Color Scale to Classic
  • Change the “Format only top or bottom ranked values” to Use a formula to determine which cells to format”
  • Enter =C4<25000 and click Format
  • Change “light red fill with dark red text” to “custom format…”
  • From the Fill tab select a light red background and click OK to exit the rules
  • Create 2 more new rules
    • =C4>50000 to apply a yellow background with a dark red font
    • =C4>75000 to apply a light green background (click Clear on the Font tab)
  • Notice the rules have been applied

Rule Precedence

  • Why do we have green cells with red font when we didn’t set a rule that way?

Managing Custom Rules

  • Select C4:C19, go to the Home tab --> Conditional Formatting --> Manage Rules
  • Notice the rules are applied top to bottom
  • Move the yellow rule up and all the green goes away
  • Move it back down

Understanding precedence

  • The Green and Red rules do not change the font, but all rules change the fill color
  • An element cannot be changed once set
  • The green rule set the fill (>75000), then the yellow rule (>50000) can’t change the color, but it can change the font, so it does resulting in a green fill with red font.
  • On the green rule, select Stop if True: the green rule will now have a black font as the engine will stop processing any more rules once the >75000 condition is met
Login to download
  • 00:04 In this video we're going to look at taking conditional formats one step
  • 00:08 further.
  • 00:09 We're not gonna use the built-in stuff.
  • 00:10 Instead, we're gonna go out and we're going to do this on our own.
  • 00:12 We're gonna figure out how the stuff actually works.
  • 00:15 So, we're gonna go and grab this entire set of data.
  • 00:18 And we're gonna do a little bit of data mining with conditional formats.
  • 00:21 So, the first thing that we do is go to condition formatting, and
  • 00:24 I'm gonna say new rule.
  • 00:27 When I come in here, it automatically offers me a two color scale.
  • 00:30 But I don't really want that.
  • 00:31 What I wanna do is I actually wanna pick up a classic rule.
  • 00:36 And I don't really wanna do this either.
  • 00:37 What I'd like to do is actually go and
  • 00:39 provide my own formula to determine which cells are gonna be format.
  • 00:44 Now, the secret here is we're gonna click in this box and we're gonna say,
  • 00:48 all right.
  • 00:48 When we look at our range, you'll notice that the top cell has a white background.
  • 00:52 That's cause it's the first cell we selected.
  • 00:54 This is the active cell, and we build all of our conditional formatting formulas
  • 00:58 based on the position of the active cell.
  • 01:00 So, what we're going to do here is we're gonna say, all right,
  • 01:05 I'd like to see if this cell here is less than 25,000.
  • 01:10 But there's a challenge here.
  • 01:12 If I go and apply this,
  • 01:13 it's gonna evaluate this formula to see if it's true or false.
  • 01:16 And then for the next row it will evaluate, because this is an absolute,
  • 01:20 the exact same formula.
  • 01:21 So, we don't want that.
  • 01:22 We wanna make this relative.
  • 01:23 So, we're gonna knock these dollar signs off of here.
  • 01:25 In this way, when it actually moves down and
  • 01:28 it tries to apply this formula to the next cell down, it will now be relative.
  • 01:32 So, it'll say D4 or C5.
  • 01:35 So, with that in place, we say, okay, that's great.
  • 01:38 What I'd like to do though, is I'd also like to provide a custom format.
  • 01:41 I'm gonna do this, make this go completely my way.
  • 01:45 And what I'm gonna do in here is, I'm gonna go and say, we'll, let's go and
  • 01:49 select a fill.
  • 01:49 And, sure, light red looks like a great background color for
  • 01:52 me to go with right now.
  • 01:54 So, I'm gonna say, OK.
  • 01:56 And I'm gonna say, OK.
  • 01:59 And it comes back, and it provides me with a red fill with red text.
  • 02:04 Now, what I'd like to do next is I'd like to actually go and add another rule.
  • 02:08 So, I'm gonna go conditional formatting, new rule.
  • 02:12 Once again, I'm gonna go classic.
  • 02:15 Once again, I'm gonna go and provide a formula with cells to format.
  • 02:19 And once again, I'm gonna say equals this cell here,
  • 02:23 although I'm gonna press F4 a couple of times to get rid of the absolutes.
  • 02:27 This time, I'm going to say, I'd like to see if it's greater than 50,000.
  • 02:34 And again, we'll provide a custom format.
  • 02:41 And I'm not gonna provide a red background this time.
  • 02:44 I think this time I'll go with yellow.
  • 02:46 And let's see, what else can we do?
  • 02:48 I'm not gonna bother with the borders.
  • 02:50 The text, well, sure, why don't we make, should make the text red?
  • 02:55 That'll work for us, why not?
  • 02:56 So, we'll say OK.
  • 02:59 And we'll say, OK.
  • 03:01 That works nicely, perfect.
  • 03:02 Let's do one more.
  • 03:03 Let's do another conditional formatting rule to see about cells that are greater
  • 03:08 than 75,000.
  • 03:09 So, we'll go and we'll pick up a formula to format and we'll go and
  • 03:14 click on this guy here.
  • 03:15 We'll change this to be absolute greater than 75,000.
  • 03:20 Let's put in some zeros, that'll work a little better.
  • 03:22 We'll go and provide a custom format that has a light red fill.
  • 03:30 No, I think we'll go and we'll change this one out, actually to something different.
  • 03:33 We'll go with a nice light green fill instead.
  • 03:37 There we go.
  • 03:38 And now we'll say, OK.
  • 03:41 And OK.
  • 03:43 And what you'll notice is that's something strange has happened.
  • 03:47 Did I asked for a green fill with red text?
  • 03:51 No, I really didn't.
  • 03:52 I asked for a green fill.
  • 03:54 I didn't ask for red text at all.
  • 03:55 So, at this point we need to go back in and we need to manage our rules and
  • 03:58 see what's going on.
  • 03:59 And this is what happens with conditional formats is that,
  • 04:02 you actually can get into an issue with rule precedent.
  • 04:06 What we can do here is we can move these formulas up and down here.
  • 04:11 These get applied in order.
  • 04:12 So, I could move this guy up and say, OK.
  • 04:15 And you'll see that now the rule takes over yellow completely.
  • 04:18 Well, that's not ideal, so let's go back into manage rules again here.
  • 04:22 We'll move him back down.
  • 04:24 What I can do, though, is I can see that the rules actually apply in this order.
  • 04:28 First off, it tries to apply this rule.
  • 04:32 Then it applies this one, then it applies this one.
  • 04:32 What's actually happening here is that we only set a green background here,
  • 04:36 we didn't set a font color.
  • 04:39 When it get's to the next rule, it says is it greater than 75,000?
  • 04:42 Yes, this is. So, it'll apply when I say OK,
  • 04:44 a green background to that cell.
  • 04:48 Then what it does is it comes along and it says, all right, well,
  • 04:51 next up is this cell greater than 50,000.
  • 04:53 Yes, it is.
  • 04:54 I'd like to apply a yellow background with red text.
  • 04:56 And it says, we already set a green background, it can't change that.
  • 05:00 But you didn't apply red text, so I'll change that for you.
  • 05:03 So, it does them separately.
  • 05:05 If we wanna stop that from happening, we need to click this stop if true button.
  • 05:09 And at that point, this rule will execute and then leave their processing chain,
  • 05:13 no more things will happen.
  • 05:15 And that will get us back to the original rule that we originally set up as well.
  • 05:19 So, it's a really powerful tool, but
  • 05:21 you have to be a little bit aware of some of the gotcha's that can happen there.

Lesson notes are only available for subscribers.

Gift this course