WEBVTT
Kind: captions
Language: en

00:00:07.240 --> 00:00:11.959
Imagine you have a spreadsheet with 500 plus
rows of data.

00:00:11.959 --> 00:00:17.200
It's hard, if not impossible, to see patterns
and trends in the data.

00:00:17.200 --> 00:00:21.680
Even with simpilar spreadsheets, it can be
difficult to spot patterns with row after

00:00:21.680 --> 00:00:26.039
row of data.
An Excel feature called conditional formatting

00:00:26.039 --> 00:00:30.199
can help with this.
With conditional formatting you can apply

00:00:30.199 --> 00:00:35.830
formatting to one or more cells based on the
value of the cell.

00:00:35.830 --> 00:00:42.110
You can highlight interesting or unusual cell
values, and visualize the data using formatting

00:00:42.110 --> 00:00:48.000
such as data bars.
Let's take a look at how we can use conditional

00:00:48.000 --> 00:00:55.000
formatting in our Company Sales spreadsheet.
First, select an area that you'd like to highlight.

00:00:57.030 --> 00:01:01.360
Our Order Amounts are really what we would
probably want to apply conditional formatting

00:01:01.360 --> 00:01:08.210
to.
On the Home tab, locate the Styles group.

00:01:08.210 --> 00:01:13.640
Click the Conditional Formatting command and
a menu will appear with your formatting options.

00:01:13.640 --> 00:01:20.280
Let's take a look at how a few of these work.
The first one, Highlight Cell Rules, such

00:01:20.280 --> 00:01:26.219
as Greater than and Less than.
Let's choose Less Than, and apply formatting

00:01:26.219 --> 00:01:32.829
to cells with values less than $200.
After entering the value, we can use the drop-down

00:01:32.829 --> 00:01:39.299
menu to choose the color and type of formatting
for Excel to apply, and then click OK.

00:01:39.299 --> 00:01:46.299
Now, in the spreadsheet, the cells with values
less than $200 appear highlighted.

00:01:48.140 --> 00:01:55.140
This allows us to easily identify the low
orders.

00:01:55.289 --> 00:02:00.439
To remove these formatting rules, we'll just
click the Conditional Formatting command and

00:02:00.439 --> 00:02:06.280
select Clear Rules.
Since my cells aren't selected right now,

00:02:06.280 --> 00:02:13.280
I'll just choose Clear Rules from Entire Worksheet
and there you go, the formatting is removed.

00:02:13.900 --> 00:02:18.750
Let's take a look at some of the other conditional
formatting options.

00:02:18.750 --> 00:02:26.000
Besides locating cells that are greater than
or less than a value, you can also apply formatting

00:02:26.000 --> 00:02:31.320
to cells that are between two values, equal
to a value, or if we had selected the entire

00:02:31.320 --> 00:02:37.990
worksheet, we could apply formatting to cells
with a specific date or month listed.

00:02:37.990 --> 00:02:43.940
You can apply conditional formatting to cells
that are the top of bottom 10% in value, or

00:02:43.940 --> 00:02:50.940
above and below average.
Let's select Above Average, select the color

00:02:52.450 --> 00:03:01.930
of formatting we want, click OK, and now those
values that are above average are highlighted

00:03:01.930 --> 00:03:06.600
so we can easily see.
Now let's clear the formatting on these selected

00:03:06.600 --> 00:03:11.410
cells.
Let me show you one other option that is really

00:03:11.410 --> 00:03:15.510
one of my favorites.
We'll select our Order Amounts again, and

00:03:15.510 --> 00:03:20.420
take a look at a conditional formatting option
called Data Bars.

00:03:20.420 --> 00:03:27.420
We'll select a color, and now these values
are formatted with data bars.

00:03:28.170 --> 00:03:34.150
This allows us to easily see which values
are greatest.

00:03:34.150 --> 00:03:39.440
Cells with smaller values appear with less
of the data bar, while cells with greater

00:03:39.440 --> 00:03:45.710
values appear with more of the data bar.
The real powerful part of conditional formatting

00:03:45.710 --> 00:03:50.540
is the ability to apply multiple rules or
formatting to cells.

00:03:50.540 --> 00:03:55.180
Right now we have data bars applied to the
cells that show us which cells have the largest

00:03:55.180 --> 00:04:01.930
values.
Now, what if I also want to mark the ones

00:04:01.930 --> 00:04:08.600
that are Below Average or Equal to a certain
amount? Now, you can see that we have our

00:04:08.600 --> 00:04:14.070
formatting for the data bars and for the below
average cells.

00:04:14.070 --> 00:04:18.630
Before we finish talking about Conditional
Formatting, let me show you how you can have

00:04:18.630 --> 00:04:25.569
more control over these formatting rules.
If you click the Conditional Formatting command,

00:04:25.569 --> 00:04:28.830
you'll see Manage Rules at the bottom of the
menu.

00:04:28.830 --> 00:04:35.729
Select this, and a dialog box will appear.
From here, you can change how your selected

00:04:35.729 --> 00:04:42.729
formatting rules are applied.
You can select a rule and move it up or down

00:04:45.120 --> 00:04:50.909
which will make it so Excel performs this
formatting rule in a different order.

00:04:50.909 --> 00:05:01.259
You can edit a rule or even delete a rule.
We'll just click OK and now you can see that

00:05:01.259 --> 00:05:07.229
the formatting to the Below Average cells
has been deleted.

00:05:07.229 --> 00:05:12.069
While this spreadsheet only has about 40 rows,
imagine you are working with a spreadsheet

00:05:12.069 --> 00:05:16.960
with hundreds of rows.
Data bars and the other conditional formatting

00:05:16.960 --> 00:05:20.190
options make it very easy to see data in meaningful
ways.