1 00:00:00,05 --> 00:00:01,06 - [Instructor] Let's talk about how to create 2 00:00:01,06 --> 00:00:05,00 high-level summaries with your project data. 3 00:00:05,00 --> 00:00:06,05 Now, what do I mean by that? 4 00:00:06,05 --> 00:00:08,01 Well, if we look at this data set here, 5 00:00:08,01 --> 00:00:09,06 I see under the Assigned To 6 00:00:09,06 --> 00:00:12,04 that Bob is repeated multiple times. 7 00:00:12,04 --> 00:00:15,01 But I really don't have a high-level overview 8 00:00:15,01 --> 00:00:17,04 of what Bob is overseeing 9 00:00:17,04 --> 00:00:21,00 or how much money has been spent by him year-to-date. 10 00:00:21,00 --> 00:00:22,09 So how could I find that out? 11 00:00:22,09 --> 00:00:27,03 Well, I can use a great feature called a PivotTable. 12 00:00:27,03 --> 00:00:29,06 So what I'm going to do is I'm going to put my cursor anywhere 13 00:00:29,06 --> 00:00:30,09 inside this table. 14 00:00:30,09 --> 00:00:32,03 I'm going to click on Okay, 15 00:00:32,03 --> 00:00:35,02 and them I'm going to click PivotTable. 16 00:00:35,02 --> 00:00:36,06 At this point in time, it's going to ask: 17 00:00:36,06 --> 00:00:38,05 Do I want to create a new worksheet? 18 00:00:38,05 --> 00:00:39,08 I'm going to say yes. 19 00:00:39,08 --> 00:00:43,00 Now, an easy way to remember this is the joke. 20 00:00:43,00 --> 00:00:45,02 A PivotTable walks into a bar and the bartender says, 21 00:00:45,02 --> 00:00:48,01 "Hey, do you want to start a new tab?" 22 00:00:48,01 --> 00:00:50,01 So now that I have started a new PivotTable, 23 00:00:50,01 --> 00:00:51,08 it's going to create a new sheet for me. 24 00:00:51,08 --> 00:00:54,00 And PivotTables are really interesting. 25 00:00:54,00 --> 00:00:57,01 So what they do is they take repeating values 26 00:00:57,01 --> 00:00:59,00 and they collapse them into one. 27 00:00:59,00 --> 00:01:00,08 Let's see that in action. 28 00:01:00,08 --> 00:01:02,07 First thing I'm going to do is I'm going to take Description 29 00:01:02,07 --> 00:01:04,03 and drop that into the rows. 30 00:01:04,03 --> 00:01:07,05 So here are all the different project descriptions 31 00:01:07,05 --> 00:01:09,09 that we have available. 32 00:01:09,09 --> 00:01:13,01 The next thing I'm going to do is I'm going to take Assigned To 33 00:01:13,01 --> 00:01:14,08 and drop that in the columns. 34 00:01:14,08 --> 00:01:16,09 Now, you see there's only three items here, 35 00:01:16,09 --> 00:01:18,09 but if I go back to the original data set, 36 00:01:18,09 --> 00:01:20,02 there's more than three items. 37 00:01:20,02 --> 00:01:22,05 So basically, what a PivotTables does is 38 00:01:22,05 --> 00:01:24,04 it uniquifies the list. 39 00:01:24,04 --> 00:01:26,05 Now, because it collapsed these down 40 00:01:26,05 --> 00:01:29,00 and we only have one item for each of them, 41 00:01:29,00 --> 00:01:31,06 we have to do something with the associated data. 42 00:01:31,06 --> 00:01:35,06 Well, that's what happens in the Values bucket over here. 43 00:01:35,06 --> 00:01:38,06 So whenever we drop something in, it aggregates it. 44 00:01:38,06 --> 00:01:41,00 So in this case, if Bob had repeating values, 45 00:01:41,00 --> 00:01:43,06 it's going to sum it up into one number to correspond 46 00:01:43,06 --> 00:01:45,01 to that one entry for Bob. 47 00:01:45,01 --> 00:01:47,01 So let's see that in action. 48 00:01:47,01 --> 00:01:53,01 I'm going to click YTD Spend and drop that right here. 49 00:01:53,01 --> 00:01:55,05 So now we can see across multiple projects, 50 00:01:55,05 --> 00:01:57,06 this is how much Bob has spent. 51 00:01:57,06 --> 00:01:59,01 Now, one thing I like to do is, 52 00:01:59,01 --> 00:02:01,05 once I've dropped these values in, 53 00:02:01,05 --> 00:02:03,03 I like to format them just to make them look 54 00:02:03,03 --> 00:02:05,09 a little bit better. 55 00:02:05,09 --> 00:02:08,07 And one other thing I really enjoy doing 56 00:02:08,07 --> 00:02:11,00 with a PivotTable is that you can also use slicers. 57 00:02:11,00 --> 00:02:13,06 So you can put your cursor anywhere inside here, 58 00:02:13,06 --> 00:02:15,05 and you can go to PivotTable Analyze 59 00:02:15,05 --> 00:02:17,08 and click Insert Slicer. 60 00:02:17,08 --> 00:02:21,03 Now, slicers work the best with categorical data. 61 00:02:21,03 --> 00:02:23,09 So let's click Status. 62 00:02:23,09 --> 00:02:26,02 That's terrific categorical data. 63 00:02:26,02 --> 00:02:27,05 And now what we can do is, 64 00:02:27,05 --> 00:02:30,04 we can actually slice on these values 65 00:02:30,04 --> 00:02:34,05 and see our high-level summary update. 66 00:02:34,05 --> 00:02:36,05 Well, now that you know how to create high-level summaries 67 00:02:36,05 --> 00:02:38,07 with PivotTables, in the next movie, 68 00:02:38,07 --> 00:02:41,03 we're going to talk about how to create descriptive statistics 69 00:02:41,03 --> 00:02:42,03 so that we can understand 70 00:02:42,03 --> 00:02:45,00 if our projects are on track or not.