Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,620 --> 00:00:11,110
In this lesson, I want to show you how you can connect your slices to multiple pivot charts.
2
00:00:11,500 --> 00:00:17,830
So far, all of the slices that we've added, both the slices and the time line are just connected to
3
00:00:17,830 --> 00:00:23,530
one chart, and the chart that it's connected to is basically the chart that we have selected when we
4
00:00:23,530 --> 00:00:25,930
insert the slice or the timeline.
5
00:00:26,710 --> 00:00:32,260
So when I make any changes to this slice are up here, it's going to affect the chart that it's connected
6
00:00:32,260 --> 00:00:32,620
to.
7
00:00:33,460 --> 00:00:38,980
Now, as you start building charts and pivot tables and adding slices, you might get to a situation
8
00:00:38,980 --> 00:00:44,650
where you have multiple charts on a page and you want one slice to control all of them.
9
00:00:45,340 --> 00:00:49,690
And this is kind of where we start to cross into Excel dashboard territory.
10
00:00:50,350 --> 00:00:53,950
Now we do have a few lessons on how to build a dashboard coming up.
11
00:00:54,280 --> 00:00:58,090
But this is going to give you sort of a basic idea as to how this works.
12
00:00:58,780 --> 00:01:03,940
So what we're going to do is we're going to create a brand new worksheet, so let's click on the plus
13
00:01:03,940 --> 00:01:04,570
at the bottom.
14
00:01:04,810 --> 00:01:09,580
I'm going to right click, I'm going to name this, and we're just going to call this report connections.
15
00:01:10,900 --> 00:01:15,820
And what I'm going to do is I'm going to copy and paste all of the pivot charts that I've created so
16
00:01:15,820 --> 00:01:18,670
far onto this report connections worksheet.
17
00:01:19,360 --> 00:01:22,260
So let's go back to gross year by sales.
18
00:01:22,270 --> 00:01:26,680
Let's control see jump across to our page control fee.
19
00:01:26,860 --> 00:01:30,760
Notice that it doesn't bring across those slices because I didn't select them.
20
00:01:31,060 --> 00:01:34,030
Let's go to our pie chart.
21
00:01:34,300 --> 00:01:37,390
Control C Control V to paste.
22
00:01:37,600 --> 00:01:45,370
And then finally, let's go to our map chart control C and then Control V to paste, and you can reorder
23
00:01:45,370 --> 00:01:47,780
these and position them however you like.
24
00:01:47,800 --> 00:01:49,450
So let's just make this super wide.
25
00:01:49,840 --> 00:01:57,100
So now I effectively have three pivot charts on this worksheet, and I want to add a slice that controls
26
00:01:57,100 --> 00:01:57,580
them all.
27
00:01:58,450 --> 00:02:04,750
So what I'm going to do here is I'm going to select the first chart, go up to pivot chart, analyze
28
00:02:04,750 --> 00:02:10,120
and insert slicer, and I'm going to insert a slice for the year.
29
00:02:10,600 --> 00:02:11,460
Let's click on.
30
00:02:11,470 --> 00:02:12,070
OK?
31
00:02:12,940 --> 00:02:17,170
So now I have my little slice again, I can position that wherever I want it to be.
32
00:02:17,650 --> 00:02:22,810
So it might be that if this is going to be more of a universal slicer that controls all of these charts,
33
00:02:22,810 --> 00:02:24,520
I might want to put it across the top.
34
00:02:25,360 --> 00:02:27,790
So I'm going to do a little bit of formatting again here.
35
00:02:27,790 --> 00:02:29,800
Let's change the color of the slicer.
36
00:02:30,460 --> 00:02:38,500
Let's right click and go to slice the settings, and I'm going to not display the heading, and I'm
37
00:02:38,500 --> 00:02:41,380
also going to put this onto two columns.
38
00:02:43,030 --> 00:02:46,220
So I'm going to put that right at the top just here.
39
00:02:46,240 --> 00:02:49,000
Of course, you can remove the border as well if you want to.
40
00:02:49,270 --> 00:02:51,550
In fact, I have my custom slicer here.
41
00:02:51,560 --> 00:02:52,720
We might as well do that.
42
00:02:54,000 --> 00:02:58,740
So this is going to act as my universal slicer that controls everything on this page.
43
00:02:59,400 --> 00:03:03,870
And again, just to make this a little bit clean looking, I'm going to remove the grid lines.
44
00:03:04,830 --> 00:03:09,840
Now look what happens when I use this slicer if I click on 2019?
45
00:03:10,590 --> 00:03:13,470
The only data that updates is the data.
46
00:03:13,470 --> 00:03:19,710
In this first chart, nothing changes in the pie chart or in the map chart down below.
47
00:03:20,310 --> 00:03:24,900
If I click on 2018, again, notice nothing changes on that pie chart.
48
00:03:25,540 --> 00:03:30,810
And that is because this slice is only connected to this first chart.
49
00:03:31,320 --> 00:03:35,540
And that's because this was the chart that I was clicked on when I inserted the slicer.
50
00:03:36,300 --> 00:03:42,360
So if you want your slices to control multiple charts on your page, you need to link them to those
51
00:03:42,360 --> 00:03:42,840
charts.
52
00:03:43,470 --> 00:03:48,270
And this is where it becomes really important to make sure that your naming your pivot charts to make
53
00:03:48,270 --> 00:03:49,710
them easy to identify.
54
00:03:50,550 --> 00:03:58,290
So what you need to do here is click on the slicer, right click and then go into reports connections.
55
00:03:59,620 --> 00:04:04,510
And this is going to open up a little report connections box, and you can make that a little bit wider.
56
00:04:05,500 --> 00:04:09,940
And notice here we have all of the names of my pivot charts.
57
00:04:10,600 --> 00:04:15,820
If I hadn't of named my pivot charts, they're just going to have the generic name of Pivot Child one
58
00:04:15,820 --> 00:04:21,340
pivot chart to pivot chart three, which then makes it super hard to know which ones your connecting
59
00:04:21,340 --> 00:04:22,450
your slicer to.
60
00:04:22,750 --> 00:04:28,930
So I want this scissor to control all of them, and currently it's only controlling gross sales by year.
61
00:04:29,680 --> 00:04:33,910
So let's put a tick in the box next to all of these charts and click on OK.
62
00:04:34,240 --> 00:04:38,200
Now, keep your eye on the pie chart as I click on 2019.
63
00:04:39,190 --> 00:04:45,310
Notice that now those figures are updating, as are the figures in the map chart down below.
64
00:04:45,700 --> 00:04:50,260
So super important to make sure that you have your report connections set up correctly.
65
00:04:51,040 --> 00:04:55,960
Now, aside from right clicking to going to report connections, you can also get to that option on
66
00:04:55,960 --> 00:04:57,820
the slicer contextual ribbon.
67
00:04:58,090 --> 00:05:01,300
You have your report connections button just here as well.
7009
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.