Pivot Table Slicer (Table of Contents)
Introduction to 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. The advantage of slicers over pivot table filter can be connected to multiple pivot tables and pivot charts. From excel 2013 version onwards, the slicer tool can be applied to a data table, pivot table & charts.
Slicer in Excel is a kind of filter that is used to filter the data available in the Pivot table as per the connections made between the 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.
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 the Pivot table & Chart, let’s add a 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, the data set is converted to a table object, which can be done by clicking inside the data set, click the Insert tab in the Home tab, select the 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.
We need to summarize sales data for each representative by region wise & quarterly for this tabular data.
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 a 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 sheets.
Go to the “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 the pivot chart by right-clicking and select “Hide Legend Field Buttons on Chart” so that those three fields will not appear on the chart.
Similarly, a pivot chart is applied in the “SALES_BY_QUARTER” sheet also, where you can choose a Pie chart for quarterly sales data. Here also, you can hide those 3 fields.
To check individual salesperson performance by region wise & quarterly data, we need to add slicers that will help you out, where you can filter out individual performance. Go to the “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 region-wise can be filtered. In the below screenshot, I have selected the east region to see individual salesperson’s performance in that region appearing in the pivot chart & table.
Similarly, you can add slicers in the “SALES_BY_QUARTER” sheet under the 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 the 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 the pivot table & chart, showing his performance quarterly.
The 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 advantages & 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 their 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 a downloadable excel template. You can also go through our other suggested articles –
13 Online Courses | 100+ Hours | Verifiable Certificates | Lifetime Validity
4.5
View Course
Related Courses
