Locked lesson.
About this lesson
When to use conditional formatting, and how to effectively apply it.
Exercise files
Download this lesson’s related exercise files.
Conditional Formatting.xlsx9.6 KB Conditional Formatting - Solution.xlsx
9.9 KB
Quick reference
Conditional Formatting
Conditional Formatting explained using the CRaFT methodology.
When to use
Conditional Formatting can assist in building a financial model in Excel which is easy to understand and navigate through.
Instructions
- Conditional Formatting is a feature that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a corresponding formula
- Accessed from the ‘Home’ tab (or ALT + O + D), conditional formatting formats the cell(s) selected depending upon whether a condition is TRUE
- Conditional formatting is now much more flexible:
- More conditions allowed
- Conditions do not have to be mutually exclusive
- Facilitates traffic light reporting, etc.
Login to download
- 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.