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