Pivot Table Slicers in Excel

Slicers are the visual representation of filters. By using a slicer, we can filter our data in the pivot table by just clicking on the type of data we want. Slicers are found in the Analyze tab of the pivot table tools. We have an option called Insert Slicer and on clicking it, we have to select the column on the basis of which we need to filter our data.

Pivot Table Slicers are Visual controls that help users filter data in a Pivot Table or Pivot Chart. They act as a user interface element. Slicers are useful when you have multiple fields(columns) in the pivot table and want to control the data displayed by selecting specific criteria.

Follow the below steps to insert a Pivot table slicer:

Step 1: Create a Pivot table

Select the data you want to analyze and then create a Pivot table.

Step 2: Design the Pivot Table

Design the Pivot table by dragging the relevant fields to the “Rows”, “Columns”, “Values”, or “Filters” area of the PivotTable Fields pane.

Step 3: Insert a Slicer

When your Pivot Table is ready, go to the “Pivot Table Analyze” or “Options” tab in the Excel ribbon. Click on “Insert Slicer”, and a dialog box will appear, with all the fields available in the Pivot Table.

Step 4: Choose a Field for the Slicer

Select the field you want to use as a slicer and click “OK”.

Here, the working of a slicer will be demonstrated by taking an example of a pivot table. Suppose we have an IMDb dataset of all the movies released in 2016 worldwide with their ratings, budget, gross revenue, country, genre, and many more things.

Suppose we want to know the budget of all the movies by country filtered by genre. For eg: We want to know the budget of all the movies in the U.S.A and the U.K. which are of the comedy genre.

We can do this in two ways:

• By dragging the genre into the filter box in the field list of the pivot table

• Secondly, we can do the same thing by using slicers. 

So, we can see that we obtain the same results from both ways. So we can say that slicers work exactly the same way as dragging a field into a filter box in the field list of the pivot table, and they add a user-friendly interface to filter the data.

To select multiple genres, we need to enable the option of Multi-select in a slicer or just press shift and select any other genre. For eg: The picture below shows the total budget in the U.S.A and U.K. for both comedy and crime genres. 

When we inserted a slicer, a new menu called slicer tools is created and within it, we have several options through which we can format our slicers.

If we have many items in our slicer, then we can increase the number of columns to make our slicer wide and give a better representation.

We can also add additional formatting to our slicers by going to the slicer settings option. In that, we can choose whether we want to display the header of our slicer or not, sort our items in the slicer ascending or descending, etc.

Usually, After inserting the Pivot Table Slicers, you might need to move and resize them to ensure they are well-placed and easy to use. Excel offers various customization options to format the Slicers according to your preferences.

Move and Resize Slicers

Moving a Slicers:

To move a Slicer to a new location on the same worksheet, follow these steps:

Step 1: Point to the Slicer’s border or any empty part of the Slicer.

Step 2: When the pointer changes to a 4-headed arrow, drag the slicers to their new position.

Resizing a Slicer:

Follow the steps to resize a Slicer to make it larger or smaller:

Step 1: Click on the Slicer o select it.

Step 2: Point to one of the sizing handles on its border.

Step 3: When the Pointer changes to a 2-headed arrow, drag inwards or outwards to adjust the Slicers’ size.

Change Slicer Style:

Excel has some built-in styles for slicers, similar to those available for Piovt tables. Follow the steps to quickly format a slicer with a different style:

Step 1: Click on an empty part of the Slicer to select it.

Step 2: On the Excel Ribbon, go to the “Slicer” tab.

Step 3: In the “Slicer Styles” group, click on one of the visible styles to apply it to the Slicer.

Change Slicer Settings:

Excel allows you to modify certain Slicer settings after adding them to a Pivot Table. Below are some settings that can be changed:

Sort Order

For Slicers displaying dates, you may want to see

Step 1: Right-click on the Slicer and click “Slicer Settings”.

Step 2: In the “Item Sorting and Filtering” section, select “Ascending ” or “Descending” as per your preference.

Step 3: Click “Ok” to apply changes.

Change Slicer Captions:

The Slicer caption shows the field names in the Pivot Table. Follow the below steps to change the Slicer Caption:

Step 1: Right-click on the Slicer and Click “Slicer Settings”.

Step 2: In the “Header” Section, type the desired caption text to replace the existing caption.

Step 3: Click “ok” to save the changes.

Multiple Slicers

We can insert multiple slicers if we want to filter our data based on two columns. Each of the slicers tells the pivot table what sub-set of data to use for calculating the numbers.

For eg: In our example of the IMDb movies database that we took above, if we want to filter our data on the basis of genre and language. i.e. If we want to calculate the budget of all the Hindi movies of comedy genre in the U.S.A and U.K, then we insert two slicers, one on the basis of genre, the other on the basis of language.

So from the picture above, we can see that there are no Hindi comedy films in the U.K.

We can link our slicer to multiple pivot tables which can build a very interactive dashboard. To do so follow the below steps:

Step 1: Right-click on the slicer and select the option of Report Connections.

Step 2: After that, you will see a menu like this.

Step 3: Now, select all the table names on which you want your slicer to work.

Suppose in our example of the IMDb movies database, I want both the budget and gross revenue to be displayed for all the comedy films in U.S.A and U.K.

So here our slicer works on both the pivot tables.

Below are some Advantages of using Pivot Table Slicers:

User-Friendly Data Exploration

Slicers provide a user-friendly and intuitive way to explore and analyze data in a Pivot Table. This helps the user to quickly filter data without understanding complex filtering mechanisms.

Interactive Data Analysis

Users can change their selections and the pivot table updates in real-time, providing immediate insights into the data.

Enhanced Visual Clarity

Slicers add a visual element to pivot tables. It makes it easier to comprehend applied data filtering.

Consistency Across Pivot Table

Slicers maintain a consistent look and feel across different Pivot tables in the workbook, providing a standardized user experience.

Easy to Use and Customize

Inserting and Customizing slicers is simple and requires no special training. Slicer’s styles, sizes, and layouts can be changed to match your preferred design.

What are Pivot Tables Slicers in Excel?

Pivot Table Slicers are visual controls in Excel that allow users to filter data within a Pivot table easily. They provide an intuitive user interface for data filtering, making it simpler for users to analyze and explore data dynamically.

How to insert a Pivot Table Slicer in Excel?

Follow the below steps to insert a Pivot Table Slicer:

Step 1: Create a Pivot table by selecting the data you want to analyze.

Step 2: Go to the “Insert” tab in the Excel Ribbon.

Step 3: Click on “Pivot Table” and choose the location for the pivot table.

Step 4: Design the Pivot table by adding fields to the “Rows”, “Columns”, “Values”, or “Filters”, area in the PivotTable Fields pane.

Step 5: Go to the “PivotTable Analyze” or “Options” tab in the Excel Ribbon.

Step 6: Click on “InsertSlicer”, choose the field you want to use for filtering, and click “Ok”.

How do Pivot Table Slicers work?

Pivot Table Slicers work as interactive buttons that allow users to filter data in a Pivot table by selecting specific criteria. When a user clicks on an item in the slicers, the Pivot table updates to display only the data that matches the selected criteria.

What are the limitations to using Pivot Tables Slicers in Excel?

The Limitation of Pivot Table Slicers is that it only works with Pivot tables and pivot charts, so they cannot be used with regular data tables. Additionally, Slicers may have some performance impacts, especially when dealing with very large datasets or complex pivot tables.