3 Ways To Display Multiple Items In A Pivot Table Filter

In this video we look at 3 ways to list or display the (Multiple Items) that are selected in the Filters area of a pivot table.

When we filter for more than one item, the cell that contains the filter drop-down menu displays the phrase (Multiple Items). There is no built-in way to see what items the pivot table is being filtered for unless we open the filter drop-down menu and scroll through the list.

However, there are a few simple ways to display this list on the worksheet. We can use a slicer, connected pivot table, or simple formula using TEXTJOIN or CONCATENATE to display the filter list. Each solution is pretty easy to implement.

Related videos:

00:00 Introduction
01:27 Add a Slicer
02:34 Connected Pivot Table
06:08 Text Join

[douglasbrown]

I reviewed this video again and it has proved to be very valuable. Thank you, Jon, for all your fine Excel training. I'm an advanced Excel user, but still learn a lot from your quality video trainings. Thanks much!

[TaraL-ojkj]

I had to take an Excel test during an interview. They heavily use PivotTables. I am so glad I found your channel! Thank you for presenting the information so clear and concise. I will be a frequent viewer.

[ej]

Very much appreciated that you mention that certain outcomes are additive which not only shows all options but emphasizes that one is needed for the other to work

[krismaly]

I enjoyed watching this video and recommend others.

The host explains all the three types of Display
Slicer
List of values
Comma Separated list

Also, I noticed that you were telling the audience TEXTJOIN function in Excel 2016 that being said that I would suggest that whenever you make video try to explain in the beginning that what version of Excel that your video would be helpful. Since you are addressing a large audience on the internet. And everybody may not have the your version.


Thanks for educating the community and appreciate your effort in teaching.

Thanks a bunch

[pricesetshedi]

I loved this, it has added value to making my sheets more dynamic

[onlineadminstrationofasses]

Wonderful! Just what I was looking for!

[peterbarron]

Great training Jon, thanks! You can also right-click the slicer and choose the option to move unselected items to the bottom so your selected items will always be at the top (although for really long lists your joined text list works better visually). Thanks!

[shiningcopper]

it is such a powerful tip. Like the video.

[robpeacock]

So pleased I stumbled across this as I have needed this ability for a long time! Thx a stack, Jon!

[douglasbrown]

Excellent video! I had wondered if there was a way to list out multiple entries while using a pivot table. I will be using this a lot going forward. Thank you again!

[sasavienne]

Excellent. Useful ideas. 👍 🌟 🌟 Thanks a lot..

[PedroCabraldaCamara]

Jon, another awesome video. I was waiting for this solution for so many years....FANTASTIC!! Keep up the great work guys!! Tks a lot

[PERUTICA]

Excellent video, thanks again for sharing your knowledge with us

[RichardGetzPhotography]

Great tutorial, thanks!! Can we do the opposite? That is, generate a list of values that the pivot will filter by?
I have 100s of items in the pivot that I want to filter multiple items. I can paste each value in the filter dropdown and check the box, but that seems slow. Is there a way I can provide the pivot with a list and tell it to 'filter by this list'?

[ryanashong]

Awesome, thanks for the video, learned alot about pivot tables from 1, 2, 3 videos.

[gbpalekar]

Very interesting and knowledge gathering video. Thanks... thanks

[irviantantohadiprasetyo]

Thank you very much....it's awesome

[zhd]

Very informative straight to the point and very easy to understand! Thank you very much for sharing. Much appreciated :)

[OzzyMatte]

great stuff, i don't like it, i love it mate.

[utdkidswifeITO]

Nice Tutorial! Is there a way to filter all connected pivot tables by using contains, or starts with etc, in one of the pivot table filters and be reflected in the other pivot and the slicer?