1
00:00:00,750 --> 00:00:04,971
Welcome back, in this video, we'll
introduce you to conditional formatting,

2
00:00:04,971 --> 00:00:08,500
a simple tool used to highlight
data that meets certain criteria.

3
00:00:10,390 --> 00:00:13,560
Let's stay on the Texas
rental data worksheet.

4
00:00:13,560 --> 00:00:17,494
In the last video, we created
this nice table using VLOOKUP for

5
00:00:17,494 --> 00:00:22,200
ten counties with specific data
we wanted for those counties,

6
00:00:22,200 --> 00:00:23,950
but let's dig a little deeper.

7
00:00:24,970 --> 00:00:30,296
I want to find all three bedroom rentals
less than $900.

8
00:00:30,296 --> 00:00:32,666
Again, we have over 250 counties, so

9
00:00:32,666 --> 00:00:36,330
trying to cherry pick
through them will be a chore.

10
00:00:36,330 --> 00:00:38,570
Instead, let's apply
conditional formatting.

11
00:00:39,760 --> 00:00:41,390
How does conditional formatting work?

12
00:00:42,810 --> 00:00:46,879
Conditional formatting changes
the appearance of cells based on specific

13
00:00:46,879 --> 00:00:47,530
criteria.

14
00:00:48,740 --> 00:00:50,224
If the condition is true,

15
00:00:50,224 --> 00:00:54,460
the cells are modified with
the format that you specified.

16
00:00:54,460 --> 00:00:56,080
If not, they remain unchanged.

17
00:00:57,940 --> 00:01:00,506
Select column G by clicking
on the column header.

18
00:01:03,580 --> 00:01:06,866
In the menu,
choose format then conditional formatting.

19
00:01:12,819 --> 00:01:16,642
Apply to range will show the cells that
will be changed when the condition

20
00:01:16,642 --> 00:01:18,140
is true.

21
00:01:18,140 --> 00:01:22,780
We want to highlight cells
that are less than $900.

22
00:01:22,780 --> 00:01:29,027
Under format rules, Choose less than,

23
00:01:29,027 --> 00:01:33,754
then enter $900 in the entry box.

24
00:01:38,594 --> 00:01:45,183
By default, you'll notice that
a few cells are shaded green.

25
00:01:45,183 --> 00:01:52,650
Bailey 801, Baylor 801,
Borden, 878 and so on.

26
00:01:52,650 --> 00:01:55,950
The condition for
these cell values is true.

27
00:01:55,950 --> 00:01:58,125
Notice the unshaded values,

28
00:01:58,125 --> 00:02:03,440
the condition is false because those
values are not less than $900.

29
00:02:07,830 --> 00:02:10,140
Click done in the conditional
formatting panel.

30
00:02:12,990 --> 00:02:16,010
Let's add another rule
to this same column.

31
00:02:16,010 --> 00:02:17,480
Click add another rule.

32
00:02:20,580 --> 00:02:25,509
This time format cells if greater than or
equal to and enter 1200.

33
00:02:30,440 --> 00:02:33,656
Under formatting style,
click on default and

34
00:02:33,656 --> 00:02:36,460
choose the third option in the top row.

35
00:02:39,014 --> 00:02:39,980
Then click done.

36
00:02:41,680 --> 00:02:45,610
Column G now has two
conditional format rules.

37
00:02:45,610 --> 00:02:50,400
The lower rental rates are in green, and
the more expensive prices are in red.

38
00:02:52,020 --> 00:02:53,920
But I foresee a problem.

39
00:02:54,940 --> 00:02:59,470
Someone with red green color blindness may
not differentiate between the two shades.

40
00:03:00,550 --> 00:03:04,610
Or if I print this worksheet in black and
white, the high prices and

41
00:03:04,610 --> 00:03:08,680
the low prices will appear
with gray backgrounds.

42
00:03:08,680 --> 00:03:09,490
So let's fix that.

43
00:03:10,910 --> 00:03:13,190
Click the value is less than 900 rule.

44
00:03:15,210 --> 00:03:17,380
These are the rates that
are more attractive to us, so

45
00:03:17,380 --> 00:03:19,030
we want to bring more attention to them.

46
00:03:21,080 --> 00:03:27,230
Under formatting style, click the B for
bold, and I for italics.

47
00:03:29,110 --> 00:03:33,261
You'll notice the font changes for
all values less than 900.

48
00:03:33,261 --> 00:03:33,840
Click Done.

49
00:03:37,510 --> 00:03:40,420
Let's make changes to
the higher values too.

50
00:03:40,420 --> 00:03:43,984
Click value is greater than or
equal to 1200 rule

51
00:03:46,276 --> 00:03:50,620
These are the values I want to avoid,
so they don't need to be as in my face.

52
00:03:51,660 --> 00:03:54,550
Under formatting style, click Default.

53
00:03:55,800 --> 00:03:58,150
And let's choose the option
in the bottom right.

54
00:03:59,170 --> 00:04:03,540
This removes the cell fill color,
but makes the font color red.

55
00:04:03,540 --> 00:04:06,660
For good measure,
let's click the strikethrough button.

56
00:04:06,660 --> 00:04:08,300
It's the S with the line through it.

57
00:04:10,090 --> 00:04:15,550
Now all the values $1200 or
more are red and crossed out.

58
00:04:16,590 --> 00:04:17,090
Click Done.

59
00:04:20,010 --> 00:04:24,280
Try some other combinations on your own,
but be sure your criteria makes sense.

60
00:04:25,310 --> 00:04:29,976
For example, if you set a condition
on studio apartments under $850,

61
00:04:29,976 --> 00:04:31,910
they will all be highlighted.

62
00:04:33,110 --> 00:04:36,847
If you create a condition for
4 bedroom over $2,000,

63
00:04:36,847 --> 00:04:42,260
that condition is false for the entire
column, so no values are formatted.

64
00:04:42,260 --> 00:04:45,634
With that in mind, have fun experimenting
with different rules, colors,

65
00:04:45,634 --> 00:04:46,590
and format options.

66
00:04:48,620 --> 00:04:53,060
In the next video, we will shift gears and
create a pivot table using sales data.

67
00:04:53,060 --> 00:04:53,560
Can't wait.