Locked lesson.
About this lesson
Leveraging the logic of the IF() function to drive conditional formats based on formulas.
Exercise files
Download this lesson’s related exercise files.
Formula based conditional formats.xlsx36 KB Formula based conditional formats - Completed.xlsx
36.2 KB
Quick reference
Formula Based Conditional Formats
Creating custom conditional formatting rules based off formulas.
When to use
To create rules that are more complicated than those allowed via the standard “highlight cells” rules.
Instructions
Creating Custom Rules
- Select your data starting in the top left corner of your data set
- Go to the Home tab --> Conditional Formatting --> New Rule
- Choose “use a formula to determine which cells to format”
- Enter a formula that tests your data
- Click Format
- Configure the formatting as you’d like to see applied
Modifying Custom Rules
- Select any cell that holds the conditional formatting rule
- Go to the Home tab --> Conditional Formatting --> Manage Rules
- Locate and select your rule, then click Edit
- Make your modifications as desired
Hints & tips
- Use F4 to toggle the absolute/relative state of your formulas while you are building them
- When editing rules, you can apply them from the conditional formatting rules manager
- Always select your data from the top left when you are building rules, as it makes it easier to set up relative/absolute references correctly
- Many rules can be set up that overlap
- 00:04 We're now gonna get into writing our own formulas to drive conditional formats.
- 00:10 And this is really cool, cuz now we can start getting into some really complicated
- 00:14 scenarios that work the way we want conditional formatting to work.
- 00:18 The first thing we need to do is we need to select some data.
- 00:20 Now, the key that I want is I want to be able to highlight the entire row when
- 00:25 some of these conditions up above are met.
- 00:29 In order to write something like that, I highly advise you that you always start by
- 00:34 selecting the first cell in the top left corner of your data range.
- 00:37 I notice I haven't pickup my header, I've got the first row of data.
- 00:41 I then need to select my data area, so I'm gonna hold down Ctrl+Shift.
- 00:45 And I'm gonna press End, and when I do that, it selects the entire range.
- 00:49 Of course, you could do this with a mouse if you're not comfortable with
- 00:51 the form of the shortcut.
- 00:51 But Ctrl+Shift+End would grab you from the top left hand corner, when you're there,
- 00:56 all the way down to the very end of your data.
- 00:58 So that actually looks nice, I've got a nice, big contiguous range selected.
- 01:02 And you'll notice that my active cell, the point which all my formulas
- 01:06 will be based upon for the relative and absolute referencing is cell A10.
- 01:12 So now that we've got that selected, let's go build some rules.
- 01:16 We're gonna go to conditional formatting, we're gonna go this time to New Rule,
- 01:20 and we're gonna choose to use a formula.
- 01:23 And in this particular formula,
- 01:24 the first thing that I wanna check is I wanna check whether or not the vendor.
- 01:29 And now remember, I'm starting from the first row, so
- 01:31 I'm gonna use the vendor from the first row.
- 01:34 I wanna check if that equals, The vendor name that I have in my cell,
- 01:40 but there's a problem here, and it has to do with these guys here.
- 01:43 Because right now every single row, every cell will check C10, this guy here,
- 01:48 it's locked in.
- 01:50 So I'm going to press F4 to toggle this, and you'll notice that at this point it
- 01:54 would lock in the row and the columns would be relative, I don't want that.
- 01:57 I want to make sure that no matter whether I'm calling it from A, B, or C,
- 02:01 it's always pointing at these columns C.
- 02:05 But when I start moving down the rows, it's gonna be relative,
- 02:07 which is why there's no dollar sign in front of the 10.
- 02:09 So we pay the column to stay put, we pay the row to stay put or not as we need.
- 02:14 D5 however, I always want this locked in, no matter where I am in this thing,
- 02:17 it always needs to point back to D5.
- 02:19 Now that I've got that set, I'm gonna go with change your format,
- 02:23 I'm gonna go to Fill, and I'm gonna choose orange.
- 02:26 I'm gonna say OK, and we'll say OK.
- 02:30 And it doesn't appear that anything happens, but when I scroll down, you can
- 02:34 see that all of the vendors that have High Alpine have been highlighted in orange.
- 02:38 So that looks like it worked perfectly, that's highlighted the entire row as well,
- 02:41 which is great.
- 02:42 So I'm gonna go back up, I have not unselected my rules or my data,
- 02:46 cuz I'm going to make some more rules.
- 02:49 So I'm gonna go Conditional Formatting again, New Rule.
- 02:53 Once again, use a formula, and this time I'm going to check whether or
- 02:57 not the month number is equal to six.
- 03:00 So to do that I can't just check if this is equal to six,
- 03:03 because I need to know what about the months.
- 03:05 So I'm gonna use the month formula to say equals month of D10,
- 03:09 let's just toggle that F4, F4, there we are.
- 03:13 Now we've got it locked in so is the month of column D,
- 03:17 whatever the row is, is that equal to.
- 03:20 And we'll lock this one in, always point to D6.
- 03:23 If it is, let's go to Format, we'll go to Font,
- 03:27 we're gonna choose a red font, Bold and Italic, and we'll say, OK and OK.
- 03:34 And now you can see that the row that has six for a month for
- 03:38 June has highlighted in red.
- 03:40 What's really neat is, as when I scroll down here,
- 03:43 you'll notice that I never setup a red and orange rule.
- 03:46 And yet, they layer, right?
- 03:47 So that's pretty cool.
- 03:49 You can actually build these things that layer on top of each other.
- 03:52 Let's do one more.
- 03:53 This one's gonna be a little bit more complicated.
- 03:56 Go to Conditional Formatting > New Rule, use a formula, and in this one I need
- 04:01 to check if the units sold are less than 7 and if the sales are greater than 1500.
- 04:07 So in this one, we've gotta bust out that old AND.
- 04:10 So if units sold is less than 7,
- 04:14 except that I just realized that this guy here,
- 04:20 I need to go and say F4 and F4 to lock him into column E.
- 04:28 If that's less than D7 and my sales, we'll click on this.
- 04:33 We'll press F4, F4, there we go.
- 04:37 If that is greater than 1,500,
- 04:41 then the rule will trigger as true.
- 04:45 So we'll say format, and on this one we're gonna go and
- 04:48 we're gonna choose a nice dark green background.
- 04:52 We're gonna go with a white font, which hopefully will make our preview white.
- 04:57 I'm not sure why we could have the color in here, but hey,
- 05:00 we're also gonna set this one to Bold, and we'll see, okay?
- 05:04 And we'll say OK, and now you can see that where our units sold is less than 7 and
- 05:09 the sales are greater than 1,500, we get those nicely highlighted.
- 05:14 And you can see in this case, it helpfully goes and
- 05:17 overwrites the rule that we have here.
- 05:19 So this is kinda cool, we've got the ability to build some
- 05:22 really complex formulas in order to drive our conditional format.
Lesson notes are only available for subscribers.