Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,750 --> 00:00:11,690
It's time now for us to do exercise six, where we're going to practice the skills that we've learned
2
00:00:11,690 --> 00:00:14,990
in this section relating to pivot tables.
3
00:00:15,230 --> 00:00:24,530
So currently we have our clean data set in an Excel table and that Excel table is called stool underscore
4
00:00:24,590 --> 00:00:25,190
sales.
5
00:00:25,460 --> 00:00:30,920
So now what I'd like you to do is to create a pivot table using this data.
6
00:00:31,100 --> 00:00:39,110
Once you've created the pivot table, I'd like you to add the category to columns, the town to rows
7
00:00:39,110 --> 00:00:46,730
and the sales to values, and I'd like you to rename the headings on the pivot table to make them meaningful.
8
00:00:46,970 --> 00:00:54,350
I'd like you to turn off grand totals and apply currency number formatting to all cells that contain
9
00:00:54,350 --> 00:00:57,260
values with zero decimal places.
10
00:00:57,500 --> 00:01:02,600
I then like you to apply a top five filter by sum of sales.
11
00:01:02,840 --> 00:01:08,960
And then finally, I'd like you to name the pivot table sales by town cats.
12
00:01:09,260 --> 00:01:14,420
So quite a bit of work to do here now is the time to pause the video and give it a go.
13
00:01:14,840 --> 00:01:19,100
If you get stuck or you won't see my answer, then please keep watching.
14
00:01:19,520 --> 00:01:22,820
So the first thing we're going to do here is we're going to create a pivot table.
15
00:01:23,420 --> 00:01:29,480
So as I'm already on the table design ribbon, I'm going to click on Summarize with Pivot Table.
16
00:01:29,870 --> 00:01:36,200
It's picked up my store sales range, and I want to add the pivot table to a new worksheet.
17
00:01:36,830 --> 00:01:37,610
Let's click on.
18
00:01:37,970 --> 00:01:45,830
OK, so now I get my pivot table fields over on the right hand side, and I asked you to add the category
19
00:01:46,340 --> 00:01:54,740
into the columns, the town into the rows and the sales into the values.
20
00:01:55,040 --> 00:02:00,410
The next thing we need to do here is we need to rename our pivot table labels just to make them a little
21
00:02:00,410 --> 00:02:01,430
bit more meaningful.
22
00:02:02,000 --> 00:02:08,990
So where we have row labels, I'm going to double click and I'm going to change this to towns where
23
00:02:08,990 --> 00:02:10,520
we have column labels.
24
00:02:10,520 --> 00:02:13,550
We're going to change this two categories.
25
00:02:13,880 --> 00:02:17,720
The next thing I asked you to do was to turn off grand totals.
26
00:02:18,200 --> 00:02:25,190
So I have a grand totals column and also if I go all the way down to the bottom are grand totals Ryan.
27
00:02:25,640 --> 00:02:28,430
And I want to turn these off for both of these.
28
00:02:29,210 --> 00:02:37,310
So up to the pivot table design ribbon into the layout group and we want to turn grand totals off four
29
00:02:37,310 --> 00:02:38,720
rows and columns.
30
00:02:39,050 --> 00:02:45,950
I then asked you to apply currency number formatting with zero decimal places to all of the values in
31
00:02:45,950 --> 00:02:46,790
the pivot table.
32
00:02:47,510 --> 00:02:49,640
Now you could do this column by column.
33
00:02:49,910 --> 00:02:57,380
I find it easier to select a cross section across all of the columns, right click and go into value
34
00:02:57,380 --> 00:02:58,310
field settings.
35
00:02:59,210 --> 00:03:00,530
No format.
36
00:03:00,650 --> 00:03:02,630
And we're going to choose currency.
37
00:03:03,080 --> 00:03:04,820
Take this down to zero.
38
00:03:05,240 --> 00:03:07,610
Click on OK and OK again.
39
00:03:07,850 --> 00:03:09,710
And it's going to change them all.
40
00:03:10,100 --> 00:03:16,550
Now, currently with the pivot table in this state, we have a lot of data and we want to create some
41
00:03:16,550 --> 00:03:21,170
pivot charts in the next session, and this is way too much data to put in a chart.
42
00:03:21,860 --> 00:03:27,530
So the next thing I asked you to do was to create a top five filter by sum of sales.
43
00:03:28,340 --> 00:03:31,760
So for this, we can right click and go to filter.
44
00:03:32,450 --> 00:03:39,710
I'm going to use the top 10 filter and then we're going to modify that to show the top five items by
45
00:03:39,710 --> 00:03:41,030
the sum of sales.
46
00:03:41,780 --> 00:03:43,070
Click on OK.
47
00:03:43,340 --> 00:03:47,690
And now I'm just seeing the highest selling categories and towns.
48
00:03:47,900 --> 00:03:51,560
And then the final thing I asked you to do was just to give this pivot table a name.
49
00:03:52,160 --> 00:03:54,820
So let's jump across to pivot table analyze.
50
00:03:54,830 --> 00:04:02,720
And in the pivot table name group, we're going to call this sales by town and cat.
51
00:04:03,740 --> 00:04:04,730
And hit enter.
5132
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.