Note: The other languages of the website are Google-translated. Back to English

How to connect a single slicer to multiple pivot tables in Excel?

By default, an Excel pivot table slicer is only connected to the pivot table that you are inserting the slicer from. In some cases, to make pivot tables work more efficiently, you may need to use a single slicer to control more than one pivot table in your workbook. This tutorial provides two methods for you to connect a single slicer to multiple pivot tables, not only from the same data set, but from different data sets.

Connect a single slicer to multiple pivot tables from the same data set
Connect a single slicer to multiple pivot tables from different data sets


Connect a single slicer to multiple pivot tables from the same data set

As shown in the screenshot below, there is a sales table in the range A1:H20, now, you want to create two pivot tables to analyze the data, and then use a single slicer to control these two pivot tables. Please do as follows to get it done.

1. First, you need to create two pivot tables based on the table range.

If the range is not in Table format, you need to select the whole range and click Insert > PivotTable.
If the range is a Table format range, you just need to click any cell of the table and then go to click Design (Table Tools) > Summarize with PivotTable.

2. In the opening Create PivotTable dialog box, specify where to place the pivot table in the Choose where you want the PivotTable report to be placed section, and then click the OK button.

3. Now you need to add fields to your pivot table. Please drag the needed fields to the target areas one by one.

In this case, I drag the Date field from the field list to the Rows area and drag the Sales field to the Values area. See screenshot:

4. Go ahead to insert the second pivot table for the table range.

Here I created two pivot tables as shown in the screenshot below.

5. Now you need to insert a slicer (this slicer will be used to control both pivot tables). Click on any cell in one pivot table, such as PivotTable1, and then go to click Analyze (under the PivotTable Tools tab) > Insert Slicer.

6. In the opening Insert Slicers dialog box, tick the column you want to use to filter both the two pivot tables, and then click OK.

7. Then a slicer is inserted into the current worksheet. Since the slicer is now only connected to the PivotTable1, you need to link it to the PivotTable2 as well. Right click the slicer and click Report Connections from the context menu.

8. In the Report Connections dialog box, check the pivot tables that you need to connect to this slicer at the same time, and then, click the OK button.

Now the slicer is connected to both the pivot tables.


Connect a single slicer to multiple pivot tables from different data sets

The above pivot tables all come from the same data set. If the pivot tables come from different data sets, you need to handle this task in a different way.

As shown in the screenshot below, supposing you have two worksheets (August and September) containing monthly sales for different months, and the data in these two tables needs to be analyzed by different pivot tables. Also, you need a single slicer to control both the pivot tables. The method in this section can do you a favor.

Note: Both worksheets should have the same column data that used to create the slicer.

Create tables

1. Make sure both the data ranges are in Table format. If not, you need to convert them to Tables.

1) Select the whole data range in the worksheet “August” and press Ctrl + T keys.
2) In the opening Create Table dialog box, the selected range is listed in the textbox automatically. If the range you selected has headers, please check the My table has header box and click the OK button.

3) Repeat the above step 1) and 2) to convert the data range in the worksheet “September” to Table.
Rename tables

2. After converting the ranges to tables, please rename the tables with descriptive names. Here I change the table names to match the names of the worksheets, i.e. August and September.

Click on any cell in the table, go to the Design tab, and then modify the table name in the Table Name text box.

Create a helper column for creating the slicer and convert to table

3. Create a new worksheet by clicking the New sheet button and rename the newly created worksheet as you need.

4. Go back to any worksheet containing data, such as “August”, select the column data that you want to use to create the slicer. Here I select the Product column in the worksheet “August”. Press Ctrl + C keys to copy the data, then press the Ctrl + V keys to paste the data in the newly created worksheet.

5. Keep the data selected in the newly created worksheet, click Data > Remove Duplicates.

6. In the Remove Duplicates dialog box, click the OK button.

7. Then a dialog box pops up to tell you how many duplicates are removed from the list, click OK to close the dialog box.

8. After removing duplicates, select the remained data cells and press the Ctrl + T keys. In the popping up Create Table dialog box, check the My table has headers box and then click OK.

9. Change the table name in this newly created worksheet to match the column header, which is “Product”.

Create pivot tables from different data sets

10. Go back to the worksheet named “August”, click on any cell in the table and go to click Design > Summarize with PivotTable.

11. In the create PivotTable dialog box, you need to configure as follows.

11.1) The name of the currently selected table is displayed in the Table/Range textbox;
11.2) Select the New Worksheet option in the Choose where you want the PivotTable report to be placed section;
11.3) Check the Add this data to the Data Model box in the Choose whether you want to analyze multiple tables section;
11.4) Click the OK button. See screenshot:

12. Now you need to add fields to your pivot table. Please drag the needed fields to the target areas one by one.

In this case, I drag the Date field from the field list to the Rows area and drag the Sales field to the Values area. See screenshot:

13. Go to the worksheet named “September”, click on any cell in the table and go to click Design > Summarize with PivotTable.

14. Then you need to configure the settings in the Create PivotTable dialog box.

The different setting here is that you need to select the Existing Worksheet option and select a cell in the worksheet where the first pivot table is placed.

15. Now add fields to this pivot table.

Now you get two pivot tables from different data sets placed in one worksheet.

Insert a pivot table slicer

16. Click on any pivot table (here I click on any cell in the first pivot table), click Analyze > Insert Slicer.

17. In the Insert Slicers dialog box, you need to configure as follows.

17.1) Click the All tab;
17.2) Check the column in the table “Product” you created in Step 8;
17.3) Click the OK button.

Build the relationships between the existing tables

18. Now you need to build the relationship between the existing tables. Click on any pivot table, and then click Analyze > Relationships.

19. In the opening Manage Relationships dialog box, click the New button.

20. In the Create Relationship dialog box, you need to build the first relationships as follows.

20.1) The first relationship is from the August table to the Product table.
So, you need to choose Data Model Table: August and Data Model Table: Product separately from the Table and Related Table drop-down lists. And choose Product as the related column in both tables. See screenshot.
20.2) Click the OK button.

21. Then it returns to the Manage Relationships dialog box, click the New button again.

22. In the Create Relationship dialog box, you need to build the second relationships as follows.

22.1) The second relationship is from the September table to the Product table.
So, you need to choose Data Model Table: September and Data Model Table: Product separately from the Table and Related Table drop-down lists. And choose Product as the related column in both tables. See screenshot.
22.2) Click the OK button.

23. When it returns to the Manage Relationships dialog box, you can see the two relationships listed inside. Click the Close button to close the dialog box.

24. Right click the slicer and click Report Connections from the context menu.

25. In the Report Connections dialog box, check the pivot table that you also need it to connect to this slicer and click the OK button.

Now you have finished all steps.

The slicer is now connected to both the pivot tables from different data sets.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations