1
00:00:00,670 --> 00:00:05,380
In the next two lectures we're going to take a look at Excel's conditional formatting.

2
00:00:05,380 --> 00:00:09,780
So until now, we took a look at basic Cell Formatting, we looked at

3
00:00:09,780 --> 00:00:11,370
number formatting.

4
00:00:11,380 --> 00:00:19,300
Now we're gonna see how we can influence the Cell Formatting based on a condition. Excel has a lot of

5
00:00:19,390 --> 00:00:21,970
easy to use inbuilt conditions.

6
00:00:21,970 --> 00:00:27,360
Let's take a look at these two in this lecture. And we're going to take a look at data bars and icon

7
00:00:27,400 --> 00:00:29,800
sets in the next lecture.

8
00:00:29,800 --> 00:00:32,920
First off, what is conditional formatting?

9
00:00:32,920 --> 00:00:38,740
I've actually used this throughout all the workbooks you've been using on the index page. Whenever you

10
00:00:38,740 --> 00:00:41,050
come to select an option from here.

11
00:00:41,050 --> 00:00:44,560
So let's say, you say, I've done this and I understand this.

12
00:00:44,650 --> 00:00:46,550
These cells turn green.

13
00:00:46,810 --> 00:00:52,230
If you select some other option like I want to review this later, they turn another color.

14
00:00:52,240 --> 00:00:58,200
This uses conditional formatting. To get to it and to see what type of rule I'm using here.

15
00:00:58,270 --> 00:01:01,330
You need to go and manage the rules.

16
00:01:01,360 --> 00:01:07,900
If you already have rules inside, you can go and manage them or edit them. And you see the different

17
00:01:07,900 --> 00:01:11,340
rules that are used here. To edit them

18
00:01:11,350 --> 00:01:16,560
you can click on it and take a look at the type of formatting that is used.

19
00:01:16,610 --> 00:01:19,870
Now in our example we're gonna start from scratch.

20
00:01:19,870 --> 00:01:22,110
So let's go to our conditional tab.

21
00:01:22,300 --> 00:01:26,720
Let's see some of these in-built functions that we have.

22
00:01:26,860 --> 00:01:29,140
Let's say for yearly salary

23
00:01:29,140 --> 00:01:35,050
I want to highlight the cells that are greater than a specific value.

24
00:01:35,050 --> 00:01:42,160
So I'm going to go with this. Here I can directly input a value or I can use a cell reference.

25
00:01:42,170 --> 00:01:48,580
If I have some number sitting in a cell, I can reference it or let's just type something in.

26
00:01:48,580 --> 00:01:54,280
I'm going to go with a hundred thousand and then I can decide on the color that I want.

27
00:01:54,280 --> 00:02:00,310
So you have some built in custom formatting in there to make it easier for you. If you don't like any

28
00:02:00,310 --> 00:02:05,890
of these built in ones you can go to a custom format and decide on the fill color that you want,

29
00:02:05,890 --> 00:02:09,160
the font color and so on. And then click on

30
00:02:09,160 --> 00:02:10,020
Ok.

31
00:02:10,210 --> 00:02:17,050
Let's go back with this in-built one, click on OK, that's our formatting. So these were the numbers

32
00:02:17,080 --> 00:02:19,170
that are greater than 100,000.

33
00:02:19,410 --> 00:02:26,490
If I change this one to 90,000 press enter, it's not highlighted anymore.

34
00:02:26,530 --> 00:02:30,010
That's the beauty of using conditional formatting.

35
00:02:30,040 --> 00:02:38,110
So in addition to this we had other options. We can do less than, between, equal to, if a text contains

36
00:02:38,110 --> 00:02:41,320
a certain word, duplicate values, and so on.

37
00:02:41,380 --> 00:02:49,810
So for our dates for example, let's go and see who started to work between the first of January 2018

38
00:02:49,900 --> 00:03:00,250
to first of January 2019 and decide and the formatting and then click on OK. And that's our dynamic

39
00:03:00,250 --> 00:03:01,300
formats.

40
00:03:01,390 --> 00:03:08,410
If for some reason you want to go and change that formatting or clear the formatting, you do it from here.

41
00:03:08,410 --> 00:03:15,430
First highlight the area, go to clear rules and clear the rules from either the selected cells

42
00:03:15,910 --> 00:03:22,510
or from the entire sheet. Entire sheet means any conditional formatting you've used anywhere on this

43
00:03:22,510 --> 00:03:24,360
sheet will be deleted.

44
00:03:24,370 --> 00:03:32,440
In this case I'm going to go and clear it from the selected cells. If you want to go and update the existing

45
00:03:32,530 --> 00:03:39,160
conditional formatting, you go to manage rules. You can see the existing formatting used. You can click

46
00:03:39,160 --> 00:03:46,750
to edit it, completely change the conditional formatting to another one. Or, you can add a new rule to

47
00:03:46,750 --> 00:03:47,080
this.

48
00:03:47,110 --> 00:03:53,850
You're not restricted to just one rule. You can select the cells that are greater than one hundred

49
00:03:53,850 --> 00:04:01,540
thousand and format them this way. But you can also add a new rule and go with format only cells that

50
00:04:01,540 --> 00:04:07,600
contain, if the cell value is less than, let's say thirty thousand.

51
00:04:07,600 --> 00:04:16,899
We want to format these in a red color, click on OK, and click on OK. And we see the two rules here. Click

52
00:04:16,899 --> 00:04:20,740
on Apply, if you like what you see, click on OK.

53
00:04:20,800 --> 00:04:26,410
Other useful options are to do a top bottom type of conditional format.

54
00:04:26,410 --> 00:04:30,930
So first off, let's go and clear the rules from the selected cells

55
00:04:31,030 --> 00:04:36,460
and let's go and highlight the top 10 items or the top 10 percent.

56
00:04:36,490 --> 00:04:38,780
Let's go with top 10 percent actually.

57
00:04:39,130 --> 00:04:41,430
I want highlighted in green

58
00:04:41,620 --> 00:04:48,280
and click on OK. In addition to this I want to highlight the bottom 10 percent.

59
00:04:48,460 --> 00:04:49,840
The red fill is ok.

60
00:04:49,840 --> 00:04:50,250
Click on

61
00:04:50,260 --> 00:04:51,290
OK.

62
00:04:51,370 --> 00:04:52,830
And that's all dynamic.

63
00:04:52,840 --> 00:04:56,980
So it takes a look at this dataset and from this data set

64
00:04:57,010 --> 00:05:04,480
It computes the top 10 percent and the bottom 10 percent and highlights them in a dynamic way. So if you take

65
00:05:04,480 --> 00:05:12,220
the 185,000 and and we change this to 85,000 that disappears from

66
00:05:12,310 --> 00:05:13,840
the top 10 percent.

67
00:05:13,840 --> 00:05:23,720
If we do the same for this one, that disappears. And this one, our top 10 percent changes to these values.

68
00:05:23,720 --> 00:05:27,740
Okay so that's how easy it is to use conditional formatting.

69
00:05:27,790 --> 00:05:33,580
The one thing you need to keep in mind is that when you copy cells that have conditional formatting

70
00:05:33,580 --> 00:05:37,420
behind them you will take the conditional formatting with you.

71
00:05:37,420 --> 00:05:39,470
So be aware of this.

72
00:05:39,480 --> 00:05:46,090
So if I copy this area and paste it here and now let's go to conditional formatting options, I see the

73
00:05:46,090 --> 00:05:48,770
rules in there. And I may not want this.

74
00:05:49,060 --> 00:05:56,740
So don't forget to go there and clear the rules from the selected cells. If you want to copy this without

75
00:05:56,740 --> 00:05:59,210
conditional formatting, just copy,

76
00:05:59,260 --> 00:06:00,210
go here,

77
00:06:00,310 --> 00:06:07,740
Paste special, paste the formulas and Number Formats but not the conditional formatting.

78
00:06:07,740 --> 00:06:08,020
Okay.

79
00:06:08,050 --> 00:06:14,350
In the next lecture let's take a look at how we can use conditional formatting together with data bars

80
00:06:14,440 --> 00:06:19,120
and icons to bring attention to specific data points.