Formula Based Conditional Formats

Quick reference

• 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.