Tired of filtering each of your Excel Pivot Tables one by one? What if you could use just one filter to change all of them at once? Good news! You can easily connect a single slicer to many pivot tables. This helps you save time and makes your reports look cleaner and more professional. In this easy guide, we will show you how to do this for two common situations: when your pivot tables use the same data, and when they use different data. Let’s make your Excel reports super dynamic!

Method 1: Connecting Slicer to Pivot Tables Made from Same Data Source

This method is super easy when your pivot tables are built using the same original data. Excel is smart! When you create a pivot table, it first makes a special copy of your data called a “Pivot Cache.” If you make another pivot table from the same data, Excel uses the same Pivot Cache. This shared connection makes linking them with one slicer very simple.

Let’s use an example with sales data to understand this better.

Step 1: Create Your First Pivot Table

First, let’s create the first pivot table from your data.

1. Click anywhere inside your data.
2. Go to the “Insert” tab on the top menu.
3. Click on “PivotTable.”
4. 

1. In the small box that appears, choose “New Worksheet” and click “OK.”
2. 

1. This will open a new sheet. In the “PivotTable Fields” area (usually on the right), drag ‘Salesperson’ to the “Rows” area and ‘Revenue’ to the “Values” area. This will show you sales by salesperson.

Step 2: Create Your Second Pivot Table

Now, let’s make the second pivot table. You have two simple ways:

• Option 1: Create from scratch. You can repeat Step 1, but make sure to select the same original data again.

• Option 2: Copy and paste the first pivot table. This is often easier! Just copy the first pivot table and paste it somewhere else on the same sheet. Excel will automatically use the same data source and Pivot Cache.

For our example, let’s change the second pivot table to show ‘Revenue’ by ‘Category’ instead of ‘Salesperson’.

Step 3: Connect One Slicer to Both Pivot Tables

This is where the magic happens! We will add a slicer and then link it to both pivot tables.

1. Click on any cell inside your first pivot table.
2. Go to the “Analyze” tab (or “PivotTable Analyze” tab) on the top menu.
3. In the “Filter” group, click on “Insert Slicer.”
4. 

1. A list of fields will appear. Choose the field you want to filter by, for example, ‘Category’, and click “OK.”
2. 

1. A new slicer for ‘Category’ will appear on your sheet.

At this point, if you click on the slicer, it will only filter the first pivot table. Let’s connect it to the second one!

1. Right-click on the ‘Category’ slicer.
2. From the menu that appears, click on “Report Connections…”
3. 

1. A new box will open showing all pivot tables that can be connected. You will see both your pivot tables listed.
2. Check the box next to all the pivot tables you want this slicer to control.
3. 

1. Click “OK.”

You are done! Now, when you click on different options in your ‘Category’ slicer, both pivot tables will update at the same time!

Pro Tip: To choose more than one item in a slicer, hold down the Ctrl key on your keyboard while clicking on different options.

Method 2: Connecting Slicer to Pivot Tables Created from Different Data Sources

Sometimes, your data might be in different tables, but they are related. For example, one table might have sales details, and another might have salesperson details. You can still connect a single slicer to pivot tables made from these different sources, but it needs a little more setup using the “Data Model.”

For this to work, both your data sources need to have a common field (like ‘Salesperson ID’ or ‘Product Name’) that links them together.

Let’s imagine you have two different data tables:

In our example, ‘Salesperson’ is the common field in both tables. Also, in the second data table (Salesperson details), each salesperson’s name should appear only once.

Step 1: Create Pivot Tables with the Data Model

In this step, we will create two pivot tables, one from each data source, and make sure to add them to Excel’s “Data Model.” This Data Model helps us create links between different data tables.

Creating the First Pivot Table:

1. Click anywhere inside your first data table.
2. Go to the “Insert” tab and click “PivotTable.”
3. 

1. In the “Create PivotTable” box, choose “New Worksheet.”
2. 

1. Very important: Check the box that says “Add this data to the Data Model” at the bottom. Then click “OK.”
2. 

1. For now, leave this pivot table blank. We will set it up after linking the data sources.

Creating the Second Pivot Table:

Repeat the same steps for your second data table:

1. Click anywhere inside your second data table.
2. Go to the “Insert” tab and click “PivotTable.”
3. Choose “New Worksheet.”
4. Again, remember to check the box “Add this data to the Data Model.” Then click “OK.”
5. Leave this second pivot table blank for now too.

Step 2: Create Relationships in Power Pivot

Now, we need to tell Excel how your two different data tables are connected. We do this using a special tool called “Power Pivot.”

1. Go to the “Power Pivot” tab on the top menu. (If you don’t see it, you might need to enable it from Excel Options > Add-ins.)
2. Click on “Manage” in the “Data Model” group.
3. 

1. The Power Pivot window will open. In this window, click on “Diagram View” on the top right.
2. 

1. You will see your two data tables as boxes. Find the common field (like ‘Salesperson’) in both boxes.
2. Click and drag the ‘Salesperson’ field from one table box and drop it onto the ‘Salesperson’ field in the other table box. This creates a line connecting them, showing their relationship.
3. 

1. Close the Power Pivot window.

Note: This creates a “one-to-many” relationship. This means one table (like the salesperson details) has unique entries for each salesperson, while the other table (like sales transactions) might have many sales for one salesperson.

Step 3: Configure Your Pivot Tables

Now that your data sources are linked, we can set up our blank pivot tables.

If you click on any of your blank pivot tables, you will see the “PivotTable Fields” pane on the right. Notice a new tab called “All.” If you click on it, you will see fields from both your connected data tables! This means you can now use fields from different sources in the same pivot table.

Let’s set up the pivot tables:

• For the First Pivot Table: Drag ‘Salesperson’ (from Data 1) to “Rows” and ‘Revenue’ (from Data 1) to “Values.”

• For the Second Pivot Table: Drag ‘Manager’ (from Data 2) to “Rows” and ‘Revenue’ (from Data 1) to “Values.” See how you are using fields from different original data sources in one pivot table! This works because you created a relationship between them.

Step 4: Connect the Slicer

Finally, let’s add the slicer and connect it to both pivot tables. The steps are very similar to the first method!

1. Click on any cell inside your first pivot table.
2. Go to the “Analyze” tab (or “PivotTable Analyze” tab) on the top menu.
3. Click on “Insert Slicer.”
4. 

1. Choose the field you want for your slicer, for example, ‘Category’, and click “OK.”
2. 

1. Right-click on your new slicer.
2. Select “Report Connections…”
3. 

1. In the “Report Connections” box, check the boxes next to both your pivot tables.
2. 

1. Click “OK.”

And there you have it! Now, even though your pivot tables come from different original data sources, a single slicer can control both of them because you linked them using the Data Model!

Important Things to Remember (Common Mistakes)

While connecting slicers is powerful, keep these points in mind to avoid problems:

• Slicer doesn’t affect the second pivot table: Double-check that you correctly selected both pivot tables in the “Report Connections” dialog box. If a pivot table is not checked, the slicer won’t filter it.

• Can’t see both pivot tables in Report Connections: This usually means your pivot tables do not share the same data source (Method 1) or were not properly added to the Data Model (Method 2). Go back and ensure you followed those steps correctly.

• Relationship creation fails (for different data sources): When connecting tables in Power Pivot, make sure your common fields (like ‘Salesperson’) have the exact same type of data (e.g., both are text, both are numbers). Also, ensure that the “one” side of your “one-to-many” relationship (the table with unique values, like salesperson details) does not have duplicate entries in the common field.

Conclusion

Congratulations! You have now learned how to connect a single slicer to multiple pivot tables in Excel, whether they come from the same data source or different ones. This skill will greatly improve your ability to analyze data, create dynamic reports, and present your findings clearly. Keep practicing, and you will become an Excel pro in no time! If you have any questions, feel free to ask. Happy Excelling!