If you are using pivot table reports for the management or even for your own analysis and, you are not using slicer then, you are not fully utilizing the pivot table report.
In this discussion, I will discuss slicers in pivot table.
How to insert slicer, how to customize slicer, where and when you need to use
slicer.
We can use slicer both in Pivot tables and Tables.
But what is slicer?
 |
| Slicers in Excel |
It's very easy to add a Slicer in your pivot table. Let
understand this tool in this pivot table.
To insert slicer, go to the Analyze menu in the ribbon,
then, click this button Insert Slicer. A new small Insert Slicer window
appears.
If you notice carefully then you will find that all the
table headers are showing in this window with check boxes.
Let suppose, we choose Segment. A new small floating window
appears, and all the segments are showing in this window, like Channel
Partners, Enterprise, Government etc. These are all buttons. Now if you click
on any button, say for example, Channel Partners then all the relevant data
regarding Channel Partners will be shown and the rest data will be hidden.
Now, if you need enterprise, click Enterprise button the
data will show the related data. I hope you have understood the working of
slicer in pivot table.
 |
| Slicer Selection |
 |
| Slicer Selected Data in Pivot Table |
Let us check this drop-down list, it is showing something is selected from the entire list. Click on this you will note that Government segment is selected, and rest are deselected, therefore, slicers act like filters and it is very easy to handle.
If you need to display all the segments, then click on this
clear filter button. All the buttons will be selected again.
There is a small button on the left side of the filter
button, this is Multiselect button. If you want to use multiple buttons from
slicer, then use this button to activate multi select. Let us do it.
Click any one button first then click this multi select
button to activate, and then click on another button, both these buttons will
be selected.
Please remember, firstly you need to select one button from
all the slicer buttons then click on the multi-select button, otherwise this
will deselect the buttons you click.
Alternatively, you can use my method, I select any one of
the buttons then I hold press the control button and then select the next button,
like this. You can use the method which is comfortable for you.
You can use multiple slicers, more than one slicer. Select
any cell in pivot table, then go to Analyze menu and again click on the Insert
Slicer button. Insert slicer window will appear again. Select as per your need.
Here I will select Name.
For example, if you want to show the performance of Johnson,
click Johnson from the Name slicer, all the relevant data related to Johnson
will be displayed, from this if you want to show only Government segment data,
you only need to click on the Government button from the Segment slicer.
The pivot table shows the data of Johnson only for
Government business.
Let me show you a few more things which are very useful. I
have these excel sheets; these are copied from the same pivot table.
Now in this pivot table, we have already selected Johnson
and Government, but these sheets are not reflecting the changes made by slicers
in Sheet 1.
If you want to affect the changes in Sheet 2 or Sheet 3 or
Sheet 4, you must connect this slicer to other pivot tables. This is a very
easy process.
You have to instruct the slicer that these sheets are to be
linked by a connection.
Click on any slicer and you will find a new menu as Option
at the last of the menu items. Click on this button, Report Connection. A new
window, Report Connections and within bracket your slicer name. Here you will
see the list of your sheets along with the pivot table name, in our case pivot
table name is PivotTable1. Tick the checkbox of Sheet 2. Click OK. Now your
slicer relates to Sheet 2. Whatever selection you made in Sheet 1 slicer it
will display the same selection in Sheet 2.
Remember, it is not mandatory that all the pivot tables
should be of same structure to use slicer to control multiple tables. You can
design pivot tables as per your need, filters will be applied as per the
slicer.
Now I will discuss a few basic settings in slicer. You can
change the color of slicer from slicer styles, preformatted styles are available
in the Slicer Style group, choose any, the color will change.
If you need to show the slicer items in more than one
column, you can do it through Format slicer. Right click on the slicer and
select the second last option Size and Properties option. Format slicer will
open, from position and layout option you can increase the number of columns,
you can fix the position of the slicer and many more.
You can change the caption of the slicer, select any slicer
and click Slicer Settings from this button, or you can get it from the pop-up
menu after right click.
This is a very good tool in pivot table and Table. Please
use this tool in your pivot table report, it will increase the value of your
report and the navigation in the report will be easy.
I hope you have learned how to use slicer in pivot table,
please feel free to ask any question in the comment section. I will respond to
your question.
