Conditional Formatting

Quick reference

• 00:04 In this lesson, we learn how Conditional Formatting can turn boring numbers,
• 00:09 into meaningful and interesting information.
• 00:12 We have the exam scores of 10 students on this spreadsheet,
• 00:16 we want to know who was the top student for each subject.
• 00:19 To gather this information at a glance,
• 00:22 conditional formatting is a really helpful tool.
• 00:26 As the name suggests, Excel enables us to give cells a certain format
• 00:31 based on a condition that we specify.
• 00:33 So if the value in our cell changes, the format will change too.
• 00:38 The Conditional Formatting command is located on the Home tab,
• 00:42 under the Styles command group.
• 00:44 First, we should select the range of cells that we're
• 00:47 looking to apply our conditional formatting to.
• 00:49 In our case, it'll be each subject one at a time,
• 00:53 pretty much we're asking which score was the highest in this subject?
• 00:58 So we highlight the first range, that's B5 to B14,
• 01:03 and click on Conditional Formatting.
• 01:07 This Down Arrow gives us a couple of options,
• 01:10 each option expands into more specific options for
• 01:13 the type of conditions that we'd like to apply to our data.
• 01:17 Here's one that catches our attention, Top/Bottom rules.
• 01:21 Since we want the top scorer, maybe this will give us what we want,
• 01:26 it mentions top 10, Bottom 10, Above and Below Average.
• 01:31 Well, we don't really want any of those, but
• 01:34 maybe we can adjust one of them to what we want.
• 01:36 Let's click on top 10 Items, a dialog box pops up which
• 01:41 says Format cells that rank in the top 10 well the 10
• 01:46 is adjustable, so let's change that 10 to a 1.
• 01:51 And since we're looking for the top performer, let's change the color
• 01:56 from red, and it can be any color we want, we can customize the format,
• 02:01 but green is here and we like green, so let's choose that.
• 02:05 We notice that while we're working, Excel is working in the background to show us
• 02:09 what our data will look like when we're done.
• 02:12 We click OK, and now we know who was the top student in the visual arts.
• 02:18 Now we want to apply the conditional formatting to the other subjects.
• 02:22 There's a shortcut called a Format Painter,
• 02:24 the Format Painter can copy the format to the other cells that we want.
• 02:29 So we select our range, double click on the Format Painter,
• 02:32 it's on the clipboard command group and it looks like a little paintbrush.
• 02:37 We double click because we're going to select multiple ranges, and
• 02:42 then we can highlight each range as we go along.
• 02:45 And we can just click on the first row, and
• 02:48 it's highlighting each student who got the top score.
• 02:52 We hit Escape to deselect the Format Painter, and we're done,
• 02:56 now we have the top achiever for each subject area.
• 03:00 Of course with the conditional formatting tool, we can also quickly determine which
• 03:04 students will remain on the honor roll next year.
• 03:06 The honor roll requirement is an average score of 80% or higher, so
• 03:11 let's figure that out.
• 03:13 This column has each student's average, and we want to put our condition in.
• 03:17 So let's highlight, and go to Conditional Formatting.
• 03:23 This time we want to highlight cells, the scores that we're looking for
• 03:28 are 80% or higher,
• 03:30 we can't select greater than 80% because that would exclude students who got 80%.
• 03:36 And we can' select greater than 79 because that would potentially include
• 03:41 students who got 79.2, 79.4 and strictly speaking, they wouldn't qualify.
• 03:48 So we need a rule that's more precise, let's go to New Rule,
• 03:53 and make all the selections ourselves.
• 03:57 We don't want to format all the cells in our range,
• 04:00 we only want to format those that contain a particular set of values.
• 04:05 So we click on Format Only cells that contain and
• 04:09 we want cell values that are greater than or equal to 80%.
• 04:16 We type that as our criteria, and we'll tell Excel what we want these
• 04:21 cells to look like by clicking on Format, maybe we want a blue fill for
• 04:26 all these cells, and let's make our font Bold, so it stands out.
• 04:31 We click on OK, and
• 04:32 here we have all the students who will be on the honor roll next year, we're done.
• 04:38 Conditional Formatting makes our data more visually appealing, and
• 04:41 makes important data stand out.

Lesson notes are only available for subscribers.