Single Excel pivot table slicer for multiple fields

Ben Turner
Ben Turner used Ask the Experts™
on
Is it possible to have a slicer that will select all records with a particular value in a range of columns?

For example, suppose I have a table of students and their subject preferences. The pivot table might show the number of students at each school. Can I then have a slicer that will show me just the students who have chosen Math as any of their preferences?
Example
My actual file has many more columns, rows, and options for subject, but this example illustrates what I'm trying to do.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
It's easy for us to get your example file.
Hi Ben,

You should add helper column data to find out what are you searching for, since your data are in multiple columns and filter/slicer can be applied to one column only. See the attached example i made for you. Change the filter value from L1 Cell then Refresh the pivot table to get fresh data.

Br,
Abbas Abdulla
EE-Slicer-PVT-Helper-Column.xlsx
Ben TurnerData Analyst
Commented:
Hi Abbas Abdulla,

I did come up with something similar to your method, although I didn't use a table.
Example.xlsx

The actual file I want to use this for has over 400,000 rows, and is about 200MB, so I wanted to have the pivot tables in a different file to the data table. Using the helper column method means I have to have both files open to do the calculation, which is a bit slow, but I will probably just have to put up with that. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial