1
00:00:00,05 --> 00:00:02,01
- [Instructor] You can change the color of a cell

2
00:00:02,01 --> 00:00:03,05
based on its values.

3
00:00:03,05 --> 00:00:04,09
Why would you want to do that?

4
00:00:04,09 --> 00:00:07,00
Well, you can very quickly see trends.

5
00:00:07,00 --> 00:00:08,08
You can also see places where the data

6
00:00:08,08 --> 00:00:11,03
doesn't match what you're expecting it to be.

7
00:00:11,03 --> 00:00:13,08
Let's create some conditional formatting,

8
00:00:13,08 --> 00:00:15,06
and I've gone back to this very basic

9
00:00:15,06 --> 00:00:17,04
spreadsheet so that you can very quickly,

10
00:00:17,04 --> 00:00:18,06
and easily see

11
00:00:18,06 --> 00:00:21,04
what conditional formatting can really do.

12
00:00:21,04 --> 00:00:23,04
Let's take column D here.

13
00:00:23,04 --> 00:00:26,04
I have all my product sales for these units.

14
00:00:26,04 --> 00:00:28,01
I'm going to highlight this data.

15
00:00:28,01 --> 00:00:29,08
And in the home ribbon tab,

16
00:00:29,08 --> 00:00:32,04
I'll click on conditional formatting.

17
00:00:32,04 --> 00:00:34,02
Now it comes with some very quick trends

18
00:00:34,02 --> 00:00:35,02
that we can see.

19
00:00:35,02 --> 00:00:37,02
For example, highlighting cells.

20
00:00:37,02 --> 00:00:39,01
I can see data that's greater than

21
00:00:39,01 --> 00:00:41,07
a certain amount, less than or equal to

22
00:00:41,07 --> 00:00:44,01
if I'm looking for text or values.

23
00:00:44,01 --> 00:00:46,04
Text that contains a certain value,

24
00:00:46,04 --> 00:00:48,03
a date occurring at a certain time,

25
00:00:48,03 --> 00:00:49,07
and I can even very quickly

26
00:00:49,07 --> 00:00:51,06
find duplicate values.

27
00:00:51,06 --> 00:00:53,07
Let's say I want to highlight cells

28
00:00:53,07 --> 00:00:58,06
that are greater than 1,000 units sold.

29
00:00:58,06 --> 00:01:00,08
I can also change the color.

30
00:01:00,08 --> 00:01:01,09
I don't want a red fill.

31
00:01:01,09 --> 00:01:03,09
I feel like this is a positive thing.

32
00:01:03,09 --> 00:01:05,00
This is great sales,

33
00:01:05,00 --> 00:01:07,04
so I'll change it to a green fill.

34
00:01:07,04 --> 00:01:08,09
I'll click okay.

35
00:01:08,09 --> 00:01:12,05
And now I can very quickly see my big sellers.

36
00:01:12,05 --> 00:01:15,07
To remove a conditional formatting at any time,

37
00:01:15,07 --> 00:01:17,03
highlight your data.

38
00:01:17,03 --> 00:01:19,05
Click conditional formatting again,

39
00:01:19,05 --> 00:01:21,07
hover your mouse over clear rules,

40
00:01:21,07 --> 00:01:22,06
and from here,

41
00:01:22,06 --> 00:01:25,08
I'll select clear rules from selected cells,

42
00:01:25,08 --> 00:01:27,03
because you may have different rules

43
00:01:27,03 --> 00:01:31,02
for different columns.

44
00:01:31,02 --> 00:01:33,03
If I go back to conditional formatting,

45
00:01:33,03 --> 00:01:34,08
I can also hover my mouse

46
00:01:34,08 --> 00:01:37,01
over top and bottom rules.

47
00:01:37,01 --> 00:01:39,00
This way I can see my lowest sellers

48
00:01:39,00 --> 00:01:40,07
or my best sellers.

49
00:01:40,07 --> 00:01:44,02
For example, I'll click the top 10 items,

50
00:01:44,02 --> 00:01:45,00
and in this case,

51
00:01:45,00 --> 00:01:46,08
I know I don't even have 10 items,

52
00:01:46,08 --> 00:01:50,03
so I just want to see my top three items.

53
00:01:50,03 --> 00:01:55,04
And again, I'll choose a green color.

54
00:01:55,04 --> 00:01:58,01
And here they are.

55
00:01:58,01 --> 00:01:59,07
I can see other things.

56
00:01:59,07 --> 00:02:01,09
For example, the top 10%,

57
00:02:01,09 --> 00:02:03,09
and I can change that percentage rate,

58
00:02:03,09 --> 00:02:06,00
even the bottom percent.

59
00:02:06,00 --> 00:02:08,01
I can do a lot more than that.

60
00:02:08,01 --> 00:02:10,07
I can see data bars with gradient fills,

61
00:02:10,07 --> 00:02:12,05
color scales of values.

62
00:02:12,05 --> 00:02:14,09
This is great for temperature ranges.

63
00:02:14,09 --> 00:02:17,02
I can even see icon sets,

64
00:02:17,02 --> 00:02:19,04
and I can set my own indicators

65
00:02:19,04 --> 00:02:22,03
for dangerous values.

66
00:02:22,03 --> 00:02:24,00
I can set trend arrows.

67
00:02:24,00 --> 00:02:27,06
For example, this is the top value.

68
00:02:27,06 --> 00:02:28,07
This is a lower one,

69
00:02:28,07 --> 00:02:30,07
and this one's on its way up.

70
00:02:30,07 --> 00:02:34,03
These are just very quick ways to skim your data.

71
00:02:34,03 --> 00:02:36,07
Conditional formatting is very powerful,

72
00:02:36,07 --> 00:02:39,09
especially when you start creating your own.

73
00:02:39,09 --> 00:02:42,06
For example, if I click new rule,

74
00:02:42,06 --> 00:02:45,06
I can format cells based on their values.

75
00:02:45,06 --> 00:02:47,06
I can even use my own formula

76
00:02:47,06 --> 00:02:50,02
to determine which cells to format.

77
00:02:50,02 --> 00:02:53,00
Now the sky is the limit for what I can do.

78
00:02:53,00 --> 00:02:55,09
For example, if I have text in a cell,

79
00:02:55,09 --> 00:02:57,07
I can change the color of that cell,

80
00:02:57,07 --> 00:02:59,02
change the background of it

81
00:02:59,02 --> 00:03:01,02
if the amount of characters are above

82
00:03:01,02 --> 00:03:02,06
a certain limit.

83
00:03:02,06 --> 00:03:04,02
So let's say I need a sentence

84
00:03:04,02 --> 00:03:06,05
that's at least 50 characters,

85
00:03:06,05 --> 00:03:10,00
I can put in a formula to match that criteria.

86
00:03:10,00 --> 00:03:12,00
So check out conditional formatting.

87
00:03:12,00 --> 00:03:13,08
It's a great way to quickly skim

88
00:03:13,08 --> 00:03:14,09
a column of data,

89
00:03:14,09 --> 00:03:16,05
and see trends or places

90
00:03:16,05 --> 00:03:18,00
that need your attention.