Slicers

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 is a tool which provides buttons, that you can click to filter tables, or PivotTables. Slicers are a powerful new way to filter pivot table data.

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.