Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,490 --> 00:00:10,200
We finished the last section of the course by putting our data into a table.
2
00:00:10,800 --> 00:00:15,330
And if you recall, we gave this table a name, we called it product sales.
3
00:00:16,170 --> 00:00:22,590
So now we're pretty much at the stage where we can put this data into a pivot table and start to analyze
4
00:00:22,590 --> 00:00:22,740
it.
5
00:00:22,980 --> 00:00:28,680
And there are a couple of different ways that you can construct a pivot table, so make sure that you're
6
00:00:28,680 --> 00:00:33,360
clicked somewhere in your data up to the Insert tab.
7
00:00:33,720 --> 00:00:40,530
And notice in the first group, the tables group, we have two options Pivot Table and recommended pivot
8
00:00:40,530 --> 00:00:41,070
tables.
9
00:00:42,150 --> 00:00:44,220
So what is the difference between these two?
10
00:00:45,000 --> 00:00:50,820
Well, if we hover mouse over recommended pivot tables and take a look at that screen, tap it says.
11
00:00:50,820 --> 00:00:54,870
Want us to recommend pivot tables that summarize your complex data?
12
00:00:55,380 --> 00:01:00,090
Click this button to get a customized set of pivot tables that we think will best suit your data.
13
00:01:00,660 --> 00:01:07,110
So that's exactly what this button is for when you click this Excel is going to basically look at your
14
00:01:07,110 --> 00:01:12,540
data and then provide you with a list of pivot tables that would best suit your data.
15
00:01:12,750 --> 00:01:17,040
And the one that is most appropriate for your type of data will be at the top of the list.
16
00:01:17,340 --> 00:01:23,400
So Excel has looked at this data, and it's basically showing me a pivot table that's going to display
17
00:01:23,400 --> 00:01:25,230
the sum of profit by country.
18
00:01:25,560 --> 00:01:30,480
So if that is the information that I'm interested in, looking at the profit broken down by country,
19
00:01:30,780 --> 00:01:34,830
I could simply click this pivot table and it will build it for me.
20
00:01:35,130 --> 00:01:40,530
The next one down is some of manufacturing price, so if that's what I want to see, I could click on
21
00:01:40,530 --> 00:01:41,460
that pivot table.
22
00:01:42,420 --> 00:01:44,730
What about some of profit by product?
23
00:01:44,760 --> 00:01:50,130
Well, again, if that's the one that I'm interested in, I could click to load up that pivot table.
24
00:01:50,760 --> 00:01:55,500
So these are simply recommendations from Excel based on your data.
25
00:01:55,740 --> 00:02:01,140
And this is a really nice, quick way of creating a pivot table without having to add the fields yourself.
26
00:02:01,440 --> 00:02:06,510
Now, we're not going to do it this way because I want you to learn how to build your own pivot tables.
27
00:02:06,960 --> 00:02:11,130
Once you know that if you want to use recommend pivot tables, then you absolutely can.
28
00:02:11,820 --> 00:02:13,530
So let's click on Cancel.
29
00:02:14,340 --> 00:02:18,600
What we're going to do is we're going to jump up to the pivot tables, but then we're going to create
30
00:02:18,600 --> 00:02:21,810
a pivot table based off of a table of data.
31
00:02:22,410 --> 00:02:25,170
So let's select from table slash range.
32
00:02:26,700 --> 00:02:28,470
So a couple of things we need to fill out here.
33
00:02:28,500 --> 00:02:32,010
First of all, we need to select the table that contains our data.
34
00:02:32,580 --> 00:02:37,890
Now, if you've already put your data into a table and given that table and name which we have done,
35
00:02:38,160 --> 00:02:40,470
then it should pick up your data.
36
00:02:41,040 --> 00:02:41,940
And I can see here.
37
00:02:41,940 --> 00:02:44,880
Yes, product sales is the data that it's picked up.
38
00:02:45,660 --> 00:02:50,370
If for some reason it doesn't pick up the correct range, you can also manually select the range.
39
00:02:50,670 --> 00:02:53,670
Now I need to choose where I want to put this pivot table.
40
00:02:54,240 --> 00:02:56,350
So do I want to put it on a new worksheet?
41
00:02:56,370 --> 00:02:59,100
Do I want to put it on an existing worksheet?
42
00:02:59,400 --> 00:03:05,310
So if I had more than one worksheet down here, I could choose which one I want to put this pivot table
43
00:03:05,310 --> 00:03:08,700
on, or do I want to add this data to the data model?
44
00:03:08,850 --> 00:03:14,310
Now we just want to put our pivot table onto a new worksheet, and I would definitely recommend that
45
00:03:14,310 --> 00:03:14,910
you do that.
46
00:03:15,450 --> 00:03:21,450
Always try and keep your source data on a different worksheet to any analysis that you're doing.
47
00:03:21,450 --> 00:03:24,990
So in general, I'll put my source data on one worksheet.
48
00:03:25,230 --> 00:03:26,820
My pivot tables on another.
49
00:03:27,030 --> 00:03:29,390
And then maybe calculations on another.
50
00:03:29,400 --> 00:03:30,600
So on and so forth.
51
00:03:30,600 --> 00:03:32,460
Just so everything is separated.
52
00:03:32,700 --> 00:03:34,980
So let's select new worksheets and click on.
53
00:03:34,980 --> 00:03:35,580
OK?
54
00:03:35,820 --> 00:03:38,340
So now you can see I have a new worksheet sheet to.
55
00:03:38,340 --> 00:03:42,960
I'm going to grab it and just move it to the other side of the pivot tables, worksheets and I have
56
00:03:42,960 --> 00:03:45,600
a blank pivot table reports at this stage.
57
00:03:45,870 --> 00:03:47,310
A couple of things to note here.
58
00:03:47,460 --> 00:03:49,920
We have a couple of new contextual ribbons.
59
00:03:50,550 --> 00:03:55,920
If we take a look, we have pivot table analyze and also a design ribbon as well.
60
00:03:56,490 --> 00:03:58,950
So both of these ribbons are where you're going to come.
61
00:03:58,950 --> 00:04:05,820
If you want to do things like apply formatting to your pivot table, add things like subtitles and grand
62
00:04:05,820 --> 00:04:11,130
totals, create pivot charts, insert slices, so on and so forth.
63
00:04:11,370 --> 00:04:17,460
And then the final thing to note here is the most important part of pivot tables, and that is the pivot
64
00:04:17,460 --> 00:04:20,130
table feels pain over on the right hand side.
65
00:04:20,850 --> 00:04:22,350
Notice in here.
66
00:04:22,410 --> 00:04:26,490
It's pulled in all of the column headings from our source data.
67
00:04:27,030 --> 00:04:30,870
And it's these that we're going to use to build our pivot table reports.
68
00:04:31,260 --> 00:04:34,710
Also note that currently our pivot table doesn't have a name.
69
00:04:34,710 --> 00:04:37,920
It's got a rather generic name of Pivot Table three.
70
00:04:38,460 --> 00:04:40,020
Now we're going to change that.
71
00:04:40,260 --> 00:04:44,490
Once we've completed our analysis and we know exactly what this table is showing.
72
00:04:44,880 --> 00:04:48,840
So let's move on to the next lesson we're going to start to build out this pivot table.
7325
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.