How to Filter Excel Pivot Tables with Slicers!

Before proceedings to our main topic, you need to know what is actually a Slicer.

What is a Slicer

A Slicer is an interactive control. A Slicer makes your job easy to filter data in a Pivot table. The following figure shows a pivot table with three Slicers, their headings are AcctType, OpenedBy, and Branch. Each Slicer is containing a particular field. In our example, the pivot table is displaying data for Savings account, Opened by New Accts at the Central branch.

Filtering Pivot Tables with Slicers in Excel

Showing a pivot table with three slicers. The data in the pivot table is filtered by these three slicers.

Filtering what we do with Slicers can be done by using the field labels in the pivot table, but Slicers are targeted for those who might not understand how to filter data in a pivot table. You can also use Slicers to create an attractive and easy-to-use interactive “dashboard”.

To add one or more Slicers in your worksheet, select any cell of your pivot table and then choose Insert ➪ Filter ➪ Slicer. The Insert Slicers dialog box will appear, with a list of all fields in your pivot table. Place a check mark next to one or more Fields, and then click OK.

Filtering Pivot Tables with Slicers in Excel.

Insert Slicers dialog box. This dialog box has the same fields when compared with the PivotTable Fields task pane.

New Feature: In Excel 2013, Slicers are no more limited to pivot tables. The slicer can also be applied to a table (created with Insert ➪ Tables ➪ Table). You can move and resize Slicers. You can change the look of Slicers too. Click the icon in the Slicer’s upper-right corner to clear filtering you have applied in the Slicer.

Read More: What is a Slicer in Excel

Filtering data in a pivot table with Slicer

A Slicer lists the items from the field. Just click a button to filter data in the pivot table. You can choose more than one button pressing CTRL while choosing buttons. Press Shift and click to select a series of consecutive buttons in the Slicer.

The following figure shows a pivot table and a pivot chart. We have used two Slicers to Filter the data (by State and by Month). In our example, we have selected the data for Arizona, Florida, and Kansas, for the Months of March through May. Slicers provide a quick and easy way to create an interactive chart.

Filtering Pivot Tables with Slicers in Excel

Two Slicers are controlling the data in the pivot table.

Download the Working Files

Bank-account-data.xlsx

pivot-table-slicer.xlsx

Happy Excelling 🙂


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply