1 00:00:04,870 --> 00:00:09,380 Let's take a look at our pivot table challenge for this section. 2 00:00:09,520 --> 00:00:14,010 We have this data set and we need to create a pivot table report out of this. 3 00:00:14,560 --> 00:00:15,720 So let's follow the steps. 4 00:00:15,730 --> 00:00:19,660 Number one: Convert the data to a table and call it TableApp. 5 00:00:19,810 --> 00:00:21,100 Really easy, right? 6 00:00:21,100 --> 00:00:27,520 I just need to click somewhere inside the data set and use the shortcut key Control+T. Or, I can just go 7 00:00:27,520 --> 00:00:29,740 to insert, click on table. 8 00:00:29,890 --> 00:00:33,730 It's going to pick up the surrounding data, that looks good. 9 00:00:33,730 --> 00:00:35,550 Go with ok. 10 00:00:35,800 --> 00:00:37,330 First thing I'm going to do. 11 00:00:37,330 --> 00:00:41,060 Remove the table style to go back to my own style. 12 00:00:41,240 --> 00:00:43,660 And we had to call this TableApp. 13 00:00:43,750 --> 00:00:49,080 I'm just going to type this in there, press enter. That was number one. 14 00:00:49,090 --> 00:00:55,060 Number one is done. Number two: Create a pivot table in G10 of this sheet. 15 00:00:55,060 --> 00:01:01,390 So that's G10, that shows profit by app and region. 16 00:01:01,390 --> 00:01:01,610 Okay. 17 00:01:01,630 --> 00:01:09,100 Now I could go to insert and insert a pivot table. Or, since I've already created a table, I just need 18 00:01:09,100 --> 00:01:17,160 to go to table design and click on summarize with pivot table. It's picked up my table name. I want it 19 00:01:17,230 --> 00:01:21,230 in the existing sheet and I wanted it in G10. 20 00:01:21,410 --> 00:01:24,870 So that's right here, click on ok. 21 00:01:24,970 --> 00:01:27,700 And I have the place holder for my pivot. 22 00:01:27,700 --> 00:01:28,600 So what do we want? 23 00:01:28,600 --> 00:01:33,670 We want to have app by region and we want to take a look at the profit. 24 00:01:33,760 --> 00:01:42,030 So I'm gonna put app to the rows, I'm just dragging it down. Region to the columns and profit to values. 25 00:01:42,040 --> 00:01:46,600 And if I just put a checkmark here, Excel is going to know, okay this has numbers. I probably want to put 26 00:01:46,600 --> 00:01:49,430 it in the values and it put it there correctly. 27 00:01:49,570 --> 00:01:53,640 So now I have my app report by region. 28 00:01:53,680 --> 00:02:00,970 Now we can just update the style of the pivot table and let's just also get rid of these field headers here. 29 00:02:00,970 --> 00:02:03,790 Iinstead of Sum of profit 30 00:02:03,790 --> 00:02:13,330 I'm going to put in profits, space, and press enter. Let's also go to the design tab and select another style. 31 00:02:13,980 --> 00:02:21,550 Now also instead of grand total, I'm just going to call this total and press enter which also changes 32 00:02:21,580 --> 00:02:25,210 it up here as well. So that's number two. 33 00:02:25,210 --> 00:02:33,870 Number two is done. Number three: Add a slicer for division and format values with a thousand separator. 34 00:02:33,880 --> 00:02:34,110 Okay. 35 00:02:34,120 --> 00:02:37,920 Because currently the information we have is for all divisions. 36 00:02:37,940 --> 00:02:44,830 Now it would be nice to click on a division and see the information only for that division. Or, select 37 00:02:45,010 --> 00:02:53,380 more than one division. Slicers will let us do this in a very user friendly interface. To insert a slicer, 38 00:02:53,380 --> 00:02:58,180 Let's go to pivot table analyse and click on insert slicer. 39 00:02:58,180 --> 00:02:59,930 Which one do we need to pick? 40 00:02:59,980 --> 00:03:00,670 Division. 41 00:03:00,670 --> 00:03:03,090 Put a checkmark click, on ok. 42 00:03:03,280 --> 00:03:04,840 And that's our slicer. 43 00:03:04,850 --> 00:03:06,160 It's already there. 44 00:03:06,220 --> 00:03:10,480 All we have to do is format it properly so it fits with our report. 45 00:03:10,510 --> 00:03:14,320 But now if I just click on these, it's going to update. 46 00:03:14,320 --> 00:03:21,400 Let's go to these slicer tab here, select a formatting that fits our pivot table style. 47 00:03:21,400 --> 00:03:28,860 And I'm also going to put it across columns, expand this and make it fit here. 48 00:03:29,470 --> 00:03:32,700 I can change the heading here from slicer settings. 49 00:03:32,980 --> 00:03:37,720 So for the caption, I can just remove it if it's obvious what it is. 50 00:03:37,720 --> 00:03:41,120 Or I can type in select division and click on 51 00:03:41,130 --> 00:03:41,800 Ok. 52 00:03:41,930 --> 00:03:48,760 Now I have the ability to select one division or select more than one division by just holding down the 53 00:03:48,880 --> 00:03:49,970 control key. 54 00:03:50,050 --> 00:03:55,810 You can also enable multi select by clicking on this. So that was done. 55 00:03:55,870 --> 00:04:01,690 We also need to format this so it's easier to read the numbers. We're going to use a thousand separator. 56 00:04:01,690 --> 00:04:03,120 So right mouse click. 57 00:04:03,250 --> 00:04:04,660 Which one do I need to pick? 58 00:04:04,660 --> 00:04:08,770 Format Cells or number format? Number format. 59 00:04:08,770 --> 00:04:09,290 Right? 60 00:04:09,310 --> 00:04:13,410 Because number format will stay with the pivot table. 61 00:04:13,450 --> 00:04:16,440 Let's go to number, use a thousand separator. 62 00:04:16,570 --> 00:04:22,480 In this case I'm going to go with zero decimal places and all of these are updated. 63 00:04:22,480 --> 00:04:23,760 That's my report. 64 00:04:23,860 --> 00:04:25,050 Number three is done. 65 00:04:25,060 --> 00:04:29,670 Now before I put a checkmark here, let's just test one thing. 66 00:04:29,830 --> 00:04:32,820 Let's add new information to this. 67 00:04:32,950 --> 00:04:37,030 In fact let's even add a new division to this. 68 00:04:37,030 --> 00:04:43,580 I'm just going to drag this down and call this the health division. 69 00:04:43,730 --> 00:04:48,110 And now let's see if the information pops up here. 70 00:04:48,250 --> 00:04:51,930 We have to refresh, so right mouse click, refresh. 71 00:04:52,090 --> 00:04:53,500 Health is there 72 00:04:53,500 --> 00:04:55,960 and I have my productivity. 73 00:04:55,990 --> 00:05:02,240 Now the moment you see that these don't fit here, you can go and update the slicer settings right here. 74 00:05:02,300 --> 00:05:04,390 So columns are three. 75 00:05:04,400 --> 00:05:12,080 I need four columns now and now my health division is right here. Before I put a check mark here. 76 00:05:12,080 --> 00:05:15,150 Notice what's happened to our pivot table. 77 00:05:15,150 --> 00:05:21,290 The columns auto fit based on our selection because you can see these shifting. When I click here, 78 00:05:21,290 --> 00:05:23,230 these are shifted far to the left. 79 00:05:23,240 --> 00:05:25,520 When I click here, they move here. 80 00:05:25,520 --> 00:05:26,570 What if we don't want that? 81 00:05:26,570 --> 00:05:28,150 What if we want it fixed? 82 00:05:28,160 --> 00:05:30,660 Do you remember where that setting was? 83 00:05:31,280 --> 00:05:34,040 That was under the pivot table options. 84 00:05:34,040 --> 00:05:40,940 So go back to pivot table analyze, go to options, and take away the check mark for auto fit column width 85 00:05:41,390 --> 00:05:42,110 on update. 86 00:05:42,110 --> 00:05:44,430 Because this could get quite annoying 87 00:05:44,630 --> 00:05:51,270 if you want to have a fixed reports. I'm just going to adjust these, let's say to this size. 88 00:05:51,280 --> 00:05:53,210 Let's just make sure that nothing gets shifted. 89 00:05:53,210 --> 00:05:59,710 When I click on health, everything stays the same width. So that's our pivot table with slicer 90 00:05:59,720 --> 00:06:00,800 report. 91 00:06:00,830 --> 00:06:03,030 Our challenge is completed.