Home > Skills > Tables > Slicers

Filter with Excel Table Slicers

Insert Excel Table Slicers, and make it easy to filter data. Format the Slicers and change the option settings, to suit your filter requirements

Introduction

In Excel 2013, and later versions, you can use Slicers to filter the data in a named Excel Table. To learn more about Excel Tables, go to the Create an Excel Table page.

Add a Table Slicer

To add a Table Slicer, follow these steps:

1. Select a cell in the Excel Table
2. On the Ribbon's Insert tab, click Slicer
• 
3. In the Insert Slicers list, add check marks for the Slicer(s) that you want to create
• 
4. All the Slicers that you inserted appear on the worksheet, overlapping one another.
• 

Arrange the Slicers

To make the Slicers easy to use, create a space for them on the worksheet, where they're always visible. In this screen shot, the top few rows are empty, and Freeze Panes has them locked in place.

The Slicers were moved to that section, and resized to fit. Also, the number of columns for each Slicer was changed.

Move a Slicer

To move a Slicer

• Point to the Slicer's heading
• Drag the Slicer to a new location

Resize a Slicer

To resize a Slicer

• Click on an empty part of the Slicer, to select it
• Point to one of the handles on the Slicer's border
• Drag in or out, to make the Slicer smaller or larger

Change Number of Columns

By default, when you add a Slicer, it has 1 column. You can change the number of columns, to best fit the list of items in the Slicer.

• If the items have lengthy text, 1 column is usually the best setting
• For items with shorter text, change the column setting to 2 or more

Follow these steps to change the number of columns

• Click on an empty part of the Slicer, to select it
• On the Options tab of the Excel Ribbon, go to the Buttons group
• Use the Up and Down arrows to increase or decrease the number of columns

Filter Excel Table With Slicers

In a named Excel table, the heading cells have drop down arrows that you can use to sort or filter the data. You can continue to use those arrows, after adding Slicers.

However, Slicers make it quicker and easier to filter the data, and they show which criteria are currently selected. In the screen shot below, the table is filtered to show records from the East region, in 2019, for the Bars category.

To filter an Excel Table

• Click a button in any of the Excel Table's Slicers
• OR - Drag over several buttons in a Slicer
• OR - Click one button, then press Ctrl and click other buttons

To clear a filter:

• Click the Clear Filter button at the top right of the Slicer

Items Not Available

When you filter the items with one Slicer, that might affect the visible items in another Slicer.

For example, if you click Snacks in the Category Slicer, only the products from the Snacks category are visible in the filtered Excel table.

The Product Slicer changes, and shows the Snack products at the top of the Slicer. Products from other categories are shown below, with lighter colours, to indicate that they are not available.

NOTE: You can change this behaviour in the Slicer Settings.

Change Slicer Settings

There are a few Slicer Settings that you can change, after adding Slicers to an Excel Table.

Sort Order

For Slicers that show dates, you might prefer to see the most recent dates at the top of the list.

To change the sort order for any Slicer, follow these steps:

• Right-click on the Slicer, and click Slicer Settings
• In the Item Sorting and Filtering section, select Ascending or Descending
• Click OK

Change Slicer Caption

By default, the Slicer caption shows the heading from the column in the Excel Table. You can change the Slicer captions, to make them shorter, or easier to understand.

In this example, Orderyr will be changed to Year

• Right-click on the Slicer, and click Slicer Settings
• In the Header section, type the Caption text, to replace the existing caption
• Click OK

Hide Items

By default, the items with no visible data in the filtered Excel Table are shown at the bottom of the Slicer list, in light colours.

To change that setting:

• Right-click on the Slicer, and click Slicer Settings
• In the Hide Items With No Data section, add or remove check marks, to select the setting that you want.
  • If Hide Items With No Data is checked, the items will not appear in the Slicer
  • If Hide Items With No Data is not checked, the items will appear in the Slicer
    • Check the Visually Indicate Items with No Data setting, to show those items in different formatting
    • Check the the Show Items With No Data Last, to show those items after the other items
• Click OK

Download the Sample File

Table Slicers: To download the sample file with the table and Slicers for this tutorial, click here: Table Slicers Sample File. The zipped file is in xlsx format, and does NOT contain macros.

Related Excel Tutorials

Excel Tables

Pivot Table Slicers

Slicers, Pop Up

Excel Slicer Macros