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.
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.
- Name the table with Sales.
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.
- Give a name (Returns) to the table.
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.
- Go to the Regions sheet and press Ctrl + V  to paste.
- To create a table, press Ctrl + TÂ
- Select the data range by enabling ‘My table has headers’
- Finally, click OK.
- After creating the table, name it with Region.
- To get the only unique values, click on the Remove Duplicates command.
- Then, click OK to create the table with unique values.
- As a result, your table for creating a slicer will be created with the unique values of different Regions.
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.
- 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.
- Therefore, your first PivotTable will appear in a new worksheet (Sheet1).
- Select the fields (Branch and Price) to show in the PivotTable.
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.
- Click on ‘Existing Worksheet’ box.
- To define a location in the existing sheet, click on the right-side icon.
- 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.
- Consequently, your selected location will appear in the Location box.
- Simply, click Enter.
- As a consequence, your second PivotTable with the Returns value will appear in the same sheet.
Step 6: Insert Slicer with Region Table
- Go to the PivotTable Analyze tab.
- From the Filter group, click on the Insert Slicer command.
- From the Slicer box, choose All.
- Then, click on the Region.
- Finally, press Enter.
- Therefore, the Slicer for the Region will show as the image shown below.
Step 7: Build Relationship with Slicer
- Firstly, click on the PivotTable Analyze tab.
- From the Calculations group, click on the Relationships command.
- Click on New to add the first relation.
- 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.
- Similar to the previous procedure, click again on New to create another relationship.
- To create the relationship between the Returns table and the Region table, select the following options as shown in the box below.
- After adding the two relationships, click on Close.
- Right-click the Slicer box.
- Then, click on the Report Connection option from the list.
- 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.
Read More: Excel Slicer for Multiple Pivot Tables (Connection and Usage)
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.
- Again, choose another option from the Region (NOR), the Slicer will filter the results for both the PivotTables.
Read More: [Fixed] Report Connections Slicer Not Showing All Pivot Tables
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Stay with us and keep learning.
