WEBVTT
Kind: captions
Language: en

00:00:11.429 --> 00:00:15.860
You may already have some experience using
charts and sparklines to visualize data in

00:00:15.860 --> 00:00:17.490
Excel.

00:00:17.490 --> 00:00:21.310
Conditional formatting offers yet another
way to visualize your data, using things like

00:00:21.310 --> 00:00:23.300
color-coding based on the cell's value.

00:00:23.300 --> 00:00:28.750
In this example, I have the sales figures
for a number of different salespeople, and

00:00:28.750 --> 00:00:32.800
I'd like to find out who's meeting or exceeding
their monthly sales goal.

00:00:32.800 --> 00:00:36.890
Of course, we could just look at the numbers—but
it might be easier if they were highlighted

00:00:36.890 --> 00:00:41.160
somehow, which we can do using a conditional
formatting rule.

00:00:41.160 --> 00:00:48.640
First, select your data… then go to Conditional
Formatting on the Home tab.

00:00:48.640 --> 00:00:54.540
Next, mouse over Highlight Cells Rules…
and in this case, we're going to choose Greater

00:00:54.540 --> 00:00:57.180
Than.

00:00:57.180 --> 00:01:02.020
Now the sales goal for each person on this
list is $4,000 per month.

00:01:02.020 --> 00:01:06.740
If we enter that number here… you can see
in the background that any cell greater than

00:01:06.740 --> 00:01:10.840
4,000 is being formatted automatically.

00:01:10.840 --> 00:01:16.210
If you want, you can even choose a different
formatting option.

00:01:16.210 --> 00:01:20.590
Just click OK when you're done.

00:01:20.590 --> 00:01:25.000
As you can see, the formatted cells really
stand out now, so we can easily tell who's

00:01:25.000 --> 00:01:27.310
met or exceeded their goal.

00:01:27.310 --> 00:01:31.500
Looks like August wasn't a very good month,
for example, but September and October were

00:01:31.500 --> 00:01:35.390
much, much better.

00:01:35.390 --> 00:01:39.530
Next let's take a look at some of the conditional
formatting presets.

00:01:39.530 --> 00:01:44.689
We're just going to re-select our data…
then go back to the Conditional Formatting

00:01:44.689 --> 00:01:46.900
command.

00:01:46.900 --> 00:01:51.619
You have three different types of presets
to choose from: Data Bars, Colors Scales,

00:01:51.619 --> 00:01:53.369
and Icon Sets.

00:01:53.369 --> 00:01:58.829
To learn more about each one, mouse over the
options in the menu.

00:01:58.829 --> 00:02:03.110
Data Bars are kind of like a bar graphs; the
higher the value, the longer the bar in the

00:02:03.110 --> 00:02:04.110
cell.

00:02:04.110 --> 00:02:11.830
Color Scales, on the other hand, will change
the background color based on the cell's value.

00:02:11.830 --> 00:02:16.900
Icon Sets can be used to add different symbols,
ratings, and indicators—and you have quite

00:02:16.900 --> 00:02:19.819
a few styles to choose from, depending on
what you need…

00:02:19.819 --> 00:02:27.129
I think this one's especially easy to read.

00:02:27.129 --> 00:02:31.870
The icons help us see which items are the
highest and also the lowest when compared

00:02:31.870 --> 00:02:33.290
to each other.

00:02:33.290 --> 00:02:37.230
We can also see which ones are about average.

00:02:37.230 --> 00:02:40.959
You might have noticed that the first rule
we applied is still visible—the one that's

00:02:40.959 --> 00:02:43.359
changing the fill color for cells above 4,000.

00:02:43.359 --> 00:02:49.019
That's because Excel lets you have multiple
conditional formatting rules at the same time,

00:02:49.019 --> 00:02:52.109
so you can highlight different things.

00:02:52.109 --> 00:02:58.439
To clear the current formatting rules, just
select your cells… then go to Conditional

00:02:58.439 --> 00:03:03.439
Formatting… and mouse over the option that
says Clear Rules.

00:03:03.439 --> 00:03:07.680
You can clear the selected cells, or the entire
sheet.

00:03:07.680 --> 00:03:12.019
In this case, though, I'd just like to remove
the "greater than 4,000" rule, while keeping

00:03:12.019 --> 00:03:14.140
the rest of the icons.

00:03:14.140 --> 00:03:19.030
We can do that by going to Manage Rules instead.

00:03:19.030 --> 00:03:23.389
Here you'll find a list of all the rules that
are currently applied to your cells.

00:03:23.389 --> 00:03:35.739
To remove one, just select the rule you want…
then click Delete Rule… and click OK.

00:03:35.739 --> 00:03:39.729
Conditional formatting is a great tool for
visualizing parts of your worksheet.

00:03:39.729 --> 00:03:43.689
So practice using some of the rules on your
own, and try combining them to see what they

00:03:43.689 --> 00:03:45.150
can tell you about your data.