WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Pivot Table Slicer

Pivot Table Slicer

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What is a Pivot Table Slicer?

Pivot Table Slicer is a tool in MS Excel. The purpose of Slicer in excel is to filter the data present in a pivot table. The data can be presented based on various categories with the help of this slicer as it offers a way to apply the filters on the pivot table that dynamically changes the view of the pivot table data.

How to Create a Pivot Table Slicer in Excel?

  • Step 1: Select the Data and Go to the insert tab; then, click on the Pivot Table.

Pivot Table Slicer Example 1-1

  • PivotTable fields dialog displays the fields to be added to the resultant report, i.e., the dataset for the pivot table slicer. The filter section has a column that acts as a table slicer.
  • The columns field denotes the columns to be displayed. The rows section has the table field whose data should be displayed for the selected column. Values field displays the actual values to display, which the pivot table is prepared for, e.g., SUM, PRODUCT, AVERAGE, etc.

Pivot Table Slicer Example 1-2

  • Step 2: Now, a Pivot Table is Created.

Pivot Table Slicer Example 1-3

  • Step 3: Once the Pivot Table is ready, Goto Insert -> Filters tab > Slicer.

Pivot Table Slicer Example 1-4

Examples

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

Example #1 – Display Fruit-wise Sales for Each Country

Pivot Table Slicer Example 1-5

Here, the rows would be for each country, and the columns will be for each of the fruits as we need to display the fruit-wise sales figures for each country. Hence, as shown in the figure above, Slicer is created in the Category field, which has 2 Product values, Fruit and Vegetables. Since we have to display the Fruits records, we will select Fruit from the slicer.

Pivot Table Slicer Example 1-6

On the right side, PivotTableFields window, all those fields needed to be considered while preparing the pivot table are checked. E.g., Quantity, Product, Country, and Category. Here, Apple/Banana/Mango is the Products, whereas Fruit/Vegetable are the Categories.

Filters field has the column selected as Category as we have to choose between Fruit or Vegetable. Rows section has Country selected, and Columns section has Product. The Values section has Sum of Quantity selected as we have to display the total sales for each fruit across the countries.

The Grand Total Column at the last displays the total sales figures of all the Fruits for a given Country. The Grand Total Row at the last displays the Total sales of a given Fruit for all the Countries.

Example 1-7

Example #2 – Monthly Fruit Sales for Each Country

Example 1-8

This extension is explained in Example #1 above. As shown in the above figure, the Date field was added to the fields to add to the report. The rows sections add a date field, as the sales figures for each month are displayed.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example #3 – Multiple Slicers

Example 2-10

As illustrated in the above figure. Multiple slicers are used; those are the product, country, and category. The fruit is selected from the value category. Mango and Apple are selected from Product options. Australia, Canada, France are chosen from country values.

Consequently, the data displayed in the pivot table on the left contains column data for the values of fruit, i.e., Apple and Mango. The rows are only for the country options selected.

Things to Remember

  1. It is used to create various views out of PivotTable in Excel based on the filters and visualizes the dataset in the desired format.
  2. For PivotTable data, there may be one or more table slicers. Ideally, more slicers in the pivot table, more granular data visualization in Excel is possible.
  3. Fields/ Values in a Table Slicer are multi-selectable, as shown in the figure below.
  4. Further, any kind of analysis can be performed on the data filtered using Pivot Slicer.

Recommended Articles

This has been a guide to Pivot Table Slicer. Here we discuss how to insert slicers in a Pivot Table in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • How to Merge 2 Tables in Excel?
  • Delete Excel Pivot Table
  • Learn Pivot Table Conditional Formatting
  • Pivot Chart in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Pivot Table Slicer Excel template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More