Customize Slicer Settings

Advanced Excel Dashboard Crash Course Section 2: 1st Dashboard using Pivot
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.30
List Price:  €129.22
You save:  €36.92
£79.14
List Price:  £110.81
You save:  £31.66
CA$135.92
List Price:  CA$190.30
You save:  CA$54.37
A$153.17
List Price:  A$214.44
You save:  A$61.27
S$134.71
List Price:  S$188.59
You save:  S$53.88
HK$782.26
List Price:  HK$1,095.19
You save:  HK$312.93
CHF 90.56
List Price:  CHF 126.79
You save:  CHF 36.22
NOK kr1,076.34
List Price:  NOK kr1,506.92
You save:  NOK kr430.58
DKK kr688.47
List Price:  DKK kr963.88
You save:  DKK kr275.41
NZ$166.49
List Price:  NZ$233.09
You save:  NZ$66.60
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳11,000.23
List Price:  ৳15,400.77
You save:  ৳4,400.53
₹8,335.05
List Price:  ₹11,669.40
You save:  ₹3,334.35
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦139,656.03
List Price:  ₦195,524.03
You save:  ₦55,868
₨27,872.43
List Price:  ₨39,022.53
You save:  ₨11,150.09
฿3,637.73
List Price:  ฿5,092.97
You save:  ฿1,455.24
₺3,222.80
List Price:  ₺4,512.05
You save:  ₺1,289.25
B$497.67
List Price:  B$696.75
You save:  B$199.08
R1,888.37
List Price:  R2,643.79
You save:  R755.42
Лв180.67
List Price:  Лв252.95
You save:  Лв72.27
₩134,942
List Price:  ₩188,924.20
You save:  ₩53,982.20
₪366.06
List Price:  ₪512.51
You save:  ₪146.44
₱5,625.18
List Price:  ₱7,875.48
You save:  ₱2,250.29
¥15,118.78
List Price:  ¥21,166.90
You save:  ¥6,048.12
MX$1,663.06
List Price:  MX$2,328.35
You save:  MX$665.29
QR365.44
List Price:  QR511.63
You save:  QR146.19
P1,373.02
List Price:  P1,922.28
You save:  P549.26
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,723.53
List Price:  E£6,613.14
You save:  E£1,889.60
ብር5,694.53
List Price:  ብር7,972.57
You save:  ብር2,278.04
Kz83,612.73
List Price:  Kz117,061.17
You save:  Kz33,448.44
CLP$98,024.19
List Price:  CLP$137,237.79
You save:  CLP$39,213.60
CN¥722.49
List Price:  CN¥1,011.52
You save:  CN¥289.02
RD$5,928.03
List Price:  RD$8,299.48
You save:  RD$2,371.45
DA13,469.95
List Price:  DA18,858.47
You save:  DA5,388.52
FJ$225.25
List Price:  FJ$315.36
You save:  FJ$90.11
Q781.84
List Price:  Q1,094.62
You save:  Q312.77
GY$20,987.69
List Price:  GY$29,383.60
You save:  GY$8,395.91
ISK kr13,817.61
List Price:  ISK kr19,345.21
You save:  ISK kr5,527.60
DH1,010.57
List Price:  DH1,414.84
You save:  DH404.26
L1,763.81
List Price:  L2,469.40
You save:  L705.59
ден5,677.01
List Price:  ден7,948.05
You save:  ден2,271.03
MOP$807.66
List Price:  MOP$1,130.76
You save:  MOP$323.09
N$1,895.98
List Price:  N$2,654.45
You save:  N$758.46
C$3,689.21
List Price:  C$5,165.04
You save:  C$1,475.83
रु13,357.88
List Price:  रु18,701.57
You save:  रु5,343.68
S/371.02
List Price:  S/519.45
You save:  S/148.42
K383.60
List Price:  K537.05
You save:  K153.45
SAR375.02
List Price:  SAR525.04
You save:  SAR150.02
ZK2,548.47
List Price:  ZK3,567.96
You save:  ZK1,019.49
L458.83
List Price:  L642.38
You save:  L183.55
Kč2,334.38
List Price:  Kč3,268.22
You save:  Kč933.84
Ft36,425.52
List Price:  Ft50,997.18
You save:  Ft14,571.66
SEK kr1,059.50
List Price:  SEK kr1,483.35
You save:  SEK kr423.84
ARS$85,666.43
List Price:  ARS$119,936.42
You save:  ARS$34,269.99
Bs695.09
List Price:  Bs973.16
You save:  Bs278.06
COP$389,673.99
List Price:  COP$545,559.18
You save:  COP$155,885.18
₡50,509.65
List Price:  ₡70,715.54
You save:  ₡20,205.88
L2,474.33
List Price:  L3,464.17
You save:  L989.83
₲736,638.54
List Price:  ₲1,031,323.43
You save:  ₲294,684.88
$U3,781
List Price:  $U5,293.55
You save:  $U1,512.55
zł398.17
List Price:  zł557.46
You save:  zł159.28
Already have an account? Log In

Transcript

Hello, everybody, welcome back to dashboard. In this video we will learn how to customize the slicer controls. So let's see. Now currently it the four slicers that we see on the screen were created from the first chart. What I want to happen is when I control one of the slicers, it should also control the rest. How does it work.

And when I click on Monday, this particular data is reflected onto the first pivot table that is created, which internally updates the chart. And that's what we are actually able to see here. That's because this particular slicer is only connected to the first pivot table. And as the data is collected from the same source, we can connect all three pivots with one slicer. I'll be doing this step for one but the rest I'll be doing at one go and then I'll be back. So I'll show you this on one of these.

So to do so first select one of the slicers right click, there's an option that says report connection Currently I'm using 2016 version, its report connection. Just remember that this is only available with 2010. And above versions in 2010, the option is pivot table connection. So once we click on it, we can see here, only one of the pivot tables is connected. This is the pivot one sheet, and we know that that's where the chart was prepared. So we just have to check the other three as well.

So I just click on to finalize the connection. I do remember before trying any of the connections to save your file just to prevent losing your work in case the file crashes or for some reason, terminates Excel. Let's see how this works. Now, if I click on Monday, the report that I'm seeing on the screen for the line the pie, and the stack chart is only for Monday. Same goes for Tuesday, Wednesday, Thursday. So you can see according to my selection, the chart reacts to my data.

Now let me complete this for the rest. as well and I'll get back to you. Welcome back. I'm done with all four slicers. So if I click on any of the parameters based on the selection, it automatically reflects the data accordingly. But as we can see, when I click on one of the parameters, there are some gray areas that appear.

Now those are areas which are not applicable say if I click on freshers now fresher only has experience of zero to one or 10 to 20 in whichever field they were working, and they might be switching. It's only available for Canada, you India, UK and the US. So I wish to filter these categories. And I only want to see data which is relevant to whatever selection I make. Now this is a slicer Ctrl. And I'll just clear the data first.

So to clear the data, we can use the clear option or the shortcut for the same as alt c do remember it has to be done selecting the slicers now to make the changes, simply right click on the slicer. And at the bottom slicer settings. Here there is a slicer Adding feature provided where we have an option hide items with no data. It's turned off, just turn it on. I click OK, that should internally set the data. If there's a slicer which does not have data stored in it, it won't show up.

Now let's try them. So if I do it for years, the zero and the 50 vanishes, I'll get back to you once I'm done with all of them. I'm done with all four slicers. Now let's see the output if I'm going from Monday, so on Monday for experience between one to 10 and 10 to 20. Now to have a control on the second option, I have to use a ctrl key is click and release the control key that in turn has the feature for me for Excel or maybe for fresher, so as we can see, it's only four years. So based on what I select only those filters are available, which are available for the relevant selection that I've made in all the rules.

Spective slices, if I want to clear I just have to click on the clear button and that will internally revert the relevant data. We can also slice if we are looking for a particular set that will also work easier and faster. So that is the first dashboard that we can create using the available tools in MS Excel. In the next video, we will see how to create the second dashboard till then keep practicing and I'll see you in the next video. Thank you

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.