Excel Slicer for Multiple Pivot Tables (Connection and Usage)

If you need to filter your Pivot Table too frequently, using the usual method of filtering can be time-consuming. Here comes the wonder of Slicer. It’s an array of buttons that allows filtering super fast. In this article, you will learn how to connect a slicer for multiple Pivot Tables in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


What Is Slicer?

Slicer is a sophisticated way to Filter Excel Tables as well as Pivot Tables. It’s the quickest way to Filter out data. Other than filtering data, the slicer also shows the current state of filtering an Excel Table or a Pivot Table.


Steps to Connect Slicer for Multiple Pivot Tables in Excel

Step-1: Creating Multiple Pivot Tables to Connect Slicer

To demonstrate to you how to connect a slicer to multiple pivot tables, I will be using the following dataset as source data.

Here, I’m going to create two pivot tables for this instance. Now follow the steps below to turn the dataset above into a pivot table.

  • First, go to Insert ➤ PivotTable ➤ From Table/Range.

Inserting data from range o use Slicer for Multiple Pivot Tables in Excel

The ‘PivotTable from table or range’ dialog box will appear.

  • Now insert the table range in the Table/Range
  • Then select New Worksheet and click OK.

Importing data: Slicer for Multiple Pivot Tables in Excel

  • For the first Pivot Table, select the Sales and Month column in the PivotTables Field dialog box.
  • Then put Months under the Rows section and Sum of Sales in the Values

Pivot Table fields: Slicer for Multiple Pivot Tables in Excel

After that, you will get the first Pivot Table having two columns.

To create the second Pivot Table,

  • Again, go to Insert ➤ PivotTable ➤ From Table/Range.

The ‘PivotTable from table or range’ dialog box will open.

  • Now insert the table range again in the Table/Range
  • Then select Existing Worksheet.
  • After that pick up a cell in the Location This will be the destination of the second Pivot Table.
  • After that, hit OK.

Selecting a location: Slicer for Multiple Pivot Tables in Excel

  • This time select the Region and Sales columns in the PivotTable Fields dialog box.
  • Drag Region under the Rows
  • Under the Values section, keep the Sum of Sales.

After that, you will get your second Pivot Table having two columns.


Step-2: Inserting Slicer for Multiple Pivot Tables

Now it’s time to insert the Slicer.

To do that,

❶ Select a cell in the first Pivot Table.

❷ Then go to PivotTable Analyze ➤ Insert Slicer.

inserting a slicer to use Slicer for Multiple Pivot Tables in Excel

❸ Select Region and Months in the Insert Slicer dialog box.

❹ Now press OK.

Insert Slicer for Multiple Pivot Tables in Excel

This will create two slicers named Region and Months respectively.

You can adjust the position and size of the slicers just by dragging them.

Read More: How to Insert Slicer without Pivot Table in Excel


Step-3: Connecting Slicer to Multiple Pivot Tables in Excel

Just inserting slicers won’t work automatically. At first, you have to connect those slicers to your Pivot Tables.

To connect the Region slicer with both Pivot Tables,

❶ Right-click on the Region slicer.

❷ Then select Report Connections.

Connecting Slicer for Multiple Pivot Tables in Excel

Report Connections (Region) dialog box will pop up.

❸ Check PivotTable3 and PivotTable4.

❹ Then hit OK.

So, the connection between the Region slicer and both Pivot Tables has been established.

To connect the Months slicer,

❺ Right-click on the Months slicer.

❻ Then select Report Connections.

❼ Check PivotTable3 and PivotTable4 in the Report Connections (Months) dialog box.

❽ After that, hit OK.

Connecting Slicer for Multiple Pivot Tables in Excel

So the connection between the Months slicer and the Pivot Tables has been established.

Now let’s check the slicers whether work or not.

  • Select Feb from the Months
  • Select East from the Region

As you can see, both Pivot Tables filtered data based on the selection in the slicers.

Using Slicer for Multiple Pivot Tables in Excel

  • Now select Apr in the Months
  • Select West in the Region

As you can see, both Pivot Tables have filtered data based on the selection in the slicers.

So, this too how you can connect slicers to multiple pivot tables in Excel.

Read More: Connect Slicer to Multiple Pivot Tables from Different Data Source


Things to Remember

  • Make sure you established connections between the slicers and the Pivot Tables.
  • Slicer is an alternative to Excel Filter.
  • Slicer displays the current state of the filtering.

Conclusion

To sum up, we have discussed steps to connect a slicer to multiple pivot tables in Excel. You are recommended to download the practice workbook attached with this article and practice all the methods. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.


Related Articles

Tags:

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo