Connect Slicer to Multiple Data Sources

Excel allows you to connect a slicer to multiple data sources using the power of Power Pivot. In the past a slicer could connect to multiple pivot tables provided those pivot tables shared the same source data. With the advent of Power Pivot there is a way to have separate tables that share a common field and link that field in the Diagram View of Power Pivot so that the tables are able to talk to one another.


The following YouTube video takes you through the process from start to finish.

The File

Excel Slicer Multiple Connections.xlsx

Setting up Power Pivot

First things first - we need to sent a minimum of 3 tables to Power Pivot. In the following example I will send the revenue table, the expense table and finally the region or location table. We will connect these 3 tables together in the diagram view.

Open the file - Create 3 tables.

  • Location - on the Region Tab.

  • Revenue - on the Revenue Tab.

  • Expense - on the Expense Tab.

Power Pivot  Slicer
 

From the Power Pivot Menu - Choose Add to Data Model.


Do the same for all 3 tables and you will have all 3 data sets in the Power Pivot back end.

Power Pivot
 

Choose Diagram View

Power Pivot Excel Slicer
 

Create a connection between.

Location - Region to Expense Region

Power Pivot Excel Slicer connect
 

Drag and drop the region as shown above.

Power Pivot Connect
 

A connection between the two tables is created. Do the same for the Revenue table.

Power Pivot Connection with Slicer
 

The table should look like the above with two connections between the Location and Expense and Revenue.

Power Pivot
 

Now Insert a 2 PivotTables from the Home menu. One from the Revenue table and one from the Expense table.

Power Pivot Connections
 

Be sure to use the Location table as the filter for both pivot tables, one from Revenue and one from Expense.


Insert the Slicer

Inserting the slicer is the second last part of the process. Click inside any of the 2 pivot tables and choose the Analyze menu.

Power Pivot include slicer
 

Choose Insert Slicer. This will bring up the following dialog.

Power Pivot  table slicer
 

Be sure to tick Region from the Location table. This will give you access to both tables as the connection was set up in Power Pivot.

Power Pivot
 

The above slicer is created.

Now right click the Slicer and choose Report Connections.

Power Pivot connected to a slicer tutorial
 

Be sure to choose both the Revenue and Expense Pivot tables. This should allow you to control both pivot tables from two completely different data sources.