How to Insert a Slicer in Excel Pivot Table (2024)

We occasionally filter data when building Pivot tables.

However, the user may not easily understand what is specifically filtered if the data is filtered using the report filter option 😡

The best Excel solution for this issue is to insert slicers. Slicers are visual filters of Excel. They make it easy for you to change your pivot tables with just a single click.

In this lesson, learn all about Excel slicers and how they can powerfully filter your pivot data table and charts πŸ˜€

Let’s go! Download the attached sample file to practice with me.

Add slicer in pivot table

Adding slicers in Excel is very easy.

You can insert slicers into Excel Tables, Pivot Tables, and Pivot Charts πŸ“ˆ

The below Pivot table shows sales data.

Pivot table data - Sales targets

Imagine that now you want to insert a slicer into your Pivot table.

  1. Select any cell in the Pivot table and go to the Insert Tab.
Go to the Insert tab after selecting a cell in the Pivot Table
  1. Click slicer in the Filter group.
Click slicer in the Filter group
  1. Select a Pivot Table field that you want to add for the slicer from the insert slicers dialog box.
Selecting Pivot Table fields for the slicer
  1. Click OK to insert the slicer into the Excel Book.
Inserting slicers

Now you have added a slicer to your Pivot Table.

To filter data, you have to click the slicer button on the slicer.

If you select “Executive” from this slicer, the Pivot table data will be filtered to “Executive”.

Pivot Table data filtered using a Pivot table slicer

Pro Tip:

Do you like to create a dynamic pivot chart?

Insert slicers for the chart. When you click the buttons of slicers, the Pivot chart will change automatically.

If you want to select multiple items, first, click on the multi-select button.

Click multi-select button to select multiple items

Click the clear filter icon to clear the slicer filter. You can see that icon in the upper right corner of each slicer.

Click the clear filter icon to clear slicer filter

You can customize these Excel slicers.

First, select the slicer to activate the Slicer tab.

If you are using Excel 2010, 2013, 2016, and 2019, you will see the Slicer tools tab with the slicer tools option tab instead of the Slicer tab.

Kasper Langmann, Microsoft Office Specialist
Activating the Slicer tab

You can change the display settings of the slicer by clicking the slicer settings in the slicer group.

Click Slicer Settings to open the slicer settings dialog box

The slicer settings dialog box can be used to do the following customizations to the Excel slicer.

  • Uncheck the box of the display header option to hide the slicer header.
Display header option - Pivot table slicers
  • Change the caption of the slicer header.
Change the caption of the slicer header
  • Sort the items on the slicer.
Sort the items on the slicer.
  • Filter the items on the slicer.
Filter the items on the slicer.

You can use the slicer styles group to change the slicer style. You can select any of the Excel in-built slicer styles or you can create your own slicer style.

Slicer styles

You can increase the number of columns and add multiple columns to the slicer.

Adding multiple columns to the slicer

If you want, you can adjust the size of the slicer buttons from the buttons group.

Adjusting height and width of buttons of the slicer.

Add multiple slicers to the same pivot table

You can connect multiple slicers to the same Pivot Table as well.

All you have to do is to select more than one item from the insert slicers dialog box 🀹🏻

You can select “Group” and “Category” from the insert slicers dialog box in this case.

Selecting multiple items from the insert slicer dialog box

Then you will get multiple slicers to filter data in your Pivot Table.

Multiple slicers - Excel Pivot Table

If you have selected “A” from the “Group” slicer and “Executive” from the “Category” slicer, your Pivot table will be filtered like below.

Pivot Table data filtered with two slicers

Use the same slicer for multiple pivot tables

You can even use a single slicer for multiple pivot tables or multiple pivot charts.

Assume that you have created 2 separate Pivot tables for sales data. One is for sales target and the other one is for sales actual.

Two Pivot tables

You can connect these 2 Excel Pivot tables with one slicer.

  1. Create a slicer for the first Pivot table.

So, you can add a slicer for the group field of the sales target Pivot Table.

You can follow the same steps to create a single slicer.

Then, you can see your Pivot tables and slicer as follows.

Slicer for the first Pivot Table.
  1. Go to the slicer tab after activating that by selecting the slicer of the first Pivot table.
Activating the slicer tab
  1. Click report connections in the slicer group.
Click report connections in the slicer group
  1. Select the second pivot table that you have created from the Report connections dialog box.

Then, click OK.

Select the second pivot table from the Report Connections (Group) dialog box

Now you can use this one slicer for both target sales and actual sales Pivot tables.

If you select “A” from this slicer, all the Pivot tables will show only Group A details.

Data of 2 Pivot tables are filtered with a single slicer.

That’s it – Now what?

Awesome! You now know all about Slicers in Excel. They are such a super cool feature for you to filter data in your Pivot Table and Pivot charts. Excel helped you do all that with just a few clicks πŸ˜€

If accomplishing a lot of tasks with just a few clicks is what you want, then Excel VBA is definitely the answer. With Excel VBA, you can unlock πŸ”“ Excel’s potential to work for your automation and a whole lot more. You can even automate your slicers with VBA.

Start learning VBA simple, easy, and free when you join my FREE Excel Expert VBA training.

Other resources

Did you know that adding slicers in Excel will make your dashboards more dynamic? Yes, you read that right. Apply what you’ve learned today and make your dashboards come alive πŸ‘

Learn how to make dashboards in Excel, here’s our step-by-step guide (check out the 6 best dashboard templates I’ve found too).

You can also read more about Excel Tables and Pivot tables to organize, manage, and visualize your data without breaking a sweat 😏

Frequently Asked Questions

Slicers contain buttons that can be used to filter tables, Pivot Tables, and Pivot charts.

Slicers are visual filters. It helps the users to easily identify which information is filtered in the given table, pivot tables, and pivot charts.

Even though both slicers and filters in Excel do the data filtering, it is easy to use slicers than filters.

Filters cannot be connected with multiple pivot tables or multiple pivot charts. However, slicers in Excel can be connected with many.

Slicers can be placed anywhere. But, filters are tied to columns and rows.