Pivot Table is one of the most powerful tools in Excel to present the summary of a larger dataset efficiently. Besides, a Slicer is an interactive tool for filtering the Pivot Table. Unfortunately, the Slicer might not work perfectly because of some reasons. In this instructive session, I’ll present 3 issues for the report connections slicer not showing all pivot tables including the way to fix these issues.
What Is Report Connections Feature in Excel Pivot Table?
Simply, the Report Connections is a feature to manage the Pivot Tables that are connected with the Slicer tool. This feature will work if you add Slicer to a Pivot Table or several Pivot Tables. And, you can connect or disconnect those Pivot Tables to filter together using the Slicer. For example (see below screenshot), you’ll get the check box before the name Pivot Table to connect.
Issues and Solutions for Report Connections Slicer Not Showing All Pivot Tables
Let’s say, you have a dataset like the following one where Product Category is given with their Order Date, Quantity, and Sales based on the States of the U.S.
Now, you have to create a Pivot Table for the above dataset.
- Just, keep your cursor over a cell within the dataset and go to Insert tab > Pivot Table > From Table/Range.
Shortly, you’ll be able to create a Pivot Table like the following PivotTable1.
Let’s come to our topic!
You have to know why Report Connections Slicer does not show all Pivot Tables with the solutions.
1. When No Connections Are Found
Sometimes, you might see No connections found words as shown below.
Why is it happening?
Especially, you’ll get such types of words if you insert Slicer (from the Insert tab > Filters ribbon) by selecting a cell outside of the Pivot Table. I tried the Slicer while selecting the blank J7 cell. That’s why Excel showed me No connections found.
The Solution to the Issue:
- Essentially, insert the Slicer tool while putting the cursor over a cell within the Pivot Table.
- Immediately, you’ll get the filtering options (in the Insert Slicers dialog box). And. check the box before the States option.
- After pressing OK, all States will appear in front of you to filter.
Now, click on any state among the options (e.g. Texas), and your Slicer will work properly.
Read More: How to Format Slicer in Excel
2. If Slicer Is Not Connected with All Pivot Tables
Have a look at the following figure where two Pivot Tables (PivotTable1 and PivotTable2) are available. But the Slicer doesn’t work for PivotTable2. Here, I select Arizona. Then, PivotTable1 is filtered only.
What is the reason for this?
Let’s click on the Report Connections feature.
Immediately, you’ll see that the PivotTable2 is not connected. That’s the main reason.
The Solution to the Issue:
Just check the box before the PivotTable2.
Eventually, you’ll find that the Slicer is working for the two Pivot Tables simultaneously.
3. When Source Data Range Is Changed
Lastly, you might get troubled with the Slicer if you add a new input to the source data.
Let’s say, you want to add the B16:F18 cells to the created Pivot Table where the Slicer exists also.
Now, right-click (while selecting a cell within the Pivot Table) and pick the Refresh option.
Later, you’ll find that the Slicer shows PivotTables but doesn’t work perfectly. That means the PivotTables are not updated with the new data.
Now, let’s see what will happen if you change the source of data?
- Firstly, click on the Change Data Source from the PivotTable Analyze tab.
- Next, specify the source of data (e.g. $B$4:$F$18).
Immediately, you’ll get a message. It tells you that you cannot change the source of the dataset until you remove the filter controls (Slicer).
The Way of Fixing the Issue:
Traditionally, you can do the below tasks.
- Remove the filter controls first > Change the data source > Add filter controls again.
But I’ll recommend you to use a dynamic way i.e. creating an Excel table. Please do the following steps.
- Initially, create a table from the Insert tab > Table option (or press CTRL + T) while selecting a cell within the dataset.
- Later, create the Pivot Tables by using the created Table2.
After adding the Slicer (also check the Report Connections where you need to connect two Pivot Tables), you’ll get the following output.
- Next, add the new input (B16:F18 cells) to Table2.
- Subsequently, right-click and choose the Refresh option.
You’ll get that Pivot Tables are updated automatically within seconds.
More importantly, if you click on the Slicer, you’ll find that it works for all Pivot Tables at the same time.
Download Practice Workbook
That’s the end of today’s session. I firmly believe this article would be highly beneficial for you to understand why Report Connections Slicer is not showing all Pivot Tables with the solutions. Anyway, don’t forget to share your thoughts.