Conditional Formats with Built-In Rules

Quick reference

• 00:04 In this video, we're going to look at getting insights out of our data by using
• 00:08 conditional formatting.
• 00:11 The data set we're using is a bunch of whiskey distillery information.
• 00:15 But I wanna be really clear that this data's all fake.
• 00:17 It's not really truly the production or annual revenues that you'd actually see.
• 00:21 It's just a sample that's made up to illustrate our purposes.
• 00:24 But if it were, one of the things I might be interested in is the founding date.
• 00:29 In theory, the longer a distillery has been around,
• 00:32 the more it should have a really good product.
• 00:34 So I would like to go and
• 00:35 highlight from my founding dates the three earliest founding dates.
• 00:40 So what we're going to do is we're going to go to conditional formatting and
• 00:44 we'll go to top/bottom rules, and we'll pick the bottom 10 items.
• 00:48 Now this is a little bit weird because there aren't even 10 items in
• 00:52 the list but, I can go an change this to say just give me three.
• 00:57 Now, I'm not a big fan of red on red text here.
• 00:59 That's a little harsh.
• 01:01 So I'm gonna go with green on green.
• 01:03 And at that point you can see the rule changes.
• 01:05 It shows me that Ardbeg, Bowmore and
• 01:07 Laphroaig were founded earlier than anybody else.
• 01:12 Four our next column, we're gonna move to the production on bottles, and
• 01:15 I'd like to highlight production that actually exceeds a certain threshold.
• 01:19 I'm gonna use a different conditional formatting rule for this one.
• 01:23 This one will be highlight cell where we choose greater than.
• 01:27 And when we choose this role,
• 01:28 Excel will pick an arbitrary number that's part way through the data set in order to
• 01:32 actually show a certain portion highlighted and certain portion, not.
• 01:36 But that's not really what I'm after.
• 01:37 I would like to know where my production is greater than 90,000 bottles in a year.
• 01:42 So I'm gonna click on cell C7 because I happen to have that information
• 01:45 recorded here.
• 01:46 And again, I'm gonna change it.
• 01:47 I don't like the light red fill.
• 01:49 I'm going to go with the green fill with dark green text and we'll say OK.
• 01:54 Notice that Ardbeg and Caol Illa are both highlighted because their production is
• 01:58 greater than 90,000 bottles.
• 02:00 But the great thing about conditional formatting,
• 02:03 if I comeback to the cell while I'm driving my rule and
• 02:06 change it to 75,000, you'll notice that now Kilchoman and Lagavulin
• 02:10 also get highlighted because they are also earn excess to 75,000 bottles.
• 02:14 So it's entirely dynamic which is pretty cool.
• 02:17 I'm gonna press Control Z to set it back.
• 02:20 But you can see just how quickly it actually goes on updates.
• 02:24 The next conditional formatting rule I'd like to look at on the Annual Revenues
• 02:28 column, is data bars, and data bars are fantastic, let me show you why.
• 02:33 We'll go to Conditional Formatting, Data Bars, and
• 02:36 I'm gonna choose the top-left data bars.
• 02:39 What I'd love about this visual is, we can see immediately
• 02:42 that Bunnahabhain has the lowest annual revenues in this data set.
• 02:46 And Caol Illa, because the bar goes all the way from the left to the very
• 02:50 right-hand side has the highest number of the data set,
• 02:53 with Ardbeg very close behind.
• 02:55 Notice the bar doesn't extent quite as far, but it's pretty close.
• 03:00 This is a great visual because you can immediately see what the lengths of
• 03:03 the bars are.
• 03:04 You know which one's the biggest and you know how everything else compares to it in
• 03:08 size really useful visual that i love to put on my reports.
• 03:12 For a final column,
• 03:13 we're going to apply a neat little trick here using what we call an Icon Set.
• 03:19 You'll notice there's lots of different icons sets here for different values.
• 03:22 The one I wanna focus on is the gold star rule and when I select it,
• 03:27 you'll see that our perfect 10s and are 9.8s have a full gold star.
• 03:30 The 8.9s have a half star, and everything else has a white star.
• 03:36 Now, that's all good and everything, but
• 03:38 it's not really what I want to see because as a whiskey connoisseur,
• 03:42 I want to make sure that I'm drinking whiskey that's rated 9 or above.
• 03:46 So for me, only the perfect 10 should have a full gold star.
• 03:50 From 9 to 9.9 it should have half a star, and
• 03:53 anything 8.9 and below, sorry, it's gonna get a white star.
• 03:58 So here's what we're going to do to modify this rule.
• 04:00 I'm gonna go click in any cell, go to conditional formatting, and
• 04:04 we'll go to manage rules, and now we'll click on Icon Set and Edit Rule.
• 04:10 This takes me into the rule Configuration Manager where I can actually play
• 04:13 around with it.
• 04:14 And it shows how the rule is actually constructed.
• 04:17 It takes the range of values we have from 8.3 to 10.
• 04:20 And it says, if it's in the lower third, give it a white star.
• 04:24 If it's between the lower third and the second third, give it a half star.
• 04:29 And then if it's in the greater 67% or
• 04:31 higher of the values, give it the full gold star.
• 04:35 I wanna change this.
• 04:36 I'm gonna change this to say,
• 04:40 a number if this is greater than or equal to 10.
• 04:45 Then at that point, we'll give it a 4 gold star.
• 04:50 I'm gonna change the next one also to number.
• 04:53 And from here, we'll say, if the value is greater than or
• 04:56 equal to 9 give it a half star.
• 04:58 Unless it's greater than or equal to 10, then give it a full star.
• 05:02 Anything that is less than this, we'll get an empty star.
• 05:06 And now when we say OK, and we say OK, you'll notice that my 9.8s get
• 05:11 a half star, my 10 gets a perfect star, and everybody else is white.
• 05:16 Now the final thing I just want to say about these things is,
• 05:18 you're not bound to just using one conditional formatting rule on a cell.
• 05:21 You can actually use multiple ones.
• 05:23 But these are a great way that dynamically enhance your data to add.
• 05:27 Different appeal to it and also help get insights.

Lesson notes are only available for subscribers.