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
23.2 KB Conditional Formats with Custom Rules - Completed.xlsx
23.2 KB

Quick reference

Conditional Formats with Custom Rules

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 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 results 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:05 In this video, were gonna continue looking at conditional formats.
  • 00:08 But this time we're gonna set up Custom Conditional Formats.
  • 00:11 I wanna take a look at my dataset here, and
  • 00:13 what I'd like to do is I'd like to apply a rule to this dataset that says if
  • 00:18 the values in any of these sales were less than 25,000, I'd like to highlight it.
  • 00:23 So to do that, I'm gonna go to conditional formatting, and
  • 00:26 while I could use a highlight cells rule, I wanna set up my own with a new rule.
  • 00:31 I'm gonna use a formula to determine which cells to format, and what I'd like to
  • 00:36 check is I'd like to check to see where each of these cells is less than 25,000.
  • 00:41 So I'm gonna click here, select my cell.
  • 00:45 Now the challenge is it comes up as absolute, so
  • 00:47 I'm gonna press F4 a couple times to toggle that to relative.
  • 00:51 And now we'll say less than 25,000 and click our RefEdit box to come back in.
  • 00:58 By changing this, this is now a relative formula that will apply to
  • 01:01 each row down there, no longer applying just to cell C6.
  • 01:05 Now I'm going to choose to format.
  • 01:08 And I'm gonna go to my Font, and I think I'm gonna change my font color
  • 01:12 to a nice dark red, and then I'm gonna go to Fill.
  • 01:15 And I'm gonna change this to a, well,
  • 01:18 let's go with a slightly orangey color on the background.
  • 01:22 So that now, when I say OK, I get something that looks like red on orange.
  • 01:26 I will say OK.
  • 01:28 And what you'll see is that a couple of values,
  • 01:29 the ones that are actually under 25,000 have been highlighted.
  • 01:33 Now, let's set up another rule.
  • 01:35 Conditional Formatting, New Rule, again, using a formula.
  • 01:41 And this time, I'm gonna go and choose again, C6, we'll toggle it to be absolute,
  • 01:46 and we're gonna see if this is greater than 50,000.
  • 01:53 If the values are greater than 50,000,
  • 01:55 then we'll go and we'll format them with a yellow background.
  • 02:00 And for the font, I think we'll go with a nice dark red.
  • 02:04 And we'll say OK, and OK, and
  • 02:06 you can see that anything that's over 50,000 has been highlighted.
  • 02:11 Now time for one more rule.
  • 02:12 Conditional Formatting, New Rule, then we go Use a formula to determine it again.
  • 02:19 This one once again, will point to C6 on a relative basis.
  • 02:24 And this time we'll check if the values are greater than 75,000.
  • 02:30 If they are,
  • 02:32 what I'd like to do is I'd like to change the fill to a green background.
  • 02:37 And we'll say OK, and OK.
  • 02:40 And at this point we need to take a quick look at the rules to make sure that
  • 02:43 everything is actually coming out the way that we expected it to do so.
  • 02:48 And the one thing that's a little bit strange here
  • 02:51 is that I did not configure a rule to show green with a red font.
  • 02:56 What's going on with that?
  • 02:58 So in order to figure this out, we need to go back and
  • 03:01 look at our conditional formatting rules in the Manage window.
  • 03:05 Now, you'll notice that we can see each of the rules here.
  • 03:09 And it's very clear that I've got green with a black font, and yet
  • 03:13 here I've got green with a red font.
  • 03:16 The yellow with red font is working, and the red on orange is also working.
  • 03:21 But why, when I never configured a green rule with a red font,
  • 03:24 am I getting that combination?
  • 03:26 And this is a little bit strange.
  • 03:28 And the answer to this is that Excel actually starts applying
  • 03:32 its conditional formatting rules from top to bottom.
  • 03:35 If I go and take a look at this rule in detail, when we say Edit,
  • 03:39 you'll notice that when I chose my formats I chose a green background, but
  • 03:44 I never set a font color at all, it's using automatic.
  • 03:48 Which is fine.
  • 03:50 Now, the thing is is when you actually go and look at this though,
  • 03:54 the way that Excel starts, it starts applying these rules from top to bottom.
  • 03:57 So it applies the green background to the cell but
  • 04:00 it doesn't apply a font color because it didn't need to.
  • 04:03 And then the next rule gets triggered.
  • 04:05 Is C6 greater than 50,000?
  • 04:07 Which in this case, works out too because its relative is C18, greater than 50,000,
  • 04:12 and it says yes, it is.
  • 04:13 But it cannot set the green background because that's already been set.
  • 04:17 But you never chose a font color, so we can set that.
  • 04:20 So this is a little bit weird.
  • 04:22 Now, if want to play around with this and make this work so
  • 04:25 that it always works properly, we have the option of changing the order of our
  • 04:29 rules using the up/down buttons to make them work.
  • 04:33 Or we can actually come back here and
  • 04:34 say, stop processing any further rules if this rule gets triggered.
  • 04:40 And at that point, we stop if true applied.
  • 04:42 When I say Apply, you'll see that it actually applies that rule and
  • 04:46 stops processing anymore.
  • 04:48 So this is how we can set up custom conditional formats and
  • 04:52 also how stop if true can help us if our rules start to conflict with each other.

Lesson notes are only available for subscribers.

Gift this course