What is a Pivot Table Slicer?

How to Create a Pivot Table Slicer in Excel?

Examples

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

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

  • Select the data and go to the “Insert” tab. Then, click on the “PivotTable.” 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. Now, a PivotTable is created. Once the PivotTable is ready, go to Insert -> Filters tab > Slicer.

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.

On the right side is the “PivotTable Fields” window. All those fields that must be considered while preparing the pivot table 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 tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more on the left contains column data for fruit, “Apple,” and “Mango” values. The rows are only for the country options selected.

Things to Remember

  • 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”/”Values” are multi-selectable.Further, we can perform any analysis on the data filtered using a slicer.

This article is a guide to PivotTable Slicer. Here, we discuss how to insert slicers in a PivotTable 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 TableDelete Excel Pivot TableTo delete a pivot table in Excel, you must first select it. Then go to the Analyze menu tab under the Design and Analyze menu tabs and select actions. Then, from the Select option’s drop-down option, select Entire Pivot Table to delete it.read moreLearn Pivot Table Conditional FormattingLearn Pivot Table Conditional FormattingTo use Conditional Formatting in a pivot table, first select the pivot and then, under the home tab, styles section, choose any of the Conditional Formatting options. Cells’ font, fill, and border colours can be changed using Conditional Formatting.read morePivot Chart in ExcelPivot Chart In ExcelIn Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends.read more