EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Pivot Table Slicer
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Pivot Table Slicer

By Pradeep SPradeep S

Pivot Table Slicer

Pivot Table Slicer (Table of Contents)

  • Definition of Pivot Table Slicer
  • How to Create a Slicer in Excel?

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.

Note: Prior to the addition of slicer in raw data, you need to find and remove duplicate values, delete leading, trailing or double spaces & remove blanks and errors in the raw data file.

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.

You can download this Pivot Table Slicer Excel Template here – Pivot Table Slicer Excel Template

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.

pivot table slicer 1-1

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.

Pivot table slicer 1-2

Once the table object is created, it appears as shown below.

pivot table slicer 1-3

We need to summarize sales data for each representative by region wise & quarterly for this tabular data.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,368 ratings)

pivot table slicer 2-1

Therefore, we need to create two PivotTables.

pivot table slicer 2-2

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.

pivot table slicer 2-3

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.

pivot table slicer 2-4

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.

pivot table slicer 2-5

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.

pivot table slicer 2-6

After grouping to quarter, data appears as shown below.

pivot table slicer 2-7

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.

PivotTable Analyze

Pivot chart appears for “SALES_BY_REGION.”

PIVOT CHART 3-2

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.

PIVOT CHART 3-3

PIVOT CHART 3-4

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.

PIVOT CHART 3-5

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.

Addition of Slicers 4-1

Insert slicers window appears, in that select Region field & click OK.

Addition of Slicers 4-2

Region-wise, Slicer will appear.Addition of Slicers 4-3

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.

Addition of Slicers 4-4

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.

Addition of Slicers 4-5

Sales Person Slicer will appear as shown below.

Addition of Slicers 4-6

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.

Addition of Slicers 4-7

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.

Note: If the slicer box gets hidden behind the chart, right-click the slicer, and select Bring forward from the slicer tools options.

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 –

  1. PowerPivot in Excel
  2. VBA Refresh Pivot Table
  3. Pivot Table Formula in Excel
  4. Slicer in Excel
Popular Course in this category
MS Excel Training Bundle
  13 Online Courses |  100+ Hours |  Verifiable Certificates |  Lifetime Validity
4.5
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Training (23 Courses, 9+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Already registered !

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Pivot Table Slicer Excel Template

EDUCBA

Download Pivot Table Slicer Excel Template

EDUCBA

डाउनलोड Pivot Table Slicer Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more