1
00:00:00,05 --> 00:00:02,03
- [Instructor] So let's apply Conditional Formatting

2
00:00:02,03 --> 00:00:06,03
so that we can identify project trends and risks.

3
00:00:06,03 --> 00:00:08,06
So if we look at our project data here,

4
00:00:08,06 --> 00:00:10,04
we have a certain amount we're spending

5
00:00:10,04 --> 00:00:11,06
in that year to date spend.

6
00:00:11,06 --> 00:00:13,00
So if we wanted to figure out,

7
00:00:13,00 --> 00:00:15,03
well, are we over or under budget?

8
00:00:15,03 --> 00:00:16,04
Let's take a look first

9
00:00:16,04 --> 00:00:18,03
and see if we can create a column

10
00:00:18,03 --> 00:00:19,04
that will help us with that.

11
00:00:19,04 --> 00:00:22,02
So in cell I1, I'm going to type in delta,

12
00:00:22,02 --> 00:00:24,00
referring to the delta differences

13
00:00:24,00 --> 00:00:25,09
between these two columns.

14
00:00:25,09 --> 00:00:28,05
And then in cell I2, I'm going to type in equals,

15
00:00:28,05 --> 00:00:33,05
I'm going to select budget here, less year two date spend.

16
00:00:33,05 --> 00:00:37,09
So if a value is less than zero, that's not a good thing,

17
00:00:37,09 --> 00:00:39,06
that means we've overspent.

18
00:00:39,06 --> 00:00:41,05
Now as we look at this other data,

19
00:00:41,05 --> 00:00:43,02
there are other things we can measure.

20
00:00:43,02 --> 00:00:46,02
For instance, what if a project is not completed

21
00:00:46,02 --> 00:00:47,08
but it's past its end date?

22
00:00:47,08 --> 00:00:49,03
And what if it's not completed at all?

23
00:00:49,03 --> 00:00:50,08
That presents its own risk.

24
00:00:50,08 --> 00:00:54,02
So let's see if we can score each project one through three.

25
00:00:54,02 --> 00:00:56,06
And I'm going to show you how I'm going to do that.

26
00:00:56,06 --> 00:01:01,01
So in cell J1, I'm going to type in project risk.

27
00:01:01,01 --> 00:01:02,09
That's going to create a new column.

28
00:01:02,09 --> 00:01:06,03
Then in cell J2, we're going to create the structure

29
00:01:06,03 --> 00:01:08,00
to help score each project.

30
00:01:08,00 --> 00:01:09,04
So the first thing I want to find out

31
00:01:09,04 --> 00:01:12,08
is if today's date is greater than the project end date,

32
00:01:12,08 --> 00:01:13,09
and it most likely is,

33
00:01:13,09 --> 00:01:16,05
so I'm going to type in today like that,

34
00:01:16,05 --> 00:01:19,04
and I'm going to type in greater than

35
00:01:19,04 --> 00:01:22,02
and then select project end date.

36
00:01:22,02 --> 00:01:26,09
And I will wrap these in parentheses just to stay organized.

37
00:01:26,09 --> 00:01:28,06
So when dealing with inequalities,

38
00:01:28,06 --> 00:01:31,09
Excel treats trues as ones and falses as zeros.

39
00:01:31,09 --> 00:01:34,03
So this allows us to score up points

40
00:01:34,03 --> 00:01:37,04
based off of whether something passes a true or false test.

41
00:01:37,04 --> 00:01:38,08
Let's keep going.

42
00:01:38,08 --> 00:01:40,01
So next thing we want to find out

43
00:01:40,01 --> 00:01:43,01
is if the status hasn't been completed yet

44
00:01:43,01 --> 00:01:46,02
'cause that's going to present its own risk just inherently.

45
00:01:46,02 --> 00:01:48,04
So we're going to select status here,

46
00:01:48,04 --> 00:01:50,03
and then we're going to do the does not equal,

47
00:01:50,03 --> 00:01:53,04
that's those two brackets like that.

48
00:01:53,04 --> 00:01:56,05
And then in quotes, we're going to type in completed.

49
00:01:56,05 --> 00:01:59,01
So if it isn't equal to completed,

50
00:01:59,01 --> 00:02:01,06
it is going to get another point.

51
00:02:01,06 --> 00:02:02,05
And then finally,

52
00:02:02,05 --> 00:02:04,04
we're going to do another plus here

53
00:02:04,04 --> 00:02:07,03
and we want to find out if we're over budget.

54
00:02:07,03 --> 00:02:09,01
So in this case, we're going to deal with the delta.

55
00:02:09,01 --> 00:02:12,02
If the delta is less than zero,

56
00:02:12,02 --> 00:02:13,08
that's also going to be another point.

57
00:02:13,08 --> 00:02:15,02
So we'll go ahead and hit Enter.

58
00:02:15,02 --> 00:02:18,07
You can see we get scoring one through three here.

59
00:02:18,07 --> 00:02:20,04
So now that we have our data,

60
00:02:20,04 --> 00:02:22,01
let's add in some visualizations

61
00:02:22,01 --> 00:02:24,09
to help quickly identify the trends and risks.

62
00:02:24,09 --> 00:02:26,01
Now as we go through this,

63
00:02:26,01 --> 00:02:27,07
you're going to see there's multiple ways

64
00:02:27,07 --> 00:02:29,01
to show the same thing.

65
00:02:29,01 --> 00:02:31,02
I want you to follow along

66
00:02:31,02 --> 00:02:32,07
just so you know the possibilities,

67
00:02:32,07 --> 00:02:35,02
but, of course, it is up to you to decide

68
00:02:35,02 --> 00:02:36,02
which ones you want to use,

69
00:02:36,02 --> 00:02:39,03
and I would not recommend using them all at once.

70
00:02:39,03 --> 00:02:42,00
All right, so let's start with cell G2.

71
00:02:42,00 --> 00:02:44,08
I'm going to highlight all the way through H8.

72
00:02:44,08 --> 00:02:46,02
I'm going to go to Conditional Formatting

73
00:02:46,02 --> 00:02:47,07
and we're going to add data bars here

74
00:02:47,07 --> 00:02:49,09
to help us understand spend.

75
00:02:49,09 --> 00:02:51,09
So I'll click on this blue one right here.

76
00:02:51,09 --> 00:02:53,05
We can see that we've spent more

77
00:02:53,05 --> 00:02:56,03
than the associated values to the left.

78
00:02:56,03 --> 00:02:58,03
So that's one way to visualize the data.

79
00:02:58,03 --> 00:03:01,00
Another may be to actually just focus

80
00:03:01,00 --> 00:03:02,09
on the calculated column itself.

81
00:03:02,09 --> 00:03:06,07
So I'm going to highlight the data here from I2 through I8.

82
00:03:06,07 --> 00:03:08,07
I will go up to Conditional Formatting.

83
00:03:08,07 --> 00:03:11,00
I'm going to go to Icon Sets,

84
00:03:11,00 --> 00:03:14,03
and I'm going to select this set of icons right here.

85
00:03:14,03 --> 00:03:15,09
Now I've just selected that,

86
00:03:15,09 --> 00:03:18,06
but it doesn't tell me how it's coming up with the answers.

87
00:03:18,06 --> 00:03:21,08
So I'm actually going to go back into Conditional Formatting.

88
00:03:21,08 --> 00:03:24,06
I'm going to click Rules here, Manage Rules,

89
00:03:24,06 --> 00:03:26,03
and then I'm going to click Edit.

90
00:03:26,03 --> 00:03:29,06
Having this icon set selected, I'll click Edit Rule here,

91
00:03:29,06 --> 00:03:31,01
and here's where we're going to just double check

92
00:03:31,01 --> 00:03:32,08
it makes sense, so I look at this,

93
00:03:32,08 --> 00:03:36,08
and I see that it assigned green above the 67th percentile.

94
00:03:36,08 --> 00:03:38,06
So that doesn't quite make sense.

95
00:03:38,06 --> 00:03:40,07
So we're in the green

96
00:03:40,07 --> 00:03:47,04
if we are less than $100,000 away from being over budget.

97
00:03:47,04 --> 00:03:49,07
So in this case I'm going to type in 100,000,

98
00:03:49,07 --> 00:03:51,05
I'm going to select Number here,

99
00:03:51,05 --> 00:03:52,06
and then if it clears that out,

100
00:03:52,06 --> 00:03:54,08
you can just retype it again.

101
00:03:54,08 --> 00:03:57,01
And then over here I'm going to select Number.

102
00:03:57,01 --> 00:03:59,02
In this case, I'm going to make it zero.

103
00:03:59,02 --> 00:04:03,02
So basically if we have at least $100,000 left to spend,

104
00:04:03,02 --> 00:04:04,01
we're going to be in the green.

105
00:04:04,01 --> 00:04:06,02
Now if we have between zero and 100,000,

106
00:04:06,02 --> 00:04:08,03
we're going to be in the yellow.

107
00:04:08,03 --> 00:04:09,09
Otherwise, if we have less than that,

108
00:04:09,09 --> 00:04:11,05
if we've overspent, we're going to be in the red.

109
00:04:11,05 --> 00:04:13,04
So I'll go ahead and click OK on that.

110
00:04:13,04 --> 00:04:15,01
I'll click OK again.

111
00:04:15,01 --> 00:04:16,01
And we can see

112
00:04:16,01 --> 00:04:19,08
that this does help us identify projects that are at risk,

113
00:04:19,08 --> 00:04:21,05
at least in terms of spending.

114
00:04:21,05 --> 00:04:24,08
Now, one final way we can use Conditional Formatting

115
00:04:24,08 --> 00:04:27,00
is just simply by color-coding projects

116
00:04:27,00 --> 00:04:29,02
we want people to pay attention to.

117
00:04:29,02 --> 00:04:30,03
So for instance,

118
00:04:30,03 --> 00:04:34,04
if we were most interested in understanding which projects

119
00:04:34,04 --> 00:04:35,03
had the highest risk,

120
00:04:35,03 --> 00:04:36,01
what we could do

121
00:04:36,01 --> 00:04:39,09
is we could highlight the project descriptions over here,

122
00:04:39,09 --> 00:04:41,07
and then we can go to Conditional Formatting

123
00:04:41,07 --> 00:04:43,06
and we could create a new rule.

124
00:04:43,06 --> 00:04:46,01
In this case, I'm going to say use a formula

125
00:04:46,01 --> 00:04:48,07
to determine which cells to format.

126
00:04:48,07 --> 00:04:51,06
So in this text box where it says format values

127
00:04:51,06 --> 00:04:52,09
where this formula is true,

128
00:04:52,09 --> 00:04:55,03
what we're going to do is we are going to type in equals,

129
00:04:55,03 --> 00:04:57,06
and I'm going to select sell J2 here.

130
00:04:57,06 --> 00:04:59,04
And then I'm going to get rid of the two

131
00:04:59,04 --> 00:05:02,00
because we actually want it to go down the rows.

132
00:05:02,00 --> 00:05:04,02
But we're going to keep the J locked in.

133
00:05:04,02 --> 00:05:06,06
And the reason that this is going to work

134
00:05:06,06 --> 00:05:10,02
is first, it's going to compare whether J2 equals three,

135
00:05:10,02 --> 00:05:12,01
and if it doesn't, it's going to move to the next line

136
00:05:12,01 --> 00:05:13,03
and it's going to work that way

137
00:05:13,03 --> 00:05:14,06
because we got rid of the dollar sign.

138
00:05:14,06 --> 00:05:17,08
So we're going to say dollar sign J2 equals three.

139
00:05:17,08 --> 00:05:19,01
If this returns a true,

140
00:05:19,01 --> 00:05:21,04
note this is format values where this formula is true,

141
00:05:21,04 --> 00:05:24,06
it's going to change the formatting based off of what we set.

142
00:05:24,06 --> 00:05:26,05
So let's just think about this real quick.

143
00:05:26,05 --> 00:05:28,08
We're starting in cell J2 does not equal three,

144
00:05:28,08 --> 00:05:29,07
doesn't do anything.

145
00:05:29,07 --> 00:05:31,05
We're going to move to cell J3,

146
00:05:31,05 --> 00:05:35,00
which follows along with the entire range that we selected.

147
00:05:35,00 --> 00:05:37,01
So it's going to follow along with B2 here,

148
00:05:37,01 --> 00:05:38,05
so it does equal three.

149
00:05:38,05 --> 00:05:41,03
So it's going to change this to whatever color we set.

150
00:05:41,03 --> 00:05:42,08
So let's go to Format here.

151
00:05:42,08 --> 00:05:44,00
I'm just going to make this simple.

152
00:05:44,00 --> 00:05:45,08
We'll just make it kind of a peach color

153
00:05:45,08 --> 00:05:48,08
just to draw our user's eyes into it.

154
00:05:48,08 --> 00:05:50,09
So I'll go ahead and click OK on that.

155
00:05:50,09 --> 00:05:54,04
And you can see it automatically highlights that.

156
00:05:54,04 --> 00:05:56,03
Well, we've reviewed multiple ways

157
00:05:56,03 --> 00:06:00,03
to visualize project trends using Conditional Formatting.

158
00:06:00,03 --> 00:06:01,08
In our next video,

159
00:06:01,08 --> 00:06:03,07
we're going to talk about how to use pivot tables

160
00:06:03,07 --> 00:06:06,00
to create high level summaries.