Conditional Formatting

Quick reference

• 00:04 While we're on the subject of formatting,
• 00:06 I want to talk about something called conditional formatting.
• 00:10 Now, I'm only gonna give a brief whistle stop tour of this one because this is more
• 00:14 important at the end of a financial model rather than the beginning.
• 00:17 But it's useful for things like checks.
• 00:21 Conditional formatting changed in Excel 2007 to make it more flexible.
• 00:27 Essentially what it allows you to do is change the appearance
• 00:32 of a cell depending upon its contents or what conditions it meets.
• 00:38 Essentially if something is true or
• 00:41 it's greater than something, less than something,
• 00:43 in the top percentile, whatever, you can actually coordinate how it appears.
• 00:49 Let's take a look at some examples.
• 00:52 In this first example I have had six students sit my exam.
• 00:58 The Pass Mark is an import in cell D4, 50%.
• 01:02 And here were the scores.
• 01:04 I want to highlight those that have actually passed.
• 01:08 I can do this using conditional formatting.
• 01:11 Conditional formatting is found in the style section of the Home tab
• 01:15 on the ribbon, just here.
• 01:20 I always find it quite odd that the keyboard shortcut is Alt-od, odd, get it?
• 01:25 Never mind.
• 01:26 There's lots of different things we can do.
• 01:29 But I'm going to actually look at highlighting these cells.
• 01:32 Again, it's conditional formatting.
• 01:35 And I can look at data bars first of all, which will show.
• 01:38 I will hover over it.
• 01:39 The scores people get.
• 01:40 Which can give a good visual graphic, quite nice.
• 01:44 Or color scales to see how they perform like this, too.
• 01:47 In this case, I want to show who's actually passed.
• 01:51 So I'm gonna come up with a new rule.
• 01:52 And it's going to be not based on cells that contain or top or
• 01:57 bottom ranked values.
• 01:59 I can either use format all cells based on their values, which will say,
• 02:04 you've got a color scale.
• 02:06 I actually want it, though, to be those that exceed the Pass Mark.
• 02:10 So it's going to be equals.
• 02:11 Now when you highlight a whole lot of cells, always imagine you're writing
• 02:16 the formula as if you are in the cell in the top lefthand corner of that range.
• 02:21 Which in this case, given I've got D6 to D11 highlighted, will be cell D6.
• 02:25 So it's going to be =D6.
• 02:29 Now, if I click on D6, it will make it absolute, so
• 02:32 I've got to get rid of those dollar signs.
• 02:36 And it's got to be greater than or equal to the Pass Mark.
• 02:40 So when it's greater than or equal to the Pass Mark, I can format it.
• 02:43 I'll click Format, and I'm going to make it bold and
• 02:47 white with a fill of a dull green, let's say.
• 02:51 That's what it's going to look like.
• 02:54 It sounds good to me, click OK.
• 02:56 There you go, it's very clear to see who's passed, nice and easy.
• 03:02 That's example one.
• 03:05 In example two, I've had ten business units report their sales to me,
• 03:10 and I'm going to produce something called traffic light reporting.
• 03:15 That is, I want to see how they are performing relative to each other.
• 03:20 So in this I'm going to highlight all the cells, back to conditional formatting.
• 03:25 And this time I'm going to go to Icon Sets, and
• 03:28 I'm going to have my traffic lights.
• 03:30 So you can see those that have actually performed better, worse, and average.
• 03:35 And you can see that it bases it on how they've scored across a range.
• 03:41 So because most of them are in a particular range, and
• 03:45 10979 Is much lower than the rest.
• 03:49 Then perhaps a lot of them are going to look like they're doing okay
• 03:52 because they're in the top third of the actual value range.
• 03:56 So maybe a better idea might be to make this one 19979 and
• 04:02 maybe make that one 21226 just to go through and do it again.
• 04:07 Just to give you a better idea what can happen.
• 04:12 Because what is wrongly thought about icon sets is that it splits the numbers into
• 04:17 top third, middle third, and bottom third.
• 04:20 But it does it on the number range, not on the number of elements in the list.
• 04:27 So if I do that, I have now got that formatted.
• 04:30 I might want to see as well the top three performers.
• 04:33 So I can add a second set of rules in here and say, okay, I want,
• 04:37 not the top ten items when we click on that, I want the top three.
• 04:42 And you can change what color you want it to be, red text or whatever.
• 04:45 So I'm going to pick a light red.
• 04:47 You can go for custom format.
• 04:49 I'll do that and click OK.
• 04:52 Now if they are in the top three I know they are green so
• 04:54 I don't want them to do the traffic light.
• 04:56 So I can manage a rule, go down here, click on manage rules.
• 05:00 And I want to move this, make sure this is at the bottom.
• 05:03 So if it's in the way you could just move it up using the arrow keys here.
• 05:06 But if it is in the top three I want it to stop if it is true,
• 05:09 cuz that's all I want it to do.
• 05:12 I click OK, look at that, nice and easy.
• 05:16 You can get conditional formatting working very, very simply.
• 05:20 Have a play.
• 05:21 Now warning about conditional formatting.
• 05:24 Conditional formatting messes up if you start inserting rows and columns.
• 05:28 It doesn't always behave itself.
• 05:31 It's best doing all conditional formatting in a model at the end unless you know
• 05:35 the cells aren't going to move.
• 05:37 It's recommended,
• 05:38 don't put too much conditional formatting until the end because it does misbehave.
• 05:44 I've brought it here to this section in the preparation because I need to talk
• 05:48 about checks shortly and I'm going to use conditional formating in that.
• 05:51 But really try to resist the urge to use conditional formatting until
• 05:55 everything else is finished in the model.
• 05:59 Talking of the model, I suppose we should finish off the layout tip, shouldn't we?
• 06:03 That's the next section.

Lesson notes are only available for subscribers.