Use slicers to filter data

Slicers provide buttons that you can click to filter table data, or PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable.

Excel Ribbon Image

When you select an item, that item is included in the filter and the data for that item will be displayed in the report. For example, when you select Callahan in the Salespersons field, only data that includes Callahan in that field are displayed.

You can use a slicer to filter data in a table with ease.

Use a slicer to filter data

  1. Click anywhere in the table.

  2. Select Insert Slicer.

    Insert Slicer
  3. Select the fields you'd like to filter.

  4. Select OK and adjust your slicer preferences, such as Columns, under Options.

    Note: To select more than one item, hold Ctrl, and then select the items that you want to show. Select and hold the corner of a slicer to adjust and resize it.

  5. Select Clear Filter Delete to clear the slicer filter.

Convert data into a table

  1. There are four ways to convert data into a table:

    Note:  In order to use a slicer, you must convert your data into a table first.

    • Press Ctrl + T.

    • Press Ctrl + l.

    • Select Home > Format as Table.

    • Select Insert > Table.

  2. Select OK.

  1. Click anywhere in the PivotTable for which you want to create a slicer.

    This displays the PivotTable Analyze tab.

  2. On the PivotTable Analyze tab, click Insert Slicer.

  3. In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.

  4. Click OK.

    A slicer is displayed for every field that you selected.

  5. In each slicer, click the items on which you want to filter.

    To select more than one item, hold down Command, and then click the items on which you want to filter.

  1. Click anywhere in a table for which you want to create a slicer.

    This displays the Table tab.

  2. On the Table tab, click Insert Slicer.

  3. In the Insert Slicers dialog box, select the check box of the fields (columns) for which you want to create a slicer.

  4. Click OK.

    A slicer is displayed for every field (column) that you selected.

  5. In each slicer, click the items on which you want to filter.

    To select more than one item, hold down Command, and then click the items on which you want to filter.

  1. Click the slicer that you want to format.

    This displays the Slicer tab.

  2. On the Slicer tab, click a colored style that you want.

If you have a slicer on a PivotTable already, you can use that same slicer to filter another PivotTable. Please note that this only works when the two PivotTables use the same data source.

  1. First create a PivotTable that is based on the same data source as the PivotTable that already has the slicer you want to reuse.

  2. Click the slicer that you want to share in another PivotTable.

    This displays Slicer tab.

  3. On the Slicer tab, click Report Connections.

  4. In the dialog box, select the check box of the PivotTables in which you want the slicer to be available.

  1. Click anywhere in the PivotTable for which you want to disconnect a slicer.

    This displays the PivotTable Analyze tab.

  2. Click the PivotTable Analyze tab, and then click Filter Connections.

  3. In the dialog box, clear the check box of any PivotTable fields for which you want to disconnect a slicer.

Do one of the following:

  • Click the slicer, and then press DELETE.

  • Control-click the slicer, and then click Remove <Name of slicer>.

A slicer typically displays the following components:



PivotTable slicer elements

1. A slicer header indicates the category of the items in the slicer.

2. A filtering button that is not selected indicates that the item is not included in the filter.

3. A filtering button that is selected indicates that the item is included in the filter.

4. A Clear Filter button removes the filter by selecting all items in the slicer.

5. A scroll bar enables scrolling when there are more items than are currently visible in the slicer.

6. Border moving and resizing controls allow you to change the size and location of the slicer.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Filter data in a PivotTable

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×