Skip to main content

Excel slicers: Filter data in PivotTable or Excel Table

Slicers in Excel are visual filters that allow you to quickly and easily filter the data in PivotTables, Excel Tables, or Pivot Charts. Unlike traditional filters, slicers display all available filter options and how those options are currently filtered, making it easier to understand the state of your data. This guide demonstrates how to insert slicers. Additionally, we will delve into advanced applications like designing a custom slicer style and linking a single slicer to several PivotTables, among other sophisticated functionalities.

What are Slicers in Excel?

How to add and use slicer in Excel?

How to connect slicer to multiple PivotTables / Pivot Charts?

Format slicers in Excel

Disconnect a slicer

Delete a slicer

Benefits of using slicers in Excel

What are Slicers in Excel?

Slicers in Excel are graphical filtering tools that allow you to quickly and efficiently filter the data in PivotTables, Pivot Charts, and Excel Tables. They provide a user-friendly way to display and manage the filters applied to your data. Unlike traditional filters that are hidden in drop-down menus, slicers are displayed as buttons on the spreadsheet, making it easy to see the current filtering state and to filter by just clicking on the desired filter option.

Slicers first appeared in Excel 2010 and have been a feature in subsequent versions, including Excel 2013 through Excel 365.

A slicer is usually composed of the following elements:

  1. Header: The top part of the slicer shows the name of the field or category being filtered, helping users identify what data the slicer controls.
  2. Filter Buttons: These are the main elements of the slicer, representing the unique items or values in the selected field. Clicking a button filters the data to show only the records related to that value.
  3. Multi-Select Button: This button lets you choose more than one filter option at a time.
  4. Clear Filter Button: This button allows users to clear the selection and remove the filter applied by the slicer, showing all the data again.
  5. Scroll Bar: If the slicer contains more items than can be displayed at once, a scroll bar appears on the side or bottom, allowing the user to scroll through all available filter options.
  6. Border moving and resizing controls allow you to adjust the size and position of the slicer.

How to add and use slicer in Excel?

Adding and using slicers in Excel enhances the interactivity of your data analysis, allowing you to filter PivotTables, Excel Tables, and Pivot Charts with ease. Here’s how you can add and use slicers in different contexts:

Add and use slicer in PivotTable

  1. First, create a PivotTable as you need. Then, click anywhere in the PivotTable.
  2. In Excel 2021 and 365, go to the PivotTable Analyze tab, and then click Insert Slicer. See screenshot:
    Tips: In other Excel versions, please do as this:
    In Excel 2013-2019, under the Analyze tab, and then click Insert Slicer.
    In Excel 2010, switch to the Options tab, and click Insert Slicer.
  3. In the Insert Slicers dialog box, select the checkbox next to each field you want to add a slicer for, then click OK.
  4. The slicers for the selected fields are created. Click on any of the slicer buttons to filter the data in the PivotTable. In this example, I will filter for Drink from Australia, France, or the United States. See screenshot:
Tips:
  • To filter by multiple items, either hold down the Ctrl key and click on the items you want to filter, or click the Multi-Select button to enable multiple selection.
  • To reset or clear the filters in a slicer, click on the Clear Filter button in the slicer.
 

Insert and use slicer in Excel table

Inserting and using a slicer in an Excel table can significantly enhance the data interaction and analysis experience. Here’s how you can optimize this process:

  1. Click anywhere inside your Excel table.
    Note: Ensure your data is organized in an Excel table. If it’s not, select your data range, then go to the Insert tab on the ribbon and click on Table. Make sure to check the box for My table has headers if your data includes headers.
  2. Navigate to the Table Design tab on the ribbon in Excel 2021 and 365. See screenshot:
    Tips: In other Excel versions, please do as this:
    In Excel 2013-2019, under the Design tab, and then click Insert Slicer.
    In Excel 2010, it is not possible to insert slicers in tables.
  3. In the Insert Slicers dialog box, select the checkbox next to each field you want to add a slicer for, then click OK.
  4. The slicers for the selected fields are created. Click on any of the slicer buttons to filter the data in the table. In this example, I will filter for Fruit from Germany. See screenshot:
Tips:
  • To filter by multiple items, either hold down the Ctrl key and click on the items you want to filter, or click the Multi-Select button to enable multiple selection.
  • To reset or clear the filters in a slicer, click on the Clear Filter button in the slicer.
 

Create and use slicer in Pivot Chart

Creating and using a slicer in a Pivot Chart in Excel not only enhances the interactivity of your data presentation but also allows for dynamic analysis. Here's a detailed guide on how to optimize this process:

  1. Create a Pivot Chart first. And then, click to select the Pivot Chart.
  2. Go to the PivotChart Analyze tab, and click Insert Slicer. See screenshot:
    Tips: In Excel 2010-2019, under the Analyze tab, click Insert Slicer.
  3. In the Insert Slicers dialog box, select the checkbox next to each field you want to add a slicer for, then click OK.
  4. Now, the slicer is created at once. Click the buttons on the slicer to filter the data displayed in your Pivot Chart. The chart will update immediately to reflect only the data related to your slicer selections.
Tips:
  • To filter by multiple items, either hold down the Ctrl key and click on the items you want to filter, or click the Multi-Select button to enable multiple selection.
  • To reset or clear the filters in a slicer, click on the Clear Filter button in the slicer.
  • If desired, you can integrate the slicer box into the chart area. To do this, enlarge the chart area and reduce the plot area by dragging their edges. Then, move the slicer box into the newly created space. See the demo below:

How to connect slicer to multiple pivot tables / pivot charts?

Connecting a slicer to multiple PivotTables or Pivot Charts in Excel allows for synchronized filtering across various data representations, enhancing the comprehensiveness and interactivity of your data analysis. This section will talk about how to connect slicer to multiple pivot tables / pivot charts.

  1. Create two or more PivotTables or Pivot Charts, in the same sheet. See screenshot:
    Note: A slicer can only be linked to pivot tables and pivot charts that share the same data source. Thus, it is essential to create these pivot tables and pivot charts from the same dataset.

  2. Then, create a slicer for any PivotTable or Pivot Chart by following the steps outlined in the Create Slicer for PivotTable or Pivot Chart section.
  3. After creating the slicer, right click it, then choose Report Connections (PivotTable Connections in Excel 2010). See screenshot:
  4. You’ll see a list of all the PivotTables in the workbook that are based on the same data source. Check the boxes next to the PivotTables you want to connect with the slicer. And then, click OK. See screenshot:
  5. From now on, you can apply filters to all connected PivotTables by simply clicking a button on the slicer. See the demo below:
Tips:
  • To filter by multiple items, either hold down the Ctrl key and click on the items you want to filter, or click the Multi-Select button to enable multiple selection.
  • To reset or clear the filters in a slicer, click on the Clear Filter button in the slicer.

Format slicers in Excel

Formatting slicers in Excel helps make your reports look better and easier to use. You can change how slicers look, resize them, organize their layout, adjust their settings, and fix their position on the sheet. Let's explore how to do these to make your Excel slicers work better and look great.

Change slicer style

  1. Click on the slicer to activate the Slicer Tools Options (Excel 2010-2019) or Slicer (Excel 2021, 365) tab in the ribbon.
  2. Navigate to the Options tab or Slicer tab. Then, pick a style from the Slicer Styles group to change the appearance of your slicer, see screenshot:
 

Resize slicers

● Resize the slicer box:

Normally, you can change the slicer's size is by dragging the edges or the corner of the box. See the demo below:

● Resize slicer buttons:

Click to select the slicer, and then go to the Slicer Tools Options (Excel 2010-2019) or Slicer (Excel 2021, 365) tab in the ribbon. Under the Buttons group, adjust the number of the Height or Width in the slicer to your need. See screenshot:

● Adjust the number of columns in slicer:

When a slicer in Excel contains too many items to fit within its box, you can arrange these items across multiple columns to ensure they are all visible and accessible. This will help in making all slicer items visible and ensures that users can easily interact with the slicer without having to scroll up and down.

Click to select the slicer, and then go to the Slicer Tools Options (Excel 2010-2019) or Slicer (Excel 2021, 365) tab in the ribbon. Under the Buttons group, adjust the number of columns in the slicer to your need. See screenshot:

 

Change the slicer settings

To change the slicer settings in Excel, follow these steps to customize its behavior and appearance according to your needs:

Click on the slicer you want to modify, right-click it and choose Slicer Settings, in the Slicer Setting dialog box you can set the following operations:

  • Name and Caption: Change the name or caption of the slicer for better identification. If you want to hide the slicer header, uncheck the Display header checkbox;
  • Sorting: Choose how to sort the items in the slicer, in ascending or descending order.
  • Filtering Options: Choose to hide items with no data or those deleted from the data source to keep the slicer clean and relevant.
 

Lock the slicer position in a worksheet

Locking a slicer ensures it stays fixed at a certain spot on the worksheet, avoiding any unintended movement when rows and columns are added or deleted, PivotTable fields are modified, or other alterations are made to the sheet. To secure a slicer's position on a worksheet, follow these steps:

  1. Right-click the slicer, and choose Size and Properties from the context menu.
  2. In the Format Slicer pane, under Properties, select the Don't move or size with cells option. See screenshot:

Disconnect a slicer

Disconnecting a slicer in Excel is useful when you need to unlink it from a PivotTable or PivotChart, allowing for independent data analysis without affecting other linked elements.

  1. Click anywhere in the PivotTable to which slicer you want to disconnect.
  2. Then, navigate to PivotTable Analyze tab (Excel 2021, Excel 365) or Analyze (Excel 2013-2019), and click Filter Connections. See screenshot:
  3. In the Filter Connections dialog box, uncheck the check box of the slicer you want to disconnect, and click OK.
Tips: Disconnecting the slicer will not remove it from your spreadsheet; it simply breaks the link with the PivotTable. To re-establish the connection at a later time, just revisit the Filter Connections dialog box and reselect the slicer.

Delete a slicer

To permanently remove a slicer from your worksheet, you can use either of the following methods:

  • Click on the slicer to select it and then press the Delete key.
  • Right-click on the slicer and choose Remove (Slicer Name) from the context menu.

Benefits of using slicers in Excel

Using slicers in Excel offers several benefits that enhance data analysis and presentation:

  • Enhanced Interactivity:
  • Slicers provide a user-friendly interface that makes it easier for users to interact with the data. They can quickly filter and segment the data without navigating complex menus or dialogs.
  • Improved Data Visualization:
  • By allowing users to filter through data and view only what is relevant, slicers help in creating dynamic and interactive charts and tables that better represent the underlying data trends and patterns.
  • Enhance Data Security:
  • Slicers enhance data security and integrity by enabling users to filter the required information without modifying the actual dataset, ensuring the original data remains unaltered and reliable.

In conclusion, slicers provide a dynamic and intuitive method for filtering and analyzing data in Excel, making them a powerful tool for data analysis and presentation. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Related Articles:

  • Count unique values in pivot table
  • By default, when we create a pivot table based on a range of data which contains some duplicate values, all the records will be counted as well, but, sometimes, we just want to count the unique values based on one column to get the right screenshot result. In this article, I will talk about how to count the unique values in pivot table.
  • Reverse a pivot table in Excel
  • Have you ever wanted to reverse or transpose the pivot table in Excel just like the below screenshots shown. Now I will tell you the quick ways to reverse a pivot table in Excel.
  • Change multiple field settings in pivot table
  • When you create a pivot table in a worksheet, after dragging the fields to the Values list in the PivotTable Field List, you may get all the same Count function as following screenshot shown. But now, you want the Sum of function to replace the Count of function at once, how could you change the calculation of multiple pivot table fields at once in Excel?
  • Add multiple fields into pivot table
  • When we create a pivot table, we need to drag the fields into the Row Labels or Values manually one by one. If we have a long list of fields, we can add a few row labels quickly, but the remaining fields should be added to the Value area. Are there any quick methods for us to add all the other fields into the Value area with one click in the pivot table?
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations