Conditional Formats with Built-In Rules

Quick reference

• 00:04 In this video we're going to look at conditional formatting and were going to
• 00:08 see how it can allow us to get some real quick business insights into our data.
• 00:12 Before we start though I do want to point out that all of the data in
• 00:14 the spreadsheet except for
• 00:16 the founding dates of the distilleries is completely fictitious.
• 00:19 Only the founding dates are actually accurate so
• 00:22 just to be aware of before we get going here.
• 00:25 The first thing that I'd like to do is I'd like to actually figure out which of
• 00:28 the distilleries was founded earliest.
• 00:29 And if I've got a big long list, that can be a little bit challenging.
• 00:32 But with conditional formatting, this is actually quite easy.
• 00:35 So what I'm gonna do is I'm gonna select the data that I wanna see here, and
• 00:39 on the home tab I'm gonna go up to conditional formatting.
• 00:41 And I'm gonna go to Top/Bottom Rules, and
• 00:44 you'll notice that I can actually pick up the bottom 10 items.
• 00:48 Now as it happens, I can also change this to say,
• 00:53 you know what, I'd rather say, let's see only the bottom three items.
• 00:59 And I don't really need to see them as a light red text.
• 01:01 This is what I'm actually looking for so I'm gonna go with green.
• 01:04 Cuz this is gonna be the oldest distilleries that exist in
• 01:09 Ila in Scotland.
• 01:10 So there we go, we can see that very quickly where our attention is drawn to
• 01:14 Bowmore, which was the earliest founded, and Ardbeg, and Laghroeg.
• 01:17 So it's kind of an interesting piece there.
• 01:21 Now what if we wanted to actually get some insight quickly as to
• 01:25 how many bottles are produced by each of these distilleries every year?
• 01:28 Again, these numbers being fictitious.
• 01:30 Again, we could use conditional formatting to do this.
• 01:33 So we can go to conditional formatting.
• 01:35 We can go to Highlight Cells Rules.
• 01:37 And let's say that we wanna highlight every distillery that produces
• 01:40 more than 90,000 bottles in a year.
• 01:44 I could enter a value of 90,000 here but
• 01:47 I could also link it directly back to the cell in this case,
• 01:51 which is kind of nice because this means it's gonna be a little bit more dynamic.
• 01:54 I'm gonna fill this one yellow with dark yellow text, for example here.
• 01:59 And say, okay.
• 02:00 So that's kind of cool.
• 02:01 What's even better is that if I change my mind and I said,
• 02:04 I wanna see this 85,000, I can do that and
• 02:07 you can see now that it's actually highlighted some different cells.
• 02:10 So that's kinda cool.
• 02:13 Another conditional formating tool that's really,
• 02:15 really nice is the ability to actually get a very,
• 02:18 very quick identification as to which of these numbers is the largest.
• 02:22 Now you can scan this list it's not extremely long but
• 02:25 picture where you have hundreds of items and
• 02:28 you wanna be able to quickly scan through and see which is the biggest.
• 02:30 We have this nice little tool called data bars.
• 02:34 And what you'll see here is that when we go and actually drop a data bar in here
• 02:39 it draws a little line that sort of goes in behind the cell.
• 02:43 And it measures it proportional to all the other ones.
• 02:46 It's very, very quick to see here in this case that Bunahabhan has the lowest amount
• 02:51 of annual revenues in this fictitious data set as compared to all of the other ones.
• 02:56 It looks like Kalila has by far the largest, well, Kalila and
• 03:00 Arbig are the two largest with Legevoolin coming in next.
• 03:04 What's really nice about this is that these lines draw some very,
• 03:07 very clear pictures right away that your brain is immediately pulled straight into,
• 03:11 which is really, really good.
• 03:12 So, data bars can be a very very useful thing to work with.
• 03:18 Another really kinda neat tool here is icon sets.
• 03:21 So if we have a ranking, say it's a world whiskey ranking of
• 03:25 the quality of the product here, maybe we'd like to try and
• 03:28 figure out which are the top gold star manufacturers here.
• 03:33 So we can use conditional formatting here for that, with icon sets.
• 03:37 I'm gonna go down and pick up this gold star icon set, and you'll notice when I
• 03:42 apply it, what it does is it actually shades the stars proportional to the rest
• 03:46 of the data that's in here, but I might wanna modify that.
• 03:49 I may not be happy with that exactly.
• 03:51 I'd like to see every distillery that's at a 10 have a full gold star.
• 03:55 Anything that's 9 or higher have a half-gold star, so
• 03:58 this one's full right now.
• 03:59 And anything that's less than 9 not have a star at all or just an empty star.
• 04:03 So to do that I'm gonna go to conditional formatting, I'm gonna manage rules and
• 04:07 that will allow me to get into my rule so I'll select it and say Edit Rule.
• 04:13 And you'll notice now that I can change some things and so you know
• 04:15 what I don't really want this based on percent, I'd like this based on a number.
• 04:20 And the number that I'd like to go with is actually sitting in the cell over here.
• 04:25 And you build these rules from the bottom up.
• 04:30 So, we'll say number,
• 04:32 I'm gonna put my largest value over here, which is gonna be 10.
• 04:36 And you'll notice now that when I actually say OK, anything that's greater than or
• 04:41 equal to 10 will have a full gold star.
• 04:43 Greater than or equal to 9 will have a half gold star.
• 04:45 And when we say OK, you can see that that change happens.
• 04:49 Again because I've linked it to a cell though, I can actually make a change very
• 04:53 quickly and say, maybe I don't want to be quite as aggressive with these rankings.
• 04:58 Now we can see full gold stars for 9.5 or higher, and
• 05:01 half a gold star for 8.5 or higher.
• 05:03 So that's how to build some real quick, and
• 05:06 even modify some quick conditional formatting rules.
• 05:09 The one other thing that you wanna be aware of is that it's very easy to clear
• 05:12 your conditional formatting rules.
• 05:14 You can select an area, go back to conditional formatting and
• 05:18 clear rules from your selected cells or your entire sheet as well.
• 05:22 So, Clear Rules here, modify them through Manage Rules and
• 05:26 build them through the interface up above.

Lesson notes are only available for subscribers.