Advertisements

Use slicers to filter data

Applies to: Excel for Office 365, Excel for Office 365 for Mac, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2016 for Mac.

Slicers offer buttons that you can press to filter tables, or PivotTables. As well as quick filtering, slicers also reveal the current filtering state, which makes it easy to understand what exactly is now being presented.

Windows:

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

Create a slicer to filter data

  1. Firstly, select any area in the table or PivotTable.
  1. Next, find Insert Slicer. This is in the Home tab.
Insert Slicer
  1. Thirdly, choose your ideal checkboxes for the fields to present. Do this in the Insert Slicers dialogue box. Then, press OK.
  1. Fourthly, a slicer will be designed for each field that you chose. Selecting any of the slicer buttons will instantly use that filter in the linked table or PivotTable.

Notes:

  • Firstly, to pick several items, hold Ctrl. Next, choose your desired items to show.
  • Also, you can clear a slicer’s filters. Simply press Clear Filter in the slicer.
  1. Furthermore, you can modify your slicer preferences in the Slicer tab (in newer versions of Excel). Alternatively, use the Design tab (Excel 2016 and older versions) on the ribbon.

Note: Pick and hold the corner of a slicer to alter and resize it.

  1. Finally, you can link a slicer to several PivotTables. Go to Slicer > Report Connections > check the PivotTables to include, then press OK.

Note: Slicers can only be connected to PivotTables with the equal data source.

Slicer components

A slicer usually displays the following components:

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

2. A filtering button that is left unselected denotes that the item is excluded from the filter.

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

4. A Clear Filter button deletes the filter by choosing all items in the slicer.

5. A scroll bar allows scrolling when the overall number of items exceed those which can be seen in the slicer.

6. Border moving and resizing controls facilitate altering the size and location of the slicer.

Need more help?

See below for some avenues to resolving your queries and/or recommending ideas.

See Also

Filter data in a PivotTable

Advertisements

Leave a Reply

%d bloggers like this: