0

I have created 6 Pivot tables, with each pulling from a different worksheet within the workbook. I am wanting to be able to have a slicer that filters all Pivot tables at the same time.

Each sheet of source data has a different format, but I have created 2 columns that have the same heading on each worksheet.

The field I am wanting to be able to filter by is a list of names (ie if James Bond is selected in the slicer, only his data would appear in the pivots). I have created columns in each sheet to return only unique values of names so as to remove duplicates, yet am still not being able to create connections between the pivots.

While these names are primarily similar on each sheet, there are some that may appear on one but not others. Do I need to build a dummy column on each sheet so they all have the exact same data somewhere to be able to make a connection?

Improve this question
1
  • You will have to use either vba or Data Model as answered by Kevin, but you can not have a common slicer if the pivot data source is different. Commented Dec 30, 2023 at 15:00

1 Answer 1

Reset to default
1

You will have to recreate the PivotTables using the Data Model.

First, add all of sheets with data to the Data Model. This will turn the data on each sheet into tables. Create another table with all of the names you want to use as a filter. Add that to the Data Model too. In the Data Model, Diagram View, drag a line from the Name field in each of your 6 Data Tables to the Name field in your table with all the names. Note the direction of the arrows in the screenshot.

enter image description here

Close the Data Model. Now create your pivot tables again, but this time use Insert PivotTable from Data Model. Use the fields from Table1, Table2, etc. to build your PivotTables. Then from your new Names table, use the Name field to create a slicer. You will be able to create connections from that slicer to all of the tables.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.