Pivot Table Slicer (Table of Contents)
Introduction to Pivot Table Slicer
Slicer in Excel is a kind of filter which is used to filter the data available in the Pivot table as per the connections made between Slicer and Pivot Table. To apply Slicer in Pivot Table, first, we need to create a pivot table. Then from the Insert menu tab, click on the Slicer icon located under the Filter section. It will give us the list of all the fields therein the Pivot table. Select the fields which we want to see in Slicer. To connect the slicers with the pivot table choose the report connection from the right-click menu.
Definition of Pivot Table Slicer
Slicers in Excel is an interactive tool or visual filters that allow you to see what items are filtered within a Pivot Table. Pivot Table Slicer is most commonly used in dashboards and summary reports. Advantage of slicers over pivot table filter, it can be connected to multiple pivot tables and pivot charts. From excel 2013 version onwards, slicer tool can be applied to a data table, pivot table & charts.
How to Create a Pivot Table Slicer in Excel?
Let’s understand how to add or create a Slicer in excel using a Pivot Table with a few examples.
Example #1 – Sales Performance Report
With the help of Pivot table & Chart, let’s add Slicer object to summarize sales data for each representative & region. Below mentioned data contains a compilation of sales information by date, salesperson, and region.
Initially, data set is converted to a table object, which can be done by clicking inside the data set, click the Insert tab in home tab, select table, a create table popup appears, where it shows data range & headers, and click OK.
Once the table object is created, it appears as shown below.
For this tabular data, we need to summarize sales data for each representative by region wise & quarterly.
Therefore, we need to create two PivotTables.
First, we will create a pivot table, for the salesperson by region wise. In the Tables object, click inside the data set, click the INSERT tab, select the Pivot table and click Ok, Pivot Table Fields pane appears in another sheet. (You can name the sheet as “SALES_BY_REGION”)
In the PivotTable Fields pane, drag salesperson to the Rows section, Region to the Columns section, and sales to the Values section.
Similarly, create second PivotTable in the same way, To create a pivot table, for the salesperson by date wise or quarterly (SALES_BY_QUARTER).
Drag date to the Rows section, salesperson to the Columns section & sales to the Values section.
Here we want to summarize data on a quarterly basis, therefore dates need to be grouped as “Quarter”. To do that, right-click on any cell in the Row Labels column and choose Group.
Grouping tab appears, with the start date & end date, in the BY list, unselect Months (default value) and others, Now select only Quarters, it appears in blue color after selection. then click OK.
After grouping to quarter, data appears as shown below.
Here, we need to create a PivotChart on each of the created pivot tables in both the sheets.
Go to “SALES_BY_REGION” sheet, click inside the PivotTable, under PivotTable Analyze tab, select PivotChart, insert chart popup window appears, in that Select Bar, under that select Clustered Bar chart.
Pivot chart appears for “SALES_BY_REGION”
Here you can hide the region, salesperson & sum of sales in pivot chart by right-clicking, and select “Hide Legend Field Buttons on Chart”, so that those three fields will not appear on chart.
Similarly, pivot chart is applied in “SALES_BY_QUARTER” sheet also, where you can choose a Pie chart for a quarterly sales data. Here also you can hide those 3 fields.
To check the performance of individual salesperson by region wise & quarterly data we need to add slicers which will help you out, where you can filter out individual performance. Go to “SALES_BY_REGION” sheet, under analyze tab. Click Insert Slicer in the Filter group.
Insert slicers window appears, in that select Region field & click OK.
Region-wise Slicer will appear.
Once the region-wise slicer is inserted, the performance of individual salesperson by region wise can be filtered. In the below screenshot, I have selected the east region, where I can see the performance of individual salesperson in that region appearing in pivot chart & table.
Similarly, you can add slicers in “SALES_BY_QUARTER” sheet also, under PivotTable Analyze tab. Click on Insert Slicer in the Filter group. Insert Slicers window appears, in that select salesperson. click OK.
Sales Person Slicer will appear as shown below.
Once the salesperson wise slicer is inserted, the quarterly performance of individual salesperson can be filtered. In the below-shown screenshot, I have selected Chapman to check out his sales performance, where you can find changes in pivot table & chart, showing his performance quarterly.
Slicer can control both the pivot table and the pivot chart. i.e. Both the pivot table & chart updated once you change the salesperson. Multiple items in a slicer can be selected, i.e. by Clicking the slicer buttons, and simultaneously hold the Ctrl key, and then you can select multiple items.
Things to Remember
- In slicers, multiple Columns can be created. One slicer can be linked to multiple pivot tables & charts.
- When compared to the report filter, Slicer has better advantage & options where One slicer can be Linked to multiple pivot tables & charts, which helps out to cross-filter & prepare an interactive report using Excel.
- Slicers can be fully customized, where you can change its look, settings & color with the help of slicer tools options.
- You can Resize a slicer, with height & width options in slicer tools options.
- There is an option to Lock the slicer position in a worksheet, with the below-mentioned procedure.
Recommended Articles
This is a guide to Pivot Table Slicer. Here we discuss How to Add or Create Pivot Table Slicer in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
