WEBVTT

1
00:00:01.260 --> 00:00:08.010
William Rosener: Well, this is Dr bill welcome back and this instructional video you'll learn about pivot tables using Microsoft excel.

2
00:00:08.400 --> 00:00:16.049
William Rosener: And pivot tables just to summarize to sort to reorganize the group to count total were average data sorted in a table.

3
00:00:16.470 --> 00:00:25.410
William Rosener: allows us to transfer columns and rows and rows and columns allows grouping by any field or column using possibly advanced calculations on them.

4
00:00:25.830 --> 00:00:36.180
William Rosener: The pivot table is a tool and Microsoft excel that allows you to quickly summarize huge data sets with just a few clicks the start by taking a look at what you learn how to create and then we'll get started them.

5
00:00:36.750 --> 00:00:43.590
William Rosener: So in this case will be creating to pivot tables, the first one, or, if you take a look at the customer, or the store and the profit.

6
00:00:44.130 --> 00:00:51.000
William Rosener: And so I can use this to you, so if I wanted to maybe if I just wanted to compare two of these stores, I can use like.

7
00:00:51.540 --> 00:01:01.740
William Rosener: Just like those two stores cases and dollar general and these are take a look at a profit and i'll show you how we can easily change that rather look at the profit, we will look at the revenue.

8
00:01:02.460 --> 00:01:09.960
William Rosener: Then we're going to come on down here and that one that one was looking at kind of two values now we'll get to three will be looking at the customer.

9
00:01:10.470 --> 00:01:23.880
William Rosener: will be looking at the region and then also, in this case will be looking at the revenue, so in this case i'm looking at the south, if I wanted to I could easily just take a look at the North and there it is.

10
00:01:25.290 --> 00:01:35.280
William Rosener: So we're going to find is it's kind of the equivalent to sorting all this data and adding them up, but also asked me to change rows and columns and easily work with some.

11
00:01:36.360 --> 00:01:53.310
William Rosener: Very large datasets there, so another we get nowhere in do let's get started here so in the same area as you found a link to this video and you should also find this dataset i've got my assignment dash data, if you want to you can just call years assignment.

12
00:01:54.570 --> 00:02:01.350
William Rosener: Okay, and the very first of all we're going to do is just that we're going to create a pivot table and what's been looking at the customer and the profit.

13
00:02:01.890 --> 00:02:18.660
William Rosener: And to do insert that table is going to come right up here i'm going to press on insert until you or maybe i'll Double Click there so it's always available someone position my cursor i've got it here in she to i'm going to press right up here on insert i'm gonna choose pivot table.

14
00:02:20.100 --> 00:02:32.430
William Rosener: And I could actually highlight a one go way down the bottom, which is ugly there's 100 rose so dope another senderos so I can press either I can type A want colon he 100.

15
00:02:33.150 --> 00:02:41.280
William Rosener: They want a 100 and i'm going to have this insert into the existing worksheet I could put it into another tab accepted their books can have an insert right here.

16
00:02:43.140 --> 00:02:50.280
William Rosener: Okay, so in this case, what i'm gonna do is, we take a look here i've been placed and filters columns values and rules.

17
00:02:50.910 --> 00:03:09.120
William Rosener: And on this one, I was looking at the profit so i'm gonna drag the profit over here into the values and then i'm going to take the customer and put that into the Rose but if I wanted to watch again is simply a change, where I position that I want to change the rows and columns there.

18
00:03:10.290 --> 00:03:18.870
William Rosener: Okay, and it was that simple there, so in this case, I wanted to like I did earlier, I could take a look at just a couple of the stores.

19
00:03:20.400 --> 00:03:26.250
William Rosener: And he's the compare the profit on cases and dollar general there.

20
00:03:27.510 --> 00:03:33.480
William Rosener: If I was interested in taking a look at the revenue or I could do is I can actually delete this one.

21
00:03:34.500 --> 00:03:52.590
William Rosener: and drag a revenue down there, so now really take a look at the Prophet will get revenue and i'm gonna go ahead and get that back to her was some good and not drag profit right down there some take a look at the roses customer in the values is profit there.

22
00:03:53.760 --> 00:04:00.840
William Rosener: And again, I could easily add I can simply hit select all and get all the stores visible down.

23
00:04:01.920 --> 00:04:09.780
William Rosener: OK next one we're going to do is we're going to kind of be taking a look at the region customer and then, this one is going to take a look at Robin.

24
00:04:10.500 --> 00:04:21.960
William Rosener: So in this case i'm going to position by cursor here in G and I tell you what before I forget I need to mention that we're working with pivot tables it's going to create a pivot cash.

25
00:04:22.470 --> 00:04:27.090
William Rosener: And so what you're going to find is this one let's take a look at the prophets I take a look at cases.

26
00:04:27.690 --> 00:04:44.820
William Rosener: Typically, when I modify a value you're going to see that it's going to automatically updated this taking a look at a cash value so if I come along and start adding some more values here, increasing that notice it has not increased.

27
00:04:46.140 --> 00:04:57.600
William Rosener: Profit over here on this table over table, so if you want to do that you're going to have to do, simply create a new pivot table, which again was, like all of two seconds time so let's go ahead and.

28
00:04:58.800 --> 00:05:07.200
William Rosener: create another pivot table we come right up here at insert pivot table, and the same thing here on the type A one.

29
00:05:08.820 --> 00:05:10.680
William Rosener: On E 100.

30
00:05:12.030 --> 00:05:15.030
William Rosener: And again, an existing worksheet and press OK.

31
00:05:16.350 --> 00:05:30.540
William Rosener: So in this case again I want to use revenue, with the values and i'm going to use customer or the Rose and then i'm going to use a region or the columns.

32
00:05:31.620 --> 00:05:43.470
William Rosener: In about momentarily includes that and since i'm one take a look at what I was doing there, I was only looking at one, so if I just want to take a look, and quickly, isolate maybe there's some problem or something.

33
00:05:43.890 --> 00:05:52.380
William Rosener: I just want to look at in this case i'm looking at just the south, looking at these very stores i'm taking a look at their revenue.

34
00:05:53.160 --> 00:06:07.650
William Rosener: So if I wanted to I could take a look and change directions whoops change directions, I would come right up here and maybe I could take them and notice, I can actually have three of the regions so then i've got the north to south and the east and.

35
00:06:09.300 --> 00:06:22.290
William Rosener: You almost need to draw an imaginary line right here, because I can take a look it's kind of shifts need to love wanna, let me just take it to the post exercise good one let's go right back to us with them South there.

36
00:06:23.340 --> 00:06:24.600
William Rosener: So anyway.

37
00:06:25.620 --> 00:06:37.650
William Rosener: the beauty of these pivot tables, is it going to quickly logic to summarize data without this if I wanted to take a look at all the revenue or the profit for a particular store.

38
00:06:38.070 --> 00:06:46.950
William Rosener: I personally have had to sort this, then I would have had won't do a double sort first by on the customer, and then they also by the region.

39
00:06:47.400 --> 00:07:00.300
William Rosener: And I would have had to add them all up using the pivot table, you can see how easy it was to kind of took care of all the sorting in the swimming so the submission here was adding them all up for me as well here.

40
00:07:00.810 --> 00:07:13.020
William Rosener: So, in the case of cases I started adding up the profit supply start adding up all the profit or cases again part of the reason for using this larger data severe.

41
00:07:13.500 --> 00:07:21.270
William Rosener: Is because Okay, if you just got 10 numbers sometimes it's not that impressive, but in this case we've got 100 and then gets a little bit more impressive.

42
00:07:22.140 --> 00:07:29.190
William Rosener: Well, hopefully, you know this video you'll learn how easy it is to create a pivot table in Microsoft excel and how kitchen next video thanks bye.