WEBVTT
Kind: captions
Language: en

00:00:11.100 --> 00:00:16.870
We've already looked at ways that you can
visualize your data using charts and sparklines.

00:00:16.870 --> 00:00:18.860
Conditional formatting offers a different
way of

00:00:18.860 --> 00:00:21.290
visualizing data because it allows you to
apply

00:00:21.290 --> 00:00:24.130
rules to the cells that will add formatting,
such

00:00:24.130 --> 00:00:28.250
as color coding, based on the cell's value.

00:00:28.250 --> 00:00:30.750
In this example, we have sales statistics
for a

00:00:30.750 --> 00:00:33.090
number of different salespeople,

00:00:33.090 --> 00:00:34.540
and let's say that the sales goal

00:00:34.540 --> 00:00:39.000
for each person is $5,000 per month.

00:00:39.000 --> 00:00:41.720
We can use conditional formatting to see who
met

00:00:41.720 --> 00:00:43.770
or exceeded this goal.

00:00:43.770 --> 00:00:53.270
First, select the data, and then go to the
Conditional Formatting command on the Home

00:00:53.270 --> 00:00:55.409
tab.

00:00:55.409 --> 00:00:58.670
And then click Highlight Cells Rules, and
select

00:00:58.670 --> 00:01:00.260
Greater Than.

00:01:00.260 --> 00:01:03.989
And we want to format cells that are greater
than

00:01:03.989 --> 00:01:10.420
5000, so we'll type that here.

00:01:10.420 --> 00:01:13.380
And then just choose a formatting option.

00:01:13.380 --> 00:01:21.399
I'll choose Green Fill with Dark Green Text.

00:01:21.399 --> 00:01:25.299
So it really makes those cells stand out.

00:01:25.299 --> 00:01:27.520
You can see that not many salespeople met
the goal

00:01:27.520 --> 00:01:29.299
in June or August,

00:01:29.299 --> 00:01:32.610
but September and October were
much better.

00:01:32.610 --> 00:01:39.580
Now, let's look at some of the conditional
formatting presets.

00:01:39.580 --> 00:01:44.570
I'm going to re-select these cells, and then
go

00:01:44.570 --> 00:01:49.439
back to Conditional Formatting.

00:01:49.439 --> 00:01:52.409
There are three different types of presets:
Data

00:01:52.409 --> 00:01:56.850
Bars, which are kind of like a bar graph;

00:01:56.850 --> 00:01:59.549
Color Scales, which change the background
color

00:01:59.549 --> 00:02:02.970
based on the value;

00:02:02.970 --> 00:02:06.229
and Icon Sets, which put different icons in
the

00:02:06.229 --> 00:02:07.890
cells depending on their value.

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

00:02:14.910 --> 00:02:19.070
The icons help us to see which items are the
highest and the lowest,

00:02:19.070 --> 00:02:22.410
and which ones are about average.

00:02:22.410 --> 00:02:24.490
You might also notice that the first rule
we

00:02:24.490 --> 00:02:26.340
created is still visible.

00:02:26.340 --> 00:02:33.670
And that's because Excel lets you have multiple
conditional formatting rules at the same time.

00:02:33.670 --> 00:02:39.190
To clear all of the rules, just select the
cells,

00:02:39.190 --> 00:02:43.690
and then go to Conditional Formatting,
Clear Rules.

00:02:43.690 --> 00:02:46.140
And you'll have a few different options.

00:02:46.140 --> 00:02:49.130
But I'd like to just remove the greater than
5000

00:02:49.130 --> 00:02:52.060
rule, while keeping the icons.

00:02:52.060 --> 00:02:57.920
To do this, go to Manage Rules.

00:02:57.920 --> 00:02:59.860
You'll see a list of all of the rules that
you've

00:02:59.860 --> 00:03:01.370
applied,

00:03:01.370 --> 00:03:10.450
and you can just select one of them, and
click Delete Rule.

00:03:10.450 --> 00:03:14.090
So practice using the different types of
conditional formatting,

00:03:14.090 --> 00:03:39.370
and try combining different ones to see
what they tell you about your data.