Pivot Table Slicer

A Pivot Table Slicer is a tool in MS Excel that helps us filter the data further, and create a smaller PivotTable from the generated main PivotTable. The data can be presented based on various categories with the help of this slicer, as it offers a way to apply the PivotTable filters that dynamically change the view of the PivotTable data.

For example, we can create a Pivot Table Slicer after generating a Pivot Table using the “Insert → Slicer” for the required data, as shown below.

Following are the steps to create a PivotTable Slicer in excel: -

1. Select the data, go to the “Insert” tab, and click the “PivotTable” option.

   
   
   
   The “PivotTable fields” dialog displays the fields to be added to the resultant report, which is the dataset for the PivotTable Slicer. In addition, 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 shown for the selected column. Finally, the values field indicates the actual values to display, which the PivotTable is prepared for, e.g., SUM, PRODUCT, AVERAGE, etc.
   
   

2. Now, a PivotTable is created.

   
   

3. Once the PivotTable is ready, go to Insert → Filters tab → Slicer.

   
   

We will consider some PivotTable Slicer Examples.

This article must help understand Pivot Table Slicers with its formulas and examples. You can download the template here to use it instantly.

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

Here, the rows are for each country, and the columns are 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, a slicer is created in the category field with two product values: “Fruit” and “Vegetables”. Since we must display the fruit records, we will select “Fruit” from the slicer.

On the right side is the “PivotTable Fields” window. All those fields that must be considered while preparing the PivotTable are checked. E.g., quantity, product, country, and category. For example, “Apple/Banana/Mango” is the “Products”. Whereas “Fruit/Vegetable” is the “Category”.

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

The “Grand Total” column at the end displays the total sales figures for all the fruits for a given country. Likewise, the “Grand Total” row shows 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, it added the “Date” field to the “Fields” to add to the report. Likewise, 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: the product, country, and category. The “Fruit” is selected from the “Values” category. “Mango” and “Apple” are chosen from the “Product” options. “Australia,” “Canada,” and “France” are selected from “Country” values.

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

• It is used to create various views from 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 PivotTable and more granular data visualization in Excel are possible.
• A table slicer’s “Fields” or “Values” are multi-selectable.
• Further, we can analyze the data filtered using a slicer.