Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
Before the 2010th version of excel we used many filters in pivot tables manually for data reports and visualization, but in 2010 and older versions excel introduced slicer in pivot tables which are used to use filters in pivot tables and we can use one or more than one filters in pivot tables at a single click, to insert a slicer we need to go on insert tab and then on slicer in the filters section.
Pivot Table Slicer (Table of Contents)
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.
Any kind of analysis can then be performed over such filtered data. E.g. Gross Sales, Region-wise Sales, Sales of category X, category Y and so on.
How to Create a Pivot Table Slicer in Excel?
- Create a Pivot Table on the dataset.
- Select the Data and Go to the insert tab then, click on the Pivot Table.
- PivotTable fields dialog displays the fields to be added to the resultant report i.e. the dataset for the pivot table slicer. Filters section has the column that acts as a table slicer.
- Columns field denote the columns to be displayed. 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.
- Now a Pivot Table is Created.
- Once the PivotTable is ready, Goto Insert -> Filters tab > Slicer.
Example #1 – Display Fruit-wise Sales for Each Country
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 on Category field which has 2 Product values, Fruit and Vegetables. Since we have to display the Fruits records, will select Fruit from the slicer.
On the right side, PivotTableFields window, all those fields to needed to be considered while preparing the pivot table are checked. E.g. Quantity, Product, Country and Category. Here, Apple/Banana/Mango are 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 #2 – Monthly Fruit Sales for Each Country
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.
Example #3 – Multiple Slicers
As illustrated in the above figure. Multiple slicers are used those are the product, country, and category. 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 About Pivot Table Slicer in Excel
- It is used to create various views out of PivotTable in Excel based on the filters and visualize the dataset in the desired format.
- 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.
- Fields/ Values in a Table Slicer are multi-selectable as shown in the figure below.
- Further, any kind of analysis can be performed on the data filtered using Pivot Slicer.
You can download these Pivot Table Slicer Excel template here – Pivot Table Slicer Excel Template
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 downloadable excel template. You may learn more about excel from the following articles –