Conditional Formats with Custom Rules

Locked lesson.

Upgrade

About this lesson

Learn to create custom conditional formatting rules based on formulas.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Conditional Formats with Custom Rules.xlsx
10.8 KB Conditional Formats with Custom Rules - Completed.xlsx
10.2 KB Conditional Formats with Custom Rules - Extra Practice.xlsx
13.6 KB

Quick reference

Topic

Creating custom conditional formatting rules based off formulas.

When to use

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 D4:D13, go to the Home tab > Conditional Formatting > New Rule
  • Choose “use a formula to determine which cells to format”
  • Enter =D4<25000 and click Format
  • From the Fill tab select a light red background and click OK to exit the rules
  • Create 2 more new rules
    • =D4>50000 to apply a yellow background with a dark red font
    • =D4>75000 to apply a light green background
    • 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 D4:D13, 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 colour
  • An element cannot be changed once set
  • The green rule set the fill (>75000), then the yellow rule (>50000) can’t change the colour, but it can change the font, so it does resulting in a green fill with red font
  • On the yellow 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 As great as the built in Conditional Formatting rules are in Excel there's never one that covers every instance of something you want to do. And
  • 00:11 to that end we actually have the ability to write custom conditional formatting rules as well based on the results of formulas.
  • 00:18 Before you do this, the number one thing that I recommend is you always highlight your entire range that you want to apply your conditional
  • 00:25 formatting to and make sure that the active cell is always in the top left. That will be the one with a different color.
  • 00:32 This is the one where you are going to write all of your formulas from and it's just so much easier to read them if you
  • 00:38 know you're starting in your top left. So once we've done that we go to Conditional Formatting
  • 00:43 click on new rule
  • 00:45 and down the bottom of this list there is "use a formula to determine which cells to format" we'll click on that,
  • 00:52 and that takes us to a formula area.
  • 00:55 So this is where we write a formula that needs to resolve to true or false. So in this particular case I'm going to look at D4
  • 01:02 and I'm going to change this to a relative reference so it gets applied to all the cells going down
  • 01:06 in our conditional formatting rule. And I'm going to look at
  • 01:10 values where D4 is less than $25,000. So when this rule is applied it'll be D4 is less than 25,000 here it'll be E4 is
  • 01:18 less than $25,000 when it applies it to the next rows down because it's relative.
  • 01:23 If that's true I want to go and apply a format and I'm going to go and pick off a fill. Let's grab a nice light red and say OK. And OK and you can
  • 01:34 see that the format gets applied to the only value in this range that's less than $25,000. Great.
  • 01:41 Now the cool thing here is that we can actually stack up multiple conditional formatting rules as well.
  • 01:45 So if I go back up here to Conditional Formatting and New Rule
  • 01:51 and expand the formula drop down again and once again lets build a new rule for D4 and we'll make it relative, is greater than 50,000.
  • 02:02 This time if that's true
  • 02:04 we're going to go and pick up a format where
  • 02:06 we're using the yellow background and we're going to pick off a nice red font here, dark red. That'll work nicely.
  • 02:14 We'll say OK apply that and there we go.
  • 02:19 Let's try one more.
  • 02:21 So go back to New Rule again
  • 02:23 use a formula
  • 02:25 and again we're going to pick up D4 and it's relative, greater than $75,000 this time.
  • 02:33 Format and we will pick up the
  • 02:37 green background. Why not, it's something different and say OK. We're not going to change the font here.
  • 02:43 And what happens when we apply this is something kind of curious.
  • 02:48 The backgrounds changed but when we went into our rule
  • 02:53 if we take a look at it
  • 02:55 we did not declare a rule with the red font in a green background. We declared a rule with a black font and
  • 03:02 green background so what actually ended up happening here?
  • 03:06 Well this is where we get into things called precedents and precedents can be a little bit
  • 03:10 confusing. If I move this rule up in its order and say apply
  • 03:14 it goes all yellow.
  • 03:17 If I move it down the fonts change on me. What's happening here is that neither this rule nor this
  • 03:23 rule actually set the color of the fonts at all but this rule does.But all three set the backgrounds
  • 03:30 and what ends up happening is the order the rules are applied from top to bottom
  • 03:34 and basically if you've applied an element format of some kind it cannot be overwritten. So what happens here is the
  • 03:43 green rule is applied but no font is set. That's why the green as background is set here
  • 03:48 and then it goes through and says well the next rule says I need a
  • 03:52 yellow background which I can't set because the green one has already been done but I can use a
  • 03:55 a red font so I'll apply that. And that's what's happening. So to stop this from going on
  • 04:00 we click stop if true and apply it and now get the proper rules. Precedents can be a little tricky to deal with but as you can see it can actually
  • 04:08 provide you with some incredible flexibility to write formats that work just the way that you want them.

Lesson notes are only available for subscribers.

Gift this course