Connect Slicer to Multiple Pivot Tables from Different Data Source

In PivotTable, the Slicer is used to construct a link between fields that are comparable. It’s a fantastic tool for comparing and analyzing data. In this tutorial, we will show you how to connect slicer to multiple pivot tables from different data source.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

We’ll link a slicer between two data sets in the sections below. The first data set is for the sales data in the ‘Sales’ worksheet, and the second data set is for the returned products from the sold products in the ‘Returns’ worksheet. Then, using the data from the ‘Sales’ and ‘Returns’ worksheets, we’ll build a new worksheet for the corresponding value of Regions. We’ll put them in a separate worksheet labeled ‘Region.’

Later, we’ll combine the Sales and Returns data into two pivot tables in the same spreadsheet. Then we’ll connect the slicer for Regions’ values to the two pivot tables and analyze the data for Sales and Returns Values. To complete the work, follow the procedures given below.

Step 1: Create Table with Sales Data

  • In the ‘Sales’ worksheet, select a cell.
  • Press  Ctrl + T  to create a table.
  • Select the data range with the column header.
  • Then, click OK.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Name the table with Sales.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source


Step 2: Create Table with Return Data

  • In the ‘Returns’ worksheet, select a cell.
  • Then, press  Ctrl + T  to create a table.
  • Then, click OK after selecting the data range with the column header.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Give a name (Returns) to the table.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source


Step 3: Create a Table for Slicer

  • As we want to connect a slicer for the value of Regions, we need to create another table with the unique values of Regions.
  • Firstly, select the Region column and press  Ctrl C  to copy.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Go to the Regions sheet and press  Ctrl V  to paste.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • To create a table, press  Ctrl
  • Select the data range by enabling ‘My table has headers’
  • Finally, click OK.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • After creating the table, name it with Region.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Then, click OK to create the table with unique values.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • As a result, your table for creating a slicer will be created with the unique values of different Regions.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source


Step 4: Insert a PivotTable with Sales Table

  • In the ‘Sales’ worksheet, click on the Insert tab.
  • From the Tables ribbon, choose the PivotTable option.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • In the Table/Range box, check whether the table name (Sales) is right.
  • Click on the New Worksheet option to create the PivotTable in a new worksheet.
  • Select the box ‘Add this data to the Data Model’.
  • Finally, press Enter.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Therefore, your first PivotTable will appear in a new worksheet (Sheet1).
  • Select the fields (Branch and Price) to show in the PivotTable.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source


Step 5: Insert a PivotTable with Returns Table

  • Similar to the previous section, click on the Insert tab.
  • Then, select the PivotTable from the Tools group.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Click on ‘Existing Worksheet’ box.
  • To define a location in the existing sheet, click on the right-side icon.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Go to the existing PivotTable worksheet (Sheet1).
  • Then, click on a cell (D3) to select the location for placing the new PivotTable.
  • Finally, click on the right-side icon in the box to go back.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Consequently, your selected location will appear in the Location box.
  • Simply, click Enter.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • As a consequence, your second PivotTable with the Returns value will appear in the same sheet.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source


Step 6: Insert Slicer with Region Table

  • Go to the PivotTable Analyze tab.
  • From the Filter group, click on the Insert Slicer command.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • From the Slicer box, choose All.
  • Then, click on the Region.
  • Finally, press Enter.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Therefore, the Slicer for the Region will show as the image shown below.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

Read More: How to Insert Slicer in Excel (3 Simple Methods)


Step 7: Build Relationship with Slicer

  • Firstly, click on the PivotTable Analyze tab.
  • From the Calculations group, click on the Relationships command.

Steps to Connect Slicer to Multiple Pivot Tables from Different Data Source

  • Click on New to add the first relation.

Sample Data

  • For establishing the relation between Sales and Region, choose the following options from the drop-down lists of the Create Relationship box.
  • Then, press Enter.

Sample Data

  • Similar to the previous procedure, click again on New to create another relationship.

Sample Data

  • To create the relationship between the Returns table and the Region table, select the following options as shown in the box below.

Sample Data

  • After adding the two relationships, click on Close.

Sample Data

  • Right-click the Slicer box.
  • Then, click on the Report Connection option from the list.

Sample Data

  • Click on both checkboxes to show the relationship between the two PivotTables.
  • Therefore, click OK and your two PivotTables are connected with the Region Slicer.

Sample Data

Read More: How to Filter Excel Pivot Tables with Slicers!


Step 8: Final Result

  • Click on a Region (MID) and your both PivotTables will show the relations between the Branch and Prices for the particular Region.

Sample Data

  • Again, choose another option from the Region (NOR), the Slicer will filter the results for both the PivotTables.

Sample Data

Read More: [Fixed] Report Connections Slicer Not Showing All Pivot Tables


Conclusion

I hope this article has given you a tutorial about how to connect a slicer to multiple pivot tables from different data sources. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.

We, the ExcelDemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

Tags:

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo