How to Use a Pivot Table Slicer

Nowadays, fewer and fewer people are intimidated by numerical crossroads in a worksheet and obtain more abilities to overcome the complex analysis of a data set in Excel.

However, did you know there’s a tool called Slicer that allows you to filter the information apart from filters? If you don’t, relax because this article will answer the following questions related to this aid:

After reading this article, you will have all the knowledge you need to analyze data and a massive data set effortlessly. Let’s begin!

What is a Pivot Table Slicer?

A Slicer is a higher way to filter a Pivot Table, and despite the Report Filter, which is pretty similar, the Slicer can give you three primary advantages (but there are more)

• Connect and filter multiple Pivot Tables and pivot Charts by pressing just one button for all of them.
• You can move it anywhere across your worksheet.
• It’s more flexible and styled: You have more formatting options, like if the items list is displayed vertically or horizontally, and much more.

You can drill down into specific sections of a report with a Slicer, and with just one click, you will be able to summarize several reports to display only those values and variables you need!

How to insert a Slicer?

You can access and add a Slicer by following the steps below:

1. First, click on the Pivot Table (any cell you instead).
2. Go to the “PivotTable Analyze” tab in the ribbon.
3. Search the “Filter” group tool (about the middle of the panel).
4. Pick the “Insert Slicer” button.

5. Once you click on it, Excel will show you a context pop-up menu. Check the boxes for the field(s) you want to use in the Slicer.

6. Click “OK” when finished.

Notice that you have new Slicers next to or near the original Pivot Table as individual tables for each category you selected.

Note: if they appear disorganized, you can place them with your cursor wherever you need.

Which are the Slicer components?

Before using the Slicer, you should understand its parts and what they are for to use them to your favor:

• Header: It is located at the top left corner of the Slicer and indicates the category’s name of the displayed items. In the preview picture, the Header is the word “Seller.”
• Multi-Select button: Is represented by check marked list next to the title on top. Hence, when clicking this option, you can select more than one item at a time.
• Clear Filter button: This is in the top right corner, represented by a sifter icon with a red X mark, and it will remove all filters from the report. Notice that the icon will be grayed out if you haven’t applied a filter yet.

Then you will see all the elements you have in each category. You will find a scroll bar at the right if there're so many. And obviously, if you want to resize the box, you only have to click on the corners or borders and drag as much as you want.

How to use a Slicer?

Alright! Using the Slicer is very intuitive: all category items will be highlighted in blue by default. So when you click and unmark an element from the Slicer, you are starting to use this tool, and those words in white won’t appear in the pivot table.

Just like that, you can use a Slicer! Of course, if you need to pick multiple items, you can use the Multi-select button or hold the Ctrl key while clicking on the elements you want.

Lastly, if you need to delete a Slicer, you must hover over the Slicer box about the borders or corners until seeing a four-directional arrow. Then, click on it and press the “Delete” key on your keyboard.

How to connect a Slicer to multiple Pivot Tables?

Maybe this is the most useful function that the Slicer has, and it’ll help you to be more accurate in your data analysis. However, to start using this fantastic aid, you must first connect the Slicer to all the Pivot Tables by following the steps below:

1. Make sure that the same data source makes all pivot tables. Otherwise, this won’t work.

Tip: If the source of the first pivot table is called “Table 1,” all the following pivot tables must use the same table name.

2. Click on the slicer you want to link.
3. Go to the “Slicer” tab in the ribbon.

4. In the “Slicer” group tools, select the “Report connections” button.

5. In the dialog box, select those tables you want to link to the slicer.

6. Click “OK” to confirm the operation.

And there you have it! Every time you click an element in the Slicer, you filter all pivot tables connected to your selection. 

Do you want to know more?

Now that you know how worthwhile a Slicer can be, you may have some questions about the topic you want to solve, or you simply want to know how to give it a refined look. Or maybe you’re looking for other tips to master Excel. Anyway, you can improve your abilities with our Excel course here in skillademia. Check it out!