Filtering Multiple PivotTables Simultaneously

When creating complex reports and, especially, dashboards in Microsoft Excel, it is very often necessary to simultaneously filter several pivot tables at once. Let’s see how this can be implemented.

Method 1: General Slicer for filtering pivots on the same data source

If the pivots are built on the basis of one source data table, then the easiest way is to use them to filter them simultaneously section is a graphic button filter connected to all pivot tables at once.

To add it, select any cell in one of the summary and on the tab Analysis select team Paste Slice (Analyze — Insert slicer). In the window that opens, check the boxes for the columns by which you want to filter data and click OK:

The created slicer will, by default, filter only the pivot for which it was created. However, using the button Report Connections (Report connections) tab Slice (Slices) we can easily add other summary tables to the list of filtered tables:

Method 2. General slice for filtering summaries on different sources

If your pivots were built not according to one, but according to different source data tables, then the above method will not work, because in the window Report Connections only those summaries that were built from the same source are displayed.

However, you can easily get around this limitation if you use the Data Model (we discussed it in detail in this article). If we load our tables into the Model and link them there, then the filtering will apply to both tables at the same time.

Let’s say that we have two tables for sales and transportation costs as input data:

Suppose that we are faced with the task of building our own summary for each of them and then filtering them simultaneously by cities with a common cut.

We do the following:

1. Turning Our Original Tables into Dynamic Smart Tables with a Keyboard Shortcut Ctrl+T or commands Home – Format as a table (Home — Format as Table) and give them names tablProdaji и tabTransport tab Constructor (Design).

2. Load both tables in turn into the Model using the button Add to Data Model on the Power Pivot tab.

It will not be possible to directly link these tables in the Model, because while Power Pivot only supports one-to-many relationships, i.e. requires one of the tables to have no duplicates in the column we are linking on. We have the same in both tables in the field City there are repetitions. So we need to create another intermediate lookup table with a list of unique city names from both tables. The easiest way to do this is with the Power Query add-in functionality, which has been built into Excel since the 2016 version (and for Excel 2010-2013 it is downloaded for free from the Microsoft website).

3. Having selected any cell inside the “smart” table, we load them one by one in Power Query with the button From table/range tab Data (Data — From table/range) and then in the Power Query window select on The main teams Close and load – Close and load in (Home — Close&Load — Close&Load to…) and import option Just create a connection (Only create connection):

4. We join both tables into one with the command Data – Combine Queries – Add (Data — Combine queries — Append). Columns with the same names in the header will fit under each other (like a column City), and those that do not match will be placed in different columns (but this is not important for us).

5. Delete all columns except column Cityby right-clicking on its title and selecting the command Delete other columns (Remove other columns) and then remove all duplicate city names by right-clicking the column heading again and selecting the command Remove Duplicates (Remove duplicates):

6. The created reference list is uploaded to the Data Model via Home — Close and load — Close and load in (Home — Close&Load — Close&Load to…) and choose the option Just create a connection (Only create connection) and the most important thing! – turn on the checkbox Add this data to the data model (Add this data to Data Model):

7. Now we can, returning to the Power Pivot window (tab PowerPivot – button Management), switch to Chart View (Diagram view) and link our tables of sales and transportation costs through the created intermediate directory of cities (by dragging fields between tables):

8. Now you can create all the required pivot tables for the created model using the button summary table (Pivot Table) on The main (Home) tab in the Power Pivot window and, by selecting any cell in any pivot, on the tab Analysis add slice button Paste Slice (Analyze — Insert Slicer) and choose to slice in the list box City in the added directory:

Now, by clicking on the familiar button Report Connections on Slice tab (Slicer — Report connections) we will see all our summary, because they are now built on related source tables. It remains to enable the missing checkboxes and click on OK – and our slicer will start filtering all selected pivot tables at the same time.