Conditional Formats with Built-In Rules

Quick reference

• 00:04 In this video we're going to look at built in conditional formats, and
• 00:08 how they can really quickly get some great insights from your data.
• 00:11 I just wanna point out with this data set here, except for
• 00:15 the years that these distilleries were founded, all of the information that's
• 00:18 here is completely fabricated and doesn't mean anything.
• 00:20 So just keep that in mind as we go through.
• 00:23 Now, one of the things about scotch distilleries that everybody always wants
• 00:27 to know is which ones are the oldest, which ones have been around for
• 00:30 the longest.
• 00:31 So we can find that out very quickly by highlighting the years for
• 00:34 these different distilleries and saying conditional formatting.
• 00:38 And we'll go to top bottom rules and show me the bottom ten items.
• 00:42 Now, we don't even have ten items here,
• 00:45 but we can change this to say give me say the bottom three.
• 00:50 I also,
• 00:51 you can see that it's giving me a little bit of a live preview here of the colors.
• 00:55 Personally I'd rather see this in something like green fill with a dark
• 00:58 green text because this is something that is a sort of a factor as to how long
• 01:01 the thing has been running.
• 01:02 And you would think that the longer that the distillery has been going,
• 01:05 potentially the better the product, potentially.
• 01:08 So we'd say okay to that.
• 01:11 Now, we also might wanna look at the production of bottles and
• 01:14 say I'd like to see, tell me the production in here quickly that
• 01:19 shows me where the production of bottles is greater than 90,000 bottles a year.
• 01:23 So now we can go back to conditional formatting.
• 01:26 And let's go with a highlight cells rule and we'll say,
• 01:29 let's set up a greater than rule.
• 01:32 And it says format cells that are greater than and
• 01:34 it actually picks an arbitrary number out of this.
• 01:37 But I can actually say you know what?
• 01:39 I'd rather drive this from my own cell, thank you very much.
• 01:43 And we can then go and say, let's change this.
• 01:46 Let's go with a yellow fill with a dark yellow text.
• 01:49 What's really cool about these things with conditional formats is that they change.
• 01:52 So if I suddenly come around and say you know I'd like to see where that number is
• 01:55 greater than 50,000, I can change it and see what's going on.
• 01:59 Or 75,000.
• 02:01 And we can see that there's less values picked up, 85,000 for example.
• 02:04 And we can actually see that all of these things move around,
• 02:07 depending on what's going on.
• 02:08 What about revenues?
• 02:11 Let's see who has the biggest share of revenue in these faked out numbers.
• 02:15 So go to Conditional formatting.
• 02:16 We're gonna apply data bars.
• 02:19 I love data bars.
• 02:20 These things are fantastic.
• 02:22 What these do is they look at all of the data that you have in your set and
• 02:26 they give a fill that is proportional to the highest and
• 02:29 lowest numbers in your data set.
• 02:31 In this particular case,
• 02:33 it says, you know, these look like there are values between zero and whatever,
• 02:37 11.9 million, so these two would appear to be largest.
• 02:42 This is the next largest, and this is the smallest number in the dataset.
• 02:45 These things are great.
• 02:47 And again, they're completely relative.
• 02:49 If I were to go and change the first digit here to 9 for 91 million.
• 02:53 You can see that they all change right away.
• 02:57 If I go control zet and
• 02:58 bring that back, you can see that they all recalculate as well.
• 03:01 So these are really, really cool things.
• 03:03 Data bars are so useful for very quickly identifying how a number relates to
• 03:07 the other numbers in your data set.
• 03:11 We also have some cool little things with conditional formatting called icon sets.
• 03:15 So let's go set one of these up.
• 03:17 This is the conditional formatting, we're gonna go with icon set,
• 03:20 we're gonna pick these stars down the bottom here.
• 03:23 And you'll notice that again, it proportionally ranks everything here and
• 03:26 says, well ten is the highest number, 9.8 is the second highest number.
• 03:30 8.9, and 8.9 seem to fit in the middle compared to what else is going on.
• 03:35 But I can actually change this too, and actually set up my rules to say,
• 03:39 I only want 10's to show up with a full star, and
• 03:42 anything over 9 to show up with a half star.
• 03:44 So I don't really want these 8.9's showing this way.
• 03:48 But I would like this guy showing a half star.
• 03:50 So to do that we can go to Conditional Formatting >Manage Rules.
• 03:56 We can select our icon set and say Edit.
• 03:59 And at this point it says well, let see what's the value is at x%.
• 04:04 I can actually go and set these to say, don't give me 67%, give me 10.
• 04:12 And let's make that a number.
• 04:15 Let's change, and this is gonna flip things out on me.
• 04:17 So I've gotta do that again.
• 04:18 As soon as I changed the number it lost my reference, so let's go back and
• 04:21 set that again.
• 04:23 There we go, number for E5.
• 04:25 We're gonna go with number for E4.
• 04:28 So the rule is now set up to say, when it's greater than zero,
• 04:33 give me a white star.
• 04:35 When I want a half a star when it's greater than zero and
• 04:40 greater than this number as well, and then this one will be greater than ten.
• 04:45 We say OK.
• 04:46 You'll notice when I click apply, this 9.8 will get a half star and
• 04:50 the 8.9's will go to white and there we go.
• 04:53 The other thing that's important to know about conditional formatting rules,
• 04:58 is you can apply multiple conditional formatting rules to the same set of data.
• 05:03 So if I wanna go and say what's the youngest distillery in this list,
• 05:08 I can apply a new rule right on top of the old ones and
• 05:10 use multiple rules in the same place.
• 05:12 So, this is really cool stuff for making your data pop very quickly

Lesson notes are only available for subscribers.