1
00:00:08,040 --> 00:00:11,120
Hello, everyone, welcome to this lecture on Pivot Tables and Excel.

2
00:00:12,150 --> 00:00:15,660
They were they were very strong tools, which are used to summarize large datasets.

3
00:00:16,690 --> 00:00:21,160
They were automatically grouped together, similar wants to give out a meaningful summary.

4
00:00:22,340 --> 00:00:30,250
To understand how, let us look at this data for Solid Wood Works Company in the first column, we have

5
00:00:30,620 --> 00:00:31,600
salesperson name.

6
00:00:33,080 --> 00:00:35,840
I have kept them as a salesperson one, two and three and so on.

7
00:00:36,950 --> 00:00:43,910
Then, is the product sold in the next column contains region in which the sale was made next, his

8
00:00:43,910 --> 00:00:51,200
customer name the date, then number of units bought by the customer, then 80 per unit cost, and the

9
00:00:51,200 --> 00:00:54,330
next one is total payment made by the customer.

10
00:00:55,160 --> 00:00:59,240
Now, let us say, we want to find out the sales made by each salesperson.

11
00:01:00,270 --> 00:01:05,900
One way to do this is to apply filters one by one and some sales by each salesperson.

12
00:01:12,880 --> 00:01:19,480
So we have applied a filter on tests for someone and the sample said is about two million, but doing

13
00:01:19,480 --> 00:01:21,400
this will be very tempting and cumbersome.

14
00:01:22,690 --> 00:01:25,510
Instead, let's do this using the word devil's.

15
00:01:26,490 --> 00:01:29,400
So we go to the Internal Revenue on.

16
00:01:32,710 --> 00:01:34,210
In the first exper.

17
00:01:35,160 --> 00:01:38,070
Please ensure that the entire area selected.

18
00:01:39,130 --> 00:01:42,010
Which itself is self-inflicted automatically for Arkes.

19
00:01:43,360 --> 00:01:49,950
Next, if you want to pivot table in this chamber to select this option, I wanted in the new version,

20
00:01:50,720 --> 00:01:51,970
so OK.

21
00:01:54,020 --> 00:01:56,360
You can see that a new suit is added to the fine.

22
00:01:57,530 --> 00:02:04,400
On the right is the pivot table menu, you notice that it has all the column names listed below this

23
00:02:04,400 --> 00:02:06,590
list are for small boxes.

24
00:02:08,100 --> 00:02:13,790
You can just drag and drop the desired column from above in the respective walks together, customer

25
00:02:13,800 --> 00:02:21,290
support, if I drag drop salesperson in the rows, all the salesperson listed on the left.

26
00:02:22,070 --> 00:02:30,380
Similarly, if I drag and drop the region name two columns, all four regions will be listed in columns.

27
00:02:32,200 --> 00:02:35,050
We can apply filters by using the filter box.

28
00:02:36,720 --> 00:02:44,340
But now let's try to get the summary of every sales person, it will drag and drop the sales person.

29
00:02:45,800 --> 00:02:46,880
Indeed, horsebox.

30
00:02:48,120 --> 00:02:52,560
Then we will bankrupt auto sales value and devalues what?

31
00:02:57,440 --> 00:03:02,390
Notice that this is showing some of total cost.

32
00:03:03,590 --> 00:03:09,980
In the ballot box, which means it has automatically added the sales value, whatever sales person or

33
00:03:09,980 --> 00:03:18,710
sales person one also is supposed to do, and so on, next to identify which customer the salesperson

34
00:03:18,710 --> 00:03:25,510
has sold, how much amount of furniture you will pick up customer name and will drop it into the columns

35
00:03:25,520 --> 00:03:25,990
box.

36
00:03:27,990 --> 00:03:35,460
We can see each person sell to each customer and the value of it, but if we want to see the count of

37
00:03:35,580 --> 00:03:39,920
sales that its person has done, we will go to the value box.

38
00:03:41,870 --> 00:03:43,340
Click on the settings.

39
00:03:44,310 --> 00:03:46,440
And instead of some silicone.

40
00:03:48,100 --> 00:03:54,940
You're going to see that salesperson one has made 17 sales to furniture.

41
00:03:56,100 --> 00:03:58,980
And student Nicole overall.

42
00:04:01,070 --> 00:04:10,130
As Bodycount salesperson has made 119 sales, which is the largest sales to arrange this data, that

43
00:04:10,130 --> 00:04:15,410
is to sort this data from just the smallest, you go to the grand total column, right?

44
00:04:15,410 --> 00:04:20,330
Click on any value and select third largest to smallest.

45
00:04:21,390 --> 00:04:27,690
You can see that six percent is on top with the maximum number of sales and salesperson seven is at

46
00:04:27,690 --> 00:04:29,820
the bottom with the least number of sales.

47
00:04:31,630 --> 00:04:34,240
There's one more box left, which is defector's box.

48
00:04:35,140 --> 00:04:42,920
If you want to apply some filter, say, for example, if I want to filter out some particular regions

49
00:04:42,920 --> 00:04:48,790
from my data, I'll pick the regions box regions options and put in effect as what?

50
00:04:50,530 --> 00:04:53,010
You can see that it has come on the top left corner.

51
00:04:54,060 --> 00:05:01,700
If you click on this small triangle, you see all the four regions that are data has if you select NE

52
00:05:01,710 --> 00:05:04,020
only and click on OK.

53
00:05:05,090 --> 00:05:12,440
These four sales person have made these many sales in the northeast region, similarly, you can choose

54
00:05:12,440 --> 00:05:16,490
not to not to say see this sales in north, west, northwest region.

55
00:05:18,260 --> 00:05:23,750
But if you want to select multiple values in this filter economy, check box.

56
00:05:25,590 --> 00:05:29,430
And then select multiple values that you want to say that.

57
00:05:30,510 --> 00:05:37,350
So if we want northeast, northwestern, southeast, it does click on open and we will find all displaced

58
00:05:37,350 --> 00:05:40,590
persons who have sold in these three regions.

59
00:05:46,890 --> 00:05:47,310
No.

60
00:05:49,190 --> 00:05:55,650
Instead of seeing this data from his personal point of view, if you want to see the amount of made

61
00:05:55,910 --> 00:06:00,740
every month, we will remove salesperson from this table.

62
00:06:02,560 --> 00:06:04,170
And will bring the date.

63
00:06:05,290 --> 00:06:07,420
Column deros, blogs.

64
00:06:10,370 --> 00:06:11,100
Can see.

65
00:06:15,330 --> 00:06:24,990
We can see that the debt will grow automatically by itself in two years, quarters and debt.

66
00:06:26,210 --> 00:06:32,360
If you want to change the grouping, you just go and click on it, go to the group option.

67
00:06:33,700 --> 00:06:36,310
And you unsullied years and quarters.

68
00:06:38,210 --> 00:06:44,450
Demands action, and you will now be able to see monthly sales for this furniture company.

69
00:06:46,090 --> 00:06:53,740
Next, if you want to watch the Kardashians, why do these two companies it is home, space and hometown.

70
00:06:55,620 --> 00:07:02,130
Probably these two companies are much together and we want to group these two sales values will select

71
00:07:02,130 --> 00:07:05,130
these two and likely group.

72
00:07:07,250 --> 00:07:14,930
And when we take this minus button, you'll see the combined sales to this group of two companies.

73
00:07:18,780 --> 00:07:20,670
Next year, these laser options.

74
00:07:21,840 --> 00:07:24,450
Slices are similar to figures.

75
00:07:25,600 --> 00:07:28,210
Only these are more convenient to play.

76
00:07:29,310 --> 00:07:37,920
So to apply it, we go to the analyst option in the menu and in that we go to the interpolated later

77
00:07:37,920 --> 00:07:38,250
option.

78
00:07:39,660 --> 00:07:45,370
Uncirculated is giving us the option to select the column name on which we want to apply this filter.

79
00:07:45,720 --> 00:07:51,450
So, for example, if I want to apply for the region to region and click, OK.

80
00:07:53,050 --> 00:07:58,870
So this laser, the four options which are favorable to her, if you click on NE.

81
00:07:59,910 --> 00:08:02,040
We are seeing this in the northeast region only.

82
00:08:03,540 --> 00:08:09,930
If you click on Notwist, you seeing this in the not particularly if I want to select multiple regions,

83
00:08:10,080 --> 00:08:11,300
I'll click on this button.

84
00:08:12,370 --> 00:08:14,590
And to the northeast and northwest both.

85
00:08:16,650 --> 00:08:22,860
This gives us an easy way to play filters, that's all for this lecture in the next lecture.

86
00:08:22,950 --> 00:08:24,510
We will cover charge.

87
00:08:24,960 --> 00:08:25,380
Thanks.