1 00:00:00,600 --> 00:00:05,970 Now that we take a look at the benefits of pivot tables, let's take a look at how we can insert one. 2 00:00:06,000 --> 00:00:07,840 This is our sample data set. 3 00:00:07,890 --> 00:00:13,410 We have information on company, sales, customer information, 4 00:00:13,590 --> 00:00:15,790 the articles we are selling. 5 00:00:15,790 --> 00:00:20,330 This company is selling women's and men's clothing and accessories. 6 00:00:20,520 --> 00:00:21,780 The number of rejects. 7 00:00:21,900 --> 00:00:24,830 These were the number of items that were sent back. 8 00:00:24,900 --> 00:00:29,790 The quantity of sales and the sales in terms of U.S. dollars. 9 00:00:29,790 --> 00:00:33,120 This dataset goes to line 108. 10 00:00:33,120 --> 00:00:35,940 It's a smaller dataset. Now based on this, 11 00:00:35,940 --> 00:00:37,770 We need to answer these two questions: 12 00:00:37,770 --> 00:00:40,530 Which company sells the most in terms of volume? 13 00:00:40,710 --> 00:00:47,310 And also in terms of U.S. dollars. The advantage of using pivot tables is that you're not restricted 14 00:00:47,310 --> 00:00:49,800 to the amount of data you have. 15 00:00:49,800 --> 00:00:54,290 You can have a small dataset like we have or a much larger dataset. 16 00:00:54,360 --> 00:01:00,030 The pivot table is going to do all the summation and aggregation for you. Before we go ahead though and 17 00:01:00,030 --> 00:01:01,780 insert our pivot table. 18 00:01:01,860 --> 00:01:06,690 Let's quickly go through our checklist. Is our data organized in a list type of format? 19 00:01:06,930 --> 00:01:07,920 Yes, it is. 20 00:01:07,920 --> 00:01:14,540 We don't have any empty columns here and we don't have any summation rows in the middle of this data set. 21 00:01:14,580 --> 00:01:18,390 Do we have a title for each column here? 22 00:01:18,390 --> 00:01:19,850 This one doesn't have. 23 00:01:19,930 --> 00:01:25,680 Now I'm just going to assume that I missed this one and insert my pivot table and see the type of error 24 00:01:25,890 --> 00:01:26,770 we might get. 25 00:01:26,790 --> 00:01:31,020 First off, let's select the data set. To do that quickly 26 00:01:31,110 --> 00:01:33,800 we can use the shortcut key Control+A. 27 00:01:33,810 --> 00:01:37,710 Now let's go to the Insert tab and insert a pivot table. 28 00:01:37,830 --> 00:01:40,530 We've already selected a range which we can see here. 29 00:01:40,650 --> 00:01:44,490 So if you hadn't selected anything you can do it at this stage. 30 00:01:44,490 --> 00:01:48,330 Then we have the ability to insert the pivot table on a new worksheet. 31 00:01:48,330 --> 00:01:54,420 If we go with this Excel will automatically create a new tab for us and insert the pivot table on that sheet. 32 00:01:54,420 --> 00:01:57,510 Or, we can select existing worksheets. 33 00:01:57,510 --> 00:02:00,020 Now remember I have a missing header here. 34 00:02:00,060 --> 00:02:02,820 I'm just going to go with okay and see what happens. 35 00:02:02,850 --> 00:02:05,640 I get this error. To create a pivot table 36 00:02:05,640 --> 00:02:08,979 you must use data as organized with labeled columns. 37 00:02:09,000 --> 00:02:09,750 I can't 38 00:02:09,750 --> 00:02:13,370 insert it until I add a column header for that. 39 00:02:13,380 --> 00:02:17,610 I'm going to go with cancel and add in my header. 40 00:02:17,610 --> 00:02:20,470 This looks like it's the code for this article. 41 00:02:20,470 --> 00:02:23,220 I'll just type in Article code for that. 42 00:02:23,250 --> 00:02:25,060 Now let's go and repeat our steps. 43 00:02:25,130 --> 00:02:27,880 Control+A, insert pivot table. 44 00:02:27,930 --> 00:02:32,610 I'll go with existing worksheet instead of a new worksheet. For location 45 00:02:32,610 --> 00:02:35,430 I'm going to insert it right here and click on 46 00:02:35,430 --> 00:02:36,080 Ok. 47 00:02:36,120 --> 00:02:43,620 First thing I get is a place holder for my pivot table report. And I get this pivot table fields items 48 00:02:43,620 --> 00:02:44,700 right here. 49 00:02:44,700 --> 00:02:51,490 Notice one thing. The names I see here are identical to my headers right here. Down here 50 00:02:51,510 --> 00:02:55,650 I get to decide how I want my table to look like. 51 00:02:55,650 --> 00:03:02,680 Notice also one thing. I immediately got two new tabs here an analyze tab and a design tab. 52 00:03:02,700 --> 00:03:03,910 These are active 53 00:03:03,960 --> 00:03:11,760 if I'm in my pivot table. The moment I click away those tabs disappear. I click in there, they appear. 54 00:03:11,870 --> 00:03:16,110 Now a lot of these features are features we're gonna be covering in the next lectures. 55 00:03:16,110 --> 00:03:19,480 One thing I want to bring your attention to is this "Fields list". 56 00:03:19,650 --> 00:03:26,490 If for any reason you close this pivot table fields. To get it back you can go to the analyze tab and 57 00:03:26,490 --> 00:03:28,530 click on this fields list. 58 00:03:28,560 --> 00:03:34,700 You can also drag this field list and bring it to where you want on your sheet. 59 00:03:34,710 --> 00:03:36,690 Now let's answer these questions. 60 00:03:36,690 --> 00:03:38,930 Which company sells the most? 61 00:03:38,940 --> 00:03:43,480 Which categories do I need to be able to answer the first question. 62 00:03:43,890 --> 00:03:45,090 Well, I need company. 63 00:03:45,270 --> 00:03:48,880 So what I'm going to do is place a check mark right beside it. 64 00:03:48,920 --> 00:03:55,200 What Excel automatically does is it tries to take a good guess at where you want this company 65 00:03:55,200 --> 00:03:59,960 to sit on your report. If you want it to be in the rows or inside the values here. 66 00:04:00,000 --> 00:04:06,760 If it comes across a field that's just numbers it's going to put it in the values. 67 00:04:06,780 --> 00:04:14,430 If for example I selected the article code which was numbers, it's going to think it's a value field. 68 00:04:14,490 --> 00:04:18,350 If it guesses wrong, you can drag and drop fields as well. 69 00:04:18,500 --> 00:04:22,120 You can pull it here or you can kick it out right here. 70 00:04:22,120 --> 00:04:27,390 Now in this case I don't want the company code. I actually want the company name so I'm going to place 71 00:04:27,390 --> 00:04:30,300 a check mark here and take away this check mark. 72 00:04:30,720 --> 00:04:34,870 Now for the value that I'm analyzing, I first want the volume. 73 00:04:34,890 --> 00:04:40,110 Let's go all the way down and get the quantity and just place a check mark beside it. 74 00:04:40,110 --> 00:04:46,510 It realized that it's a value field. And it's also taking a best guess of the type of analysis I want to do. 75 00:04:46,510 --> 00:04:50,040 And correctly it's put the sum of quantity. 76 00:04:50,190 --> 00:04:55,590 So if I wanted to get something else. Let's say I want to get the count of quantity or the average 77 00:04:55,590 --> 00:05:02,750 of quantity. I can click on this dropdown, go to value fields settings and change my selection from 78 00:05:02,750 --> 00:05:03,350 here. 79 00:05:03,350 --> 00:05:04,520 But Sum was correct. 80 00:05:04,550 --> 00:05:05,600 So I'm gonna go with 81 00:05:05,600 --> 00:05:06,250 Ok. 82 00:05:06,350 --> 00:05:09,210 So right now I have the answer to my question. 83 00:05:09,380 --> 00:05:18,630 Which company sells the most? It's Urban Right. Now, is the company that sells the most 84 00:05:18,660 --> 00:05:22,950 does it also sell the most in terms of U.S. dollars? 85 00:05:22,950 --> 00:05:25,990 Let's bring in sales in USD. 86 00:05:26,130 --> 00:05:33,840 It's not. The one that actually generates the most sales in terms of dollars is Lucas Basics. Which means 87 00:05:33,840 --> 00:05:37,960 they have more expensive products than Urban Right has. 88 00:05:38,070 --> 00:05:38,610 That's it. 89 00:05:38,610 --> 00:05:46,190 That's how fast it is to come up with answers to these questions using a pivot table. Now if you wanted 90 00:05:46,190 --> 00:05:47,120 to print this. 91 00:05:47,120 --> 00:05:48,380 This doesn't look so great. 92 00:05:48,380 --> 00:05:52,520 So you might want to make some adjustments. In terms of design 93 00:05:52,550 --> 00:05:59,150 let's go to the design tab and see the different styles we have from here. We can change that style 94 00:05:59,240 --> 00:06:01,110 by selecting from this list. 95 00:06:01,130 --> 00:06:04,650 You can also create your own pivot table style here. 96 00:06:04,670 --> 00:06:07,330 I'm just going to go with this one in this case. 97 00:06:07,430 --> 00:06:12,500 Now the second thing I want to do is to adjust the number formatting. 98 00:06:12,500 --> 00:06:16,590 Now here's something you have to be careful on. When you right mouse click here 99 00:06:16,760 --> 00:06:19,250 don't select format cells 100 00:06:19,280 --> 00:06:25,100 if you're adjusting a pivot table number. Instead, select number format. 101 00:06:25,100 --> 00:06:32,480 Now the reason for this is that the number format is the formatting that's applicable to the pivot 102 00:06:32,480 --> 00:06:34,220 table for this field. 103 00:06:34,220 --> 00:06:39,170 Which means if your pivot table expands, that number format comes with it. 104 00:06:39,170 --> 00:06:45,350 If you selct format cells, we're just formatting the underlying cell and not the column of our pivot 105 00:06:45,350 --> 00:06:46,150 table. 106 00:06:46,310 --> 00:06:48,380 I'm gonna go with number format here. 107 00:06:48,650 --> 00:06:53,270 Select thousand separator and zero decimal places. 108 00:06:53,270 --> 00:06:56,150 Now let's apply the same formatting to this one. 109 00:07:00,370 --> 00:07:01,880 Ok so this looks good. 110 00:07:02,000 --> 00:07:05,440 Now the sum of quantity that's something I want to change. 111 00:07:05,450 --> 00:07:07,190 I just want to put quantity. 112 00:07:07,190 --> 00:07:11,000 But the problem is the moment I press enter the pivot table doesn't like it. 113 00:07:11,030 --> 00:07:14,180 It tells me that field name already exists. 114 00:07:14,180 --> 00:07:15,700 I can't use a field name 115 00:07:15,710 --> 00:07:19,300 that's identical to something in here. But I could do this: 116 00:07:19,340 --> 00:07:25,020 I could add a space right after or before the word because now they're not identical 117 00:07:25,130 --> 00:07:28,880 and I can get them to look identical. 118 00:07:28,880 --> 00:07:35,570 This row label here also doesn't look good. I'm gonna go to pivot table analyze and here take 119 00:07:35,570 --> 00:07:37,780 away the field headers. 120 00:07:37,940 --> 00:07:45,410 This takes the row labels part away. Ok so clicking it shows it, clicking it again takes it away. 121 00:07:45,410 --> 00:07:51,340 The field list that was this one if I click it, it disappears. If I click it again, it appears. 122 00:07:51,350 --> 00:07:57,080 So these are toggles. The +/- buttons is something we're going to take a look at in the next lectures. 123 00:07:57,080 --> 00:08:04,950 Now one really useful pivot table feature is the ability to drill down even in this view. 124 00:08:04,970 --> 00:08:10,570 Let's say I'm really curious at what items are generating this. 125 00:08:10,730 --> 00:08:13,660 All I have to do is double click on this field. 126 00:08:13,720 --> 00:08:21,720 What Excel does is it creates a new sheet for me with all the details that sum up to that number. 127 00:08:21,980 --> 00:08:26,580 So it basically takes only the relevant fields that make up that number. 128 00:08:26,600 --> 00:08:33,650 If I highlight this I see the exact same sum that I saw in my pivot table right here. 129 00:08:33,650 --> 00:08:39,200 These are additional sheets that get added automatically by double clicking 130 00:08:39,230 --> 00:08:42,750 you can safely remove these whenever you want. 131 00:08:42,799 --> 00:08:47,000 One last thing we're going to check is how this pivot table gets updated. 132 00:08:47,240 --> 00:08:50,680 Let's say we change one of these fields. 133 00:08:50,780 --> 00:08:52,830 This fields belongs to Urban 134 00:08:52,880 --> 00:08:53,630 Right. 135 00:08:53,630 --> 00:08:59,770 This one, sales in USD. Let's just increase that to a really big amount. 136 00:08:59,810 --> 00:09:02,330 When I press enter it doesn't show here. 137 00:09:02,330 --> 00:09:08,300 Right so this is another thing you need to take care about pivot tables is to get your values show inside 138 00:09:08,300 --> 00:09:09,340 the pivot table. 139 00:09:09,470 --> 00:09:15,470 You have to refresh it. You can do that by right mouse clicking and selecting refresh from here. Or, going 140 00:09:15,470 --> 00:09:20,640 to the analyze tab and refreshing it from here and then it pulls through. 141 00:09:20,660 --> 00:09:23,850 So I'm just gonna press Control+Z to go back. 142 00:09:23,930 --> 00:09:30,070 Now, one disadvantage of the way we did this is when it comes to adding new data. 143 00:09:30,320 --> 00:09:35,100 Let's go to the bottom of this dataset and add in a new field. 144 00:09:35,120 --> 00:09:40,510 I'm going to go with 1090DE Leila Basics. 145 00:09:40,640 --> 00:09:44,380 The rest let's just copy and paste here. 146 00:09:44,570 --> 00:09:47,880 Now let's go up here and refresh our pivot table. 147 00:09:48,030 --> 00:09:51,010 Right mouse click, refresh, it doesn't show up. 148 00:09:51,050 --> 00:09:51,930 Why? 149 00:09:51,950 --> 00:09:58,880 Because remember we restricted our data set to a specific range. To be able to show that new data 150 00:09:58,880 --> 00:10:06,000 in here now I have to go back to the analyze tab and go to change data source and update it right here. 151 00:10:06,000 --> 00:10:11,440 I'm going to go with 109, press enter and now Leila Basics is there. 152 00:10:11,690 --> 00:10:15,020 But this is something that you probably want to avoid, right? 153 00:10:15,080 --> 00:10:19,890 Because you don't want to be expanding your data set as new data come in. 154 00:10:19,940 --> 00:10:24,320 This is why you should use Excel tables as your source. 155 00:10:24,320 --> 00:10:27,230 And this is something we're going to take a look at in the next lecture.