1 00:00:00,05 --> 00:00:02,02 - [Instructor] In this video, we're going to talk 2 00:00:02,02 --> 00:00:05,06 about two different ways to visualize progress. 3 00:00:05,06 --> 00:00:06,05 The first way is going to be 4 00:00:06,05 --> 00:00:08,08 using a conditional format icon set 5 00:00:08,08 --> 00:00:12,01 and the next way is going to be using a pivot chart. 6 00:00:12,01 --> 00:00:13,08 Well, let's go ahead and get started. 7 00:00:13,08 --> 00:00:17,06 The first thing we want to do is actually figure out 8 00:00:17,06 --> 00:00:20,01 how we are doing progress-wise 9 00:00:20,01 --> 00:00:21,09 between our budget and our expenses. 10 00:00:21,09 --> 00:00:23,03 So the first thing we want to do 11 00:00:23,03 --> 00:00:25,01 is just take a quick note of the data. 12 00:00:25,01 --> 00:00:27,03 You'll see there's a column in here called Progress, 13 00:00:27,03 --> 00:00:31,08 so this tells us how far we are along in running our project 14 00:00:31,08 --> 00:00:34,01 with regard to the milestones we've set, 15 00:00:34,01 --> 00:00:36,08 but there's also an additional metric we want to track. 16 00:00:36,08 --> 00:00:38,04 So you'll note we have Progress right here, 17 00:00:38,04 --> 00:00:42,06 but we also have how much of our budget we've set aside 18 00:00:42,06 --> 00:00:44,04 and how much we've already spent, 19 00:00:44,04 --> 00:00:46,04 so that's another form of progress, right? 20 00:00:46,04 --> 00:00:48,05 So we want to create a new column in here. 21 00:00:48,05 --> 00:00:50,06 You can see I right clicked on column H 22 00:00:50,06 --> 00:00:52,04 and I hit Insert New Column. 23 00:00:52,04 --> 00:00:56,06 In this case, I'm going to call this Percent Spent like that 24 00:00:56,06 --> 00:00:59,01 and this is just going to be a really easy equation. 25 00:00:59,01 --> 00:01:01,08 We'll type in equals, I'll select Expenses, 26 00:01:01,08 --> 00:01:03,07 and divide it by the budget. 27 00:01:03,07 --> 00:01:07,02 And so if it comes up as dollar signs for you, that's okay. 28 00:01:07,02 --> 00:01:10,01 Highlight the whole thing and click Percentage. 29 00:01:10,01 --> 00:01:12,03 Okay, so what we have here is the idea 30 00:01:12,03 --> 00:01:14,07 that 51% of the project is done 31 00:01:14,07 --> 00:01:17,01 and 50% of the budget has been spent, 32 00:01:17,01 --> 00:01:18,07 so that's actually pretty good, right? 33 00:01:18,07 --> 00:01:20,09 I think that we would be happy with that. 34 00:01:20,09 --> 00:01:23,07 So one of the ways we can actually visualize progress, 35 00:01:23,07 --> 00:01:25,07 if we're just thinking about progress here, 36 00:01:25,07 --> 00:01:28,00 is we can apply a conditional format to this column. 37 00:01:28,00 --> 00:01:30,08 So I'm going to highlight all the data in column D 38 00:01:30,08 --> 00:01:33,08 and of course, you can use a keyboard for this as well. 39 00:01:33,08 --> 00:01:35,01 I just use my mouse. 40 00:01:35,01 --> 00:01:36,05 And then from on the Home tab, 41 00:01:36,05 --> 00:01:38,01 I'm going to click conditional formatting 42 00:01:38,01 --> 00:01:40,00 and under Icon Sets, 43 00:01:40,00 --> 00:01:43,07 I'm going to use these little pie charts here. 44 00:01:43,07 --> 00:01:45,02 I think actually this is technically 45 00:01:45,02 --> 00:01:48,00 called the Harvey Balls font from, 46 00:01:48,00 --> 00:01:50,03 I think it originated at Booz Allen Hamilton 47 00:01:50,03 --> 00:01:52,00 which I used to work at, 48 00:01:52,00 --> 00:01:54,08 so I think it's very cool that they have this in here. 49 00:01:54,08 --> 00:01:56,04 That was one of the original fonts 50 00:01:56,04 --> 00:01:58,04 to actually visualize this type of stuff. 51 00:01:58,04 --> 00:02:00,03 Okay, so this is a good way to do it. 52 00:02:00,03 --> 00:02:03,02 I mean, one thing that you might consider doing is, 53 00:02:03,02 --> 00:02:05,06 I'm going to right click on this and hit Cut 54 00:02:05,06 --> 00:02:06,06 and then I'm going to right click here 55 00:02:06,06 --> 00:02:08,03 and I'll do Insert Cut Sales. 56 00:02:08,03 --> 00:02:09,09 So I'm just moving this to the front 57 00:02:09,09 --> 00:02:11,09 and then of course, you could decide to yourself 58 00:02:11,09 --> 00:02:13,06 well, I don't really need those percentages. 59 00:02:13,06 --> 00:02:15,05 So you'll go from on the Home tab, 60 00:02:15,05 --> 00:02:16,03 you'll see there's 61 00:02:16,03 --> 00:02:18,02 this little number pop out, click that here. 62 00:02:18,02 --> 00:02:21,04 Under Custom, you can actually just remove the numbers, 63 00:02:21,04 --> 00:02:24,01 replace this general with a semicolon, 64 00:02:24,01 --> 00:02:25,03 and that's just going to remove the numbers. 65 00:02:25,03 --> 00:02:26,07 It doesn't actually remove the values. 66 00:02:26,07 --> 00:02:27,08 It just masks the numbers. 67 00:02:27,08 --> 00:02:29,05 So in a certain sense, 68 00:02:29,05 --> 00:02:32,00 if you really just wanted a quick visual 69 00:02:32,00 --> 00:02:33,07 of how everything was doing, 70 00:02:33,07 --> 00:02:35,06 you could just push that over here, 71 00:02:35,06 --> 00:02:38,02 and then you can actually click on this down arrow 72 00:02:38,02 --> 00:02:40,08 and hit Sort Largest to Smallest 73 00:02:40,08 --> 00:02:42,02 and that's going to actually sort them all. 74 00:02:42,02 --> 00:02:44,00 So you could see the ones that have 75 00:02:44,00 --> 00:02:46,02 the most dunk first and vice versa 76 00:02:46,02 --> 00:02:48,02 and remember these are still numbers behind here. 77 00:02:48,02 --> 00:02:51,01 They're just being masked by this conditional format. 78 00:02:51,01 --> 00:02:53,00 All right, so that's one way to measure progress 79 00:02:53,00 --> 00:02:56,07 if you wanted to do it within the dataset itself. 80 00:02:56,07 --> 00:02:58,08 Another way to do it is with a pivot table. 81 00:02:58,08 --> 00:03:01,05 So having my cursor anywhere inside of here, 82 00:03:01,05 --> 00:03:03,00 I'm going to click on Insert. 83 00:03:03,00 --> 00:03:04,06 I'll click Pivot Table here 84 00:03:04,06 --> 00:03:07,01 and we're going to do a New Worksheet. 85 00:03:07,01 --> 00:03:09,04 So I'm going to zoom out here 86 00:03:09,04 --> 00:03:12,03 and what I'm going to do is I'll drop Project Name into rows, 87 00:03:12,03 --> 00:03:14,01 that's going to gimme the big list of project names, 88 00:03:14,01 --> 00:03:17,00 and then what I'll do is I'm going to drop Progress 89 00:03:17,00 --> 00:03:18,08 into the values over here 90 00:03:18,08 --> 00:03:20,04 and let's see here. 91 00:03:20,04 --> 00:03:24,08 We also want to drop Percent Spent over here. 92 00:03:24,08 --> 00:03:27,07 Okay, so what we want to do is visualize 93 00:03:27,07 --> 00:03:31,05 what the progress was versus the amount spent. 94 00:03:31,05 --> 00:03:34,01 So I'll start in A4, I'll hold Shift. 95 00:03:34,01 --> 00:03:36,08 I'll hit right, right, Control + Shift + Down. 96 00:03:36,08 --> 00:03:38,06 After you've done that Control + Shift + Down 97 00:03:38,06 --> 00:03:39,06 and you're holding Control + Shift, 98 00:03:39,06 --> 00:03:42,03 hit that up key so that you highlight the whole region 99 00:03:42,03 --> 00:03:44,01 but don't include the grand total. 100 00:03:44,01 --> 00:03:46,01 So from here, I'll click Insert. 101 00:03:46,01 --> 00:03:54,00 I'm going to insert a new 2D column chart like this. 102 00:03:54,00 --> 00:03:58,02 So what we want is one set of data 103 00:03:58,02 --> 00:03:59,07 is going to show us our progress 104 00:03:59,07 --> 00:04:02,04 and the other set of data is going to show us our amount spent. 105 00:04:02,04 --> 00:04:03,09 So the way I like to do this 106 00:04:03,09 --> 00:04:05,07 is I'm going to right click on the orange. 107 00:04:05,07 --> 00:04:07,07 That's the amount spent on mine. 108 00:04:07,07 --> 00:04:09,09 I'll click on Format Data Series here. 109 00:04:09,09 --> 00:04:12,02 We're going to make this series overlap 100% 110 00:04:12,02 --> 00:04:14,04 so there are one on top of the other 111 00:04:14,04 --> 00:04:16,00 and then I'm going to go to Design. 112 00:04:16,00 --> 00:04:19,02 And with that percentage spent selected, 113 00:04:19,02 --> 00:04:22,00 I'm going to go click Change Chart Type 114 00:04:22,00 --> 00:04:24,02 and where it says Sum of Percent Spent, 115 00:04:24,02 --> 00:04:27,06 I'm going to change that to a regular line chart like this. 116 00:04:27,06 --> 00:04:29,09 So line chart with markers. 117 00:04:29,09 --> 00:04:34,05 I'll hit Okay and then I'm going to select it one more time. 118 00:04:34,05 --> 00:04:35,07 I'll go to Format. 119 00:04:35,07 --> 00:04:37,01 I'm going to remove the shape outline. 120 00:04:37,01 --> 00:04:38,04 I don't care about that, 121 00:04:38,04 --> 00:04:40,02 but I would like these markers 122 00:04:40,02 --> 00:04:42,08 on here to be a little bit more interesting. 123 00:04:42,08 --> 00:04:46,08 So from here I will click on the paint bucket 124 00:04:46,08 --> 00:04:48,08 and you'll see there's a little marker button here. 125 00:04:48,08 --> 00:04:51,09 I'll click marker options, I'll go to Built-in, 126 00:04:51,09 --> 00:04:54,09 and on this dropdown I'm going to select the line like that 127 00:04:54,09 --> 00:04:57,00 and then I'm just going to make them bigger. 128 00:04:57,00 --> 00:05:00,01 So this is going to give us a sense of comparison. 129 00:05:00,01 --> 00:05:03,04 So we know that something that looks like this here, 130 00:05:03,04 --> 00:05:06,06 where the progress is less than spent, that's good. 131 00:05:06,06 --> 00:05:08,01 But if we spent more than the progress, 132 00:05:08,01 --> 00:05:10,03 well maybe we should look into that project. 133 00:05:10,03 --> 00:05:14,05 So let's see if we can organize this by the amount spent. 134 00:05:14,05 --> 00:05:16,03 I have clicked cell C4. 135 00:05:16,03 --> 00:05:18,00 I'm going to click Sort over here. 136 00:05:18,00 --> 00:05:19,02 Let's do largest to smallest 137 00:05:19,02 --> 00:05:22,01 so we can actually see this on our chart 138 00:05:22,01 --> 00:05:23,06 and I don't really need this, 139 00:05:23,06 --> 00:05:24,07 so I'm going to delete that. 140 00:05:24,07 --> 00:05:26,06 I just clicked the legend, hit Delete. 141 00:05:26,06 --> 00:05:28,02 Now this is a lot of data at once. 142 00:05:28,02 --> 00:05:33,06 Let's see if we can actually do a little bit more with it. 143 00:05:33,06 --> 00:05:36,06 I'm going to click into my pivot table 144 00:05:36,06 --> 00:05:37,07 and I'll go to Insert 145 00:05:37,07 --> 00:05:39,07 and I'll click Slicer, 146 00:05:39,07 --> 00:05:43,01 and in this case, let's slice on some people 147 00:05:43,01 --> 00:05:50,01 so let's do Assigned To like that, 148 00:05:50,01 --> 00:05:52,04 and now from here we can actually drill down 149 00:05:52,04 --> 00:05:54,05 and see how everyone's doing. 150 00:05:54,05 --> 00:05:56,08 So we see over here under Nail It 151 00:05:56,08 --> 00:05:58,07 that we've spent more than the progress. 152 00:05:58,07 --> 00:06:00,02 That's probably not good. 153 00:06:00,02 --> 00:06:02,03 In this case, we've spent a little 154 00:06:02,03 --> 00:06:04,01 bit more than the progress in some of these cases. 155 00:06:04,01 --> 00:06:05,06 Let's see if anyone's winning here. 156 00:06:05,06 --> 00:06:07,09 So Mohammad's not doing so bad. 157 00:06:07,09 --> 00:06:12,07 Looks like Omari is right on schedule with their projects. 158 00:06:12,07 --> 00:06:14,08 Well, now that we've created progress charts 159 00:06:14,08 --> 00:06:17,01 to visualize how our projects are doing, 160 00:06:17,01 --> 00:06:18,07 in the next video, 161 00:06:18,07 --> 00:06:20,06 we're going to talk about in-cell bar charts 162 00:06:20,06 --> 00:06:22,07 which is going to actually help us understand 163 00:06:22,07 --> 00:06:25,00 data at the project record level.