1 00:00:08,160 --> 00:00:08,970 Hello everyone. 2 00:00:08,970 --> 00:00:14,250 Welcome to this lecture on Pivot Tables in excel they were developed very strong tools which are used 3 00:00:14,250 --> 00:00:20,140 to summarize large datasets there were tables automatically grouped together similar materials to give 4 00:00:20,140 --> 00:00:23,320 out a meaningful summary to understand how. 5 00:00:23,320 --> 00:00:29,620 Let us look at this funny tattoos his data for solid wood works company in the first column. 6 00:00:29,720 --> 00:00:38,990 We have salesperson name I'd give them a salesperson one to entry into one then in the product or name 7 00:00:39,620 --> 00:00:43,460 the next column contains these region in which the sale was made. 8 00:00:43,460 --> 00:00:48,900 Next is customer name then date then number of units bought by the customer. 9 00:00:49,040 --> 00:00:50,990 Then a day per unit cost. 10 00:00:50,990 --> 00:00:55,180 On the next one is total payment made by the customer. 11 00:00:55,220 --> 00:01:00,270 Now let us say we want to find out the sales made by each sales person. 12 00:01:00,350 --> 00:01:05,930 One way to do this is to apply for that one by one and some sales by each is person 13 00:01:12,710 --> 00:01:19,840 so we have applied a filter on salesperson 1 and the sum of said is about 2 million but doing this will 14 00:01:19,840 --> 00:01:23,580 be very time taking and cumbersome instead. 15 00:01:23,830 --> 00:01:29,970 Let's do this using the Red Devils so we go to the Insert menu click on Spirit devil 16 00:01:32,830 --> 00:01:41,290 in the first text but please ensure that the entire table area selected with excellent selected automatically 17 00:01:41,290 --> 00:01:44,320 foreach is next. 18 00:01:44,350 --> 00:01:46,690 If you want the pivot table in the same location. 19 00:01:46,750 --> 00:01:50,040 Select the single cute option I wanted and the new. 20 00:01:50,860 --> 00:01:51,590 So select. 21 00:01:51,680 --> 00:02:00,340 Okay you can see that a new sheet is added to the file on the right is the pivot table menu. 22 00:02:00,350 --> 00:02:09,350 Notice that it has all the column names listed below this list are for small boxes you can just drag 23 00:02:09,350 --> 00:02:14,980 and drop the desired column from above in the respective box together customize reports. 24 00:02:15,770 --> 00:02:21,330 If a drag drop salesperson in the rows all these sales personnel listed on the left. 25 00:02:22,130 --> 00:02:30,380 Similarly if I drag and drop the region name two columns all four regions will be listed in columns 26 00:02:32,290 --> 00:02:40,380 we can apply if it does by using different looks but no let us try to get the sales summary of every 27 00:02:40,380 --> 00:02:50,730 sales person we will drag and drop the sales person indeed rows box then we will bankrupt total sales 28 00:02:50,730 --> 00:02:52,470 value in the values book 29 00:02:57,520 --> 00:03:07,400 notice that this is showing some of total cost in the values box which means it has automatically added 30 00:03:07,400 --> 00:03:12,830 the sales value wherever salesperson or sales person 1 or sales president do. 31 00:03:12,830 --> 00:03:21,680 And so on next to identify which customer this is person has sold how much amount of furniture we will 32 00:03:21,680 --> 00:03:31,170 pick up customer name and we'll drop it into the columns box we can see each is person sale to each 33 00:03:31,170 --> 00:03:33,110 customer and the value of it. 34 00:03:33,720 --> 00:03:42,170 But if we want to see the count of sales that it is worth and has done we will go to the value box click 35 00:03:42,170 --> 00:03:46,020 on value field settings and instead of some we will select. 36 00:03:46,030 --> 00:03:48,920 Column on okay. 37 00:03:48,960 --> 00:04:01,690 You will see that salesperson 1 has made 17 sales to furniture and 11 to student Nicole overall as body 38 00:04:01,690 --> 00:04:10,670 count salesperson 3 has made 119 sales which is the largest sales to arrange this data that is to sort 39 00:04:10,670 --> 00:04:12,980 this data from largest to smallest. 40 00:04:13,040 --> 00:04:21,720 You go to the grand total column right click on any value and select sort largest to smallest you can 41 00:04:21,720 --> 00:04:28,230 see that 6 percent grade is on top with the maximum number of sales and salesperson 7 is at the bottom 42 00:04:28,410 --> 00:04:36,130 with the least number of sales there's one more box left which is difficult box if you want to play 43 00:04:36,130 --> 00:04:44,050 something that say for example if I want to figure that out some particular regions from my data I'll 44 00:04:44,050 --> 00:04:50,480 pick the regions box regions options and put in different box. 45 00:04:50,620 --> 00:04:53,100 You can see that it has come on the top left corner. 46 00:04:54,150 --> 00:05:00,130 If you click on this small triangle you see all the four regions that are data has. 47 00:05:00,180 --> 00:05:09,800 If you select Northeast only and click on okay these for salesperson and made these any sales in the 48 00:05:09,800 --> 00:05:11,420 northeast region. 49 00:05:11,420 --> 00:05:18,320 Similarly you can change notice to not less to see CDL the northwest northwest region. 50 00:05:18,320 --> 00:05:27,700 But if you want to select multiple values in this field to take on the checkbox and then select multiple 51 00:05:27,700 --> 00:05:33,780 values that you want to fill that so if we want northeast northwest anthologies. 52 00:05:33,830 --> 00:05:40,620 Take those and click on okay and you will find all these persons who have sold in these three regions. 53 00:05:46,960 --> 00:05:55,370 No instead of seeing this data from salesperson point of view if you want to see the amount of seed 54 00:05:55,490 --> 00:06:00,820 made every month we will remove salesperson from this table. 55 00:06:02,710 --> 00:06:07,420 And really bring the date column in tidy rows box 56 00:06:10,410 --> 00:06:11,160 we can see 57 00:06:15,390 --> 00:06:26,780 we can see that the date will group automatically by excel in two years quarters and dates if you want 58 00:06:26,780 --> 00:06:28,420 to change the grouping. 59 00:06:28,520 --> 00:06:38,660 You just go and click on it go to the group option and you until like years and quarters leave the month 60 00:06:38,660 --> 00:06:40,490 option and click on okay. 61 00:06:40,760 --> 00:06:46,660 You will now be able to see monthly sales for this furniture company next. 62 00:06:46,690 --> 00:06:56,010 If you want to match the total sales made to these two companies which is home space and hometown probably 63 00:06:56,010 --> 00:07:02,640 these two companies are most together and we want to grab these two sales values will select these two 64 00:07:03,130 --> 00:07:07,960 and right click group and rent. 65 00:07:08,070 --> 00:07:14,930 We take this minus but in see the combined sales to this group of two companies 66 00:07:18,870 --> 00:07:27,830 next year these laser options like sales are similar to those only the these are more convenient to 67 00:07:27,830 --> 00:07:37,540 a play so to a player as laser we go to the analyze option in the menu and in that we go to the inserts 68 00:07:37,590 --> 00:07:44,760 later option inserts later is giving us the option to select the column name on which we want to play 69 00:07:44,760 --> 00:07:45,390 this filter. 70 00:07:45,780 --> 00:07:54,760 So for example if I want to apply for a region with a convenient and click Okay so this laser deferred 71 00:07:54,910 --> 00:07:56,810 options which we are all too high. 72 00:07:57,280 --> 00:08:06,000 If you click on ne we are seeing this agent noticed regionally if you click on Northwest we are seeing 73 00:08:06,000 --> 00:08:07,870 this here in northwest regionally. 74 00:08:08,040 --> 00:08:14,010 If I want to select multiple regions I will click on this button and select northeast and northwest. 75 00:08:14,260 --> 00:08:19,960 But this gives us an easy way to play filters. 76 00:08:20,110 --> 00:08:21,830 That's all for this lecture. 77 00:08:22,020 --> 00:08:25,010 In the next lecture we will cover charts. 78 00:08:25,050 --> 00:08:25,410 Thanks.