Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,110 --> 00:00:12,250
In this exercise exercise aids, I'd like you to practice adding interaction to your pivot tables and
2
00:00:12,250 --> 00:00:13,180
pivot charts.
3
00:00:14,140 --> 00:00:22,330
So the first thing I'd like you to do is to add a slicer to filter by manager, and I'd like you to
4
00:00:22,330 --> 00:00:25,220
use a two column layout for that slicer.
5
00:00:25,450 --> 00:00:31,720
I'd also like to adjust the slicer settings so that you don't see the heading, and then I'd like to
6
00:00:31,720 --> 00:00:34,990
apply the le1 to slice the style dark.
7
00:00:35,050 --> 00:00:35,650
Six.
8
00:00:35,890 --> 00:00:41,200
And I'd also like you to customize the slice and just remove the border.
9
00:00:41,440 --> 00:00:47,210
Once we've done all that, I would then like you to add a different type of slice a time line slicer
10
00:00:47,260 --> 00:00:54,340
to filter by dates, and I'd like to customize that timeline so it matches the other slicer style.
11
00:00:54,520 --> 00:01:01,240
Once we've got our slices added, I then like you to practice updating your data, so you'll notice
12
00:01:01,240 --> 00:01:08,650
that we have a tab at the bottom called July underscore data, and I'd like you to add this data into
13
00:01:08,650 --> 00:01:10,600
the original data source.
14
00:01:10,810 --> 00:01:16,270
Once you've done that, I'd like you to come back to this worksheet and update the pivot table and the
15
00:01:16,270 --> 00:01:17,260
pivot charts.
16
00:01:18,220 --> 00:01:20,290
So again, quite a lot to do there.
17
00:01:20,320 --> 00:01:23,350
If you'd like to see my answer, then please keep watching.
18
00:01:23,710 --> 00:01:28,510
So the first thing I'm going to do here is we're going to add a slice to filter by manager.
19
00:01:29,200 --> 00:01:34,030
So I'm going to click inside my pivot table up to pivot table analyze.
20
00:01:34,030 --> 00:01:37,330
And in the filter group, we're going to insert a slice up.
21
00:01:37,510 --> 00:01:43,800
This is going to bring up all of the headings from a data source, and we want to filter by manager.
22
00:01:43,820 --> 00:01:46,510
So let's select that one click on.
23
00:01:46,660 --> 00:01:52,480
OK, now we have quite a few managers you'll see I have my scroll bar just there.
24
00:01:52,990 --> 00:01:59,230
Currently, I have these in a one column layout and I want you to change this to a two column layout.
25
00:01:59,380 --> 00:02:06,070
So on the slice, a ribbon in the Buttons Group currently where we have columns one, I'm going to change
26
00:02:06,070 --> 00:02:12,850
this to two and I'm going to drag this out a little bit so we can see those names and also maybe drag
27
00:02:12,850 --> 00:02:15,400
it all the way down to the bottom.
28
00:02:15,760 --> 00:02:19,060
So now we're going to do some customizing of this slicer.
29
00:02:19,150 --> 00:02:23,380
The first thing I asked you to do was not to display the slicer heading.
30
00:02:24,130 --> 00:02:30,130
So for this, we can right click and jump into slices settings or alternatively, we can go up to the
31
00:02:30,130 --> 00:02:34,390
slice of ribbon and choose slicer settings from there.
32
00:02:34,540 --> 00:02:40,900
And all we need to do here is select the display header option and click on OK.
33
00:02:41,140 --> 00:02:42,700
And that is now gone.
34
00:02:43,060 --> 00:02:49,540
I then asked you to apply the lavender slicer style Dark six, and if you didn't get this quite right,
35
00:02:49,540 --> 00:02:50,140
that's fine.
36
00:02:50,140 --> 00:02:52,330
It doesn't really matter which one of these you apply.
37
00:02:52,570 --> 00:02:54,910
But this is the one that I asked you to apply.
38
00:02:55,000 --> 00:03:01,060
So let's click to add it, and then I asked you to customize the slice and remove the border.
39
00:03:01,540 --> 00:03:06,040
So if I click away from this slide, see, you can see we have a border around the outside.
40
00:03:06,490 --> 00:03:09,550
We're going to customize this slice style.
41
00:03:10,390 --> 00:03:17,350
So for this, we need to jump up to slice slicer and you can't customize existing styles.
42
00:03:17,980 --> 00:03:23,950
So we need to right click and duplicate the style, first of all and give it a new name.
43
00:03:24,640 --> 00:03:33,190
So I'm going to call this my initials, custom slicer, and all we want to do here is remove that outside
44
00:03:33,190 --> 00:03:36,130
border and that applies to the whole slicer.
45
00:03:36,130 --> 00:03:38,500
So let's select it and click on format.
46
00:03:38,950 --> 00:03:45,550
And if we go to the Border tab, we can then select the none preset to remove all of those borders.
47
00:03:45,790 --> 00:03:51,490
Clicking OK, OK, again, now we need to apply our new slicer.
48
00:03:52,060 --> 00:03:53,080
Click the dropdown.
49
00:03:53,530 --> 00:03:55,700
You'll see it up here in the custom group.
50
00:03:55,720 --> 00:03:58,780
There it is, a custom click to apply.
51
00:03:58,990 --> 00:04:02,800
And if I click away, notice that border has now disappeared.
52
00:04:03,040 --> 00:04:07,690
Now I'm going to move this just down here underneath, and let's just check to make sure it works.
53
00:04:08,410 --> 00:04:14,350
If I click around on some of these, you can see that both my pivot table and my pivot chart are updating.
54
00:04:14,950 --> 00:04:19,180
Next, I asked you to add a time line slicer to filter by date.
55
00:04:19,330 --> 00:04:23,620
So once again, I'm going to click in my data up to pivot table analyze.
56
00:04:23,620 --> 00:04:26,210
And this time we're going to insert a timeline.
57
00:04:26,290 --> 00:04:31,120
We only have one field that applies to timelines and that is date field.
58
00:04:31,130 --> 00:04:33,310
So let's select it, then click on OK.
59
00:04:33,460 --> 00:04:39,280
And really, I just wanted you to customize this in the same way that we did for the other slicer.
60
00:04:39,940 --> 00:04:41,620
So I'm going to drag this along.
61
00:04:41,740 --> 00:04:45,310
Let's drag this up a bit so it fits into that space.
62
00:04:46,330 --> 00:04:54,250
And on the timeline ribbon, I'm going to remove the header and the selection labels and the time level
63
00:04:54,250 --> 00:04:54,880
as well.
64
00:04:55,540 --> 00:04:58,720
So now that kind of nicely fits into that gap.
65
00:04:59,050 --> 00:05:01,710
And what I'm also going to do is just make it the same color.
66
00:05:01,710 --> 00:05:04,450
So let's choose the same slice that.
67
00:05:04,600 --> 00:05:06,640
Style to apply to this.
68
00:05:07,030 --> 00:05:10,040
And the final thing to do here would be to remove that border.
69
00:05:10,040 --> 00:05:14,110
So once again, we need to right click duplicate the style.
70
00:05:14,740 --> 00:05:20,050
I'm going to call this DEA custom timeline.
71
00:05:21,190 --> 00:05:23,510
Whole timeline format.
72
00:05:24,040 --> 00:05:24,820
None.
73
00:05:25,300 --> 00:05:25,750
Click on.
74
00:05:25,750 --> 00:05:26,380
OK.
75
00:05:26,770 --> 00:05:27,820
OK, again.
76
00:05:28,120 --> 00:05:32,800
And then we can apply a custom style just to get rid of that border.
77
00:05:33,070 --> 00:05:38,380
Now, the final thing I asked you to add here now, the final thing I asked you to do in this exercise
78
00:05:38,380 --> 00:05:46,960
is add the dates from the July underscored data tab to the table and then update the pivot table and
79
00:05:46,960 --> 00:05:48,010
the pivot charts.
80
00:05:48,220 --> 00:05:50,380
So let's jump across to July data.
81
00:05:50,620 --> 00:05:57,910
Notice that this has exactly the same column headings, and I'm going to control a to select all control
82
00:05:57,970 --> 00:05:59,320
C to copy.
83
00:05:59,530 --> 00:06:06,700
Let's go back to our original data source control down Arrow to jump to that last row.
84
00:06:06,970 --> 00:06:10,070
And I can now control V to pace that data in.
85
00:06:10,270 --> 00:06:15,760
And if it brings across the column headings, don't worry, you can simply just right click and delete
86
00:06:15,760 --> 00:06:16,780
out that row.
87
00:06:17,740 --> 00:06:23,860
And because we put our data in a table, the table is now auto expanded to accommodate that new data.
88
00:06:24,430 --> 00:06:28,900
And this makes updating your pivot tables and your pivot charts super simple.
89
00:06:29,620 --> 00:06:31,780
So let's jump back to here.
90
00:06:32,320 --> 00:06:37,840
I'm going to click on my pivot chart, and all I need to do here is go to pivot chart analyze.
91
00:06:38,800 --> 00:06:42,550
And in the middle I have a refresh button and I'm going to choose to refresh.
92
00:06:42,720 --> 00:06:45,400
You'll notice that change.
93
00:06:45,400 --> 00:06:48,250
It's now, including that new data.
9239
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.