Bottom line: Learn how to create a list of the (Multiple Items) that are filtered for in the pivot table Fields area.
Skill level: Beginner
Video: 3 Ways to Display (Multiple Items) Filter Criteria
Download the sample Excel file to follow along.
The (Multiple Items) Dilemma
The Filters area of the pivot table allows us to apply a filter to the entire pivot table. This is a great way to filter the report to only see data for certain time periods, categories, regions, etc.
We can check the Select Multiple Items box in the filter drop down menu to filter the pivot table for multiple items in the field. This is a really powerful feature.
However, 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 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.
This is time consuming, and can also cause confusion for readers and users of our Excel files.
3 Ways to Display the Filter Criteria on the Worksheet
Even though there is no built-in way to display the filter list, I have 3 simple workarounds that can be implemented pretty quickly.
It's important to note that these solutions are additive. That means in order for solution #3 to work, we will need to implement solutions #1 and #2 first. Read on and you will see what I mean.
Solution #1 – Add a Slicer to the Pivot Table
The quickest way to see a list of the Multiple Items in the filter is to add a slicer to the pivot table.
- Select any cell in the pivot table.
- Select the Analyze/Options tab in the ribbon.
- Click the Insert Slicer button.
- Check the box for the field that is in the Filters area with the filter applied to it.
- Press OK.
A slicer will be added to the worksheet. The items that are selected in the filter drop-down list will also be selected/highlighted in the slicer. These two controls work interchangeably, and we can use both the slicer and the filter drop-down menu to apply filters to the pivot table.
The slicer is a great solution if you only have a few items in the filter list. If you have dozens or hundreds of items in the filter list, then the user is required to scroll horizontally through the slicer to see the selected items. So, it's not the best solution for long filter lists.
Solution #2 – Add a Connected Pivot Table
We can list out all of the selected filter items in cells on the worksheet with another pivot table. Here is a quick guide of the steps to create the connected pivot table. Please watch the video above for further instructions.
It's important to note that we still need the slicer created in Solution #1 for this to work.
- Select the entire pivot table.
- Copy and paste it to a blank area in the worksheet.
- In the new pivot table, move the field in the Filters area to the Rows area.
- Remove all other fields in the pivot table so there is only one field in the Rows area.
- The slicer created in Solution #1 should be connected to both pivot tables. If not, right-click the slicer > Report/Pivot Table Connections, and check the boxes for both pivot tables on this sheet.
This new pivot table will display a list of the items that are filtered for in the first pivot table. As filters are applied to the Filters area of the first pivot table, the second pivot table automatically updates to display the filter items. This happens because both pivot tables are connected by the slicer. Pretty cool stuff! 🙂
This solution allows us to create formulas based on the list of applied filter items in the pivot table. We can use this in all types of scenarios for creating interactive reports, dashboards and financial models. The possibilities are endless. Solution #3 is an example of how to use the results in a formula.
Solution #3 – Create a Comma Separated List of Filter Items
The list of filter items can also be joined into one list of comma separated values in one cell. This is nice if you want to display the list right next to the pivot table.
We can easily create this list with the new TEXTJOIN function that was introduced in Excel 2016. If you don't have Excel 2016 or Office 365 yet, then you can also do this with the CONCATENATE function. It's just more work to setup.
Again, for this to work we will need to implement solutions #1 and #2 first. Here are the steps. Checkout the video above for more details.
- Type =TEXTJOIN( in the cell where you want to display the list.
- TEXTJOIN has 3 arguments. The first argument is the delimiter or separator between each cell value. We can put just about anything we want in here. We just have to wrap the delimiter in quotation marks. To separate the values with commas, put a comma followed by a space in the argument: “, ” Then type a comma.
- The 2nd argument is the ignore_empty option. This allows us to ignore empty cells and requires a TRUE/FALSE value. We will select TRUE to ignore any empty cells. That means empty cells will not be added to our list.
- The 3rd argument is the text. For this argument we can reference a range of cells. In this case we will reference the entire column of the second pivot table in Solution #2. Since the TEXTJOIN function is going to ignore empty cells, we can reference the entire column. The filter list will grow/shrink depending on how many filter items are selected. This makes the output of TEXTJOIN dynamic, without having to create a dynamic named range.
- Close the parenthesis on the formula and hit Enter to see the results.
- The list will also contain the header label of the Rows area of the pivot table. We can remove this by turning off the Field Headers. This is a toggle button on the Analyze/Options tab of the ribbon in the Show section.
There are a lot of options with this solution. We can change the delimiter to a different character besides a comma. We can even use the line break character CHAR(10) to list each item on a new line in the same cell. Just apply Wrap Text to the cell.
Another option for the delimiter is the pipe character. ” | “
What if I don't have TEXTJOIN?
If you aren't using Excel 2016 or Office 365 yet, then you can create this formula with the CONCATENATE function. It is just more work to setup. However, I have a free macro that creates the CONCATENATE formula for you, including the delimiter character.
Multiple Ways to List Multiple Items
Well, there are 3 ways to list and display the filter items on the worksheet. The magic here is in the slicer that allows us to create connections between pivot tables. Checkout my article on how slicers and pivot tables are connected for a detail explanation on this relationship. I also have a video on how to use slicers. You can share this with your co-workers and users that are not familiar with using slicers.
I also have a free 3-part video series on Filters in Excel that is part of my Filters 101 Course.
My free 3-part video series on pivot tables and dashboards explains more about creating interactive reports with slicers and charts.
Please leave a comment below with any questions. I'm interested to hear how you will implement these techniques in your Excel files. Thank you! 🙂
Thanks Jon, Awesome formula “Textjoin”
Thank you Muhammad. Yes, Textjoin is a very useful function that replaces the need for Concatenate.
3 Ways to Display (Multiple Items) Filter Criteria in a Pivot Table
Thanks for the video and download. Very useful and informative. I will join one of your course once I am working again and can afford it. For now I will just continue to use what is available free of charge.
Thank you Brenda! I appreciate your support and look forward to having you join us in one of the courses. 🙂
Many thanks for sharing Jon. Genius is simplicity.
Thank you Gordon!
As always, good stuff! Thank you
Thanks Teresa! 🙂
Thank you Jon, Just learning how to use slicers, and never thought about using it this way! Always appreciate your simple examples!
Awesome! Thanks Lisa! There are a lot of possibilities with slicers to make our worksheets interactive.
Clear and concise bit of coaching – thanks, Jon
Thank you Nigel! 🙂
Excellent presentation. I particularly liked the SLICER option and will definitely being using it with my Pivot tables. Thank you
Awesome! I’m happy to hear you will be putting it to good use. Thanks Nancy!
I know this isn’t a forum but the solution I need is that if you set a multiple item filter for something like document number B- to get all docs that begin with B- and you refresh your data to add new doc’s that filter is static and does not dynamically select or include the new data. Thanks in advance for any replies!
Great question! You could apply the filter in the Rows area of the Connected Pivot table for this case. The Rows area filters allow us to apply Label Filters for criteria like (Begins With, End With, Contains, etc.). This filter criteria will be reapplied after new data is added and the pivot table is refreshed.
If users are filtering in the Filters area, you could probably figure out a way to hide the filters area row, and have them apply filters in the Row label filter drop-down menu of the connected pivot table instead. I hope that makes sense.
You’re killing me Jon… So much you can do with this that I had no idea of… Can’t thank you enough for all that you’re helping me with..!
Thanks Eddie! Yeah, there are a lot of possibilities here. Feel free to post a comment here if you find new uses for this technique.
Hi Jon. Thanks for such a helpful tutorial! I would like your input on an issue that may be related to this tutorial (or it might not be, I’m not really sure). For example, I would like to make a data placemat/dashboard that contains a mixture of data about different schools. I would like to target specific data about one school (e.g. # of graduates) for some areas of the placemat, but in other parts of the placemat I would like to compare this school’s data with other schools of my choosing (filter). For example I would like to report the total number of graduates just for the school of interest, but would like to compare the graduation rate of this school to other schools in the region. Out of the total list of schools (which could be over 40) I would like to be able to tease out a few schools to compare the school of interest with on the placemat, but still have that particular school’s data highlighted on other areas on the placemat. I’m thinking having a list of the different filtered schools might be necessary to do this, but how would you go about doing this? Optimally the target school’s data would be highlighted in blue on the comparison graphs, with the other filtered school’s data in grey so it sticks out.
To combine #2 and #3, we may put the field into Column label instead, provided that there are not too many items to be selected.
Great suggestion! Thanks MF 🙂
Some great learnings in that video. Thanks Jon. Can you select the filter values from within the slicer?
Thanks for the nice feedback. I’m not sure I understand your question. If you are referring to selecting or copying the text of each slicer item, you cannot do that in the Excel App. You can do it with a macro by looping through the visible pivot items in the pivot field. I hope that helps.
Can I get the vba code for copying the text of the filtered Item?
I am doing job in finance, Thank you so much sir for sharing such informative video, I learnt from this video, very nice video. And I hope this process will continuous.
Some great tips here, I wonder if you can help on an issue I have with pivot tables?
I have one set of data, and would like to filter down so that each filter’s results are ‘affected/refreshed’ by the preceding filter choice?
However, I am finding that the data under each filter’s drop box is showing the full data list, not the filtered selection? I have tried Option 2 above but this does not solve my problem?
Any help gratefully received!
I have the same problem. Did you get any solution? If yes, then please do share.
Thanks in advance
At work every month i get a list of campaign IDs that i need to manually add into a pivot table to include in the filter 1 by 1. is there any way that i can add a whole list of new ID’s at once and have them all added to the filter?
I am looking for some help / suggestions, post multiple selections through slicers, I now have a pivot table with the precise list. However, when I select the drop down arrow to select the attribute, I get to the complete list instead of the filtered list based on my selections through slicers. Is there a way, the drop down can be restricted to the list of values based on selection of slicers only ?
Unfortunately the list in the Filter Drop-down menu cannot be modified. It will always contain a list of all unique items in the pivot field.
Hi Jon. You are getting much closer to what I need to do but it’s not there yet.
I have a pivot table with approx 200 customers and 1000 SKU Item#
I regularly need to look at a list of let’s say 10 – 30 SKU and see who bought them, but this list varies.
For example, I might have 20 different types of widget (so 20 different SKU). I can generate that list easily from a different Excel sheet using Sort or Heading Filters. At the moment I have to go into the filter and check the 20 different boxes one by one.
So, I guess, what I am looking to do is take (copy/paste) my list and drop it somewhere so that the Pivot Table filters using that list.
I hope that makes sense.
I’m having trouble with the filter I created in my pivot table. I’m trying to sort my data by finished item id and also component id. I only want the related component id’s to show when I select a certain finished item id in my filter. For example, finished item xyz contains component id’s 1 and 2. If I have a list of 20 different finished item id’s and 40 some component id’s that are related to the finished id’s, how can I sort the information and only have it show the component id’s that are associated with the finished id that I’ve filtered? I hope that makes sense??
Cool, thanks. Just what I needed to help call out filters that I have applied via a slicer. Thanks for taking the time to share your knowledge. Very clear and concise in an easy to digest format.
I have a list of 20 discounts and 6 companies. Each of the 6 companies offer some but not all of the list of 20 discounts. What I want to be able to do is pick the company, and show the list of discounts that company offers. Sound simple, but my brain has a hard time understanding what is being shown here, and how to adapt it to my need. Can you assist ?
I need the formula in Excel for Creating a Comma Separated List of Filter Items as shown in Solution # 3, but not in Pivot table. Plz help me.
Thanks for the detailed video.
Today , I have looked this solution and got it from your website.
Nice work bro.
I am NOT an Excel expert, so please bear with me if I am using incorrect nomenclature and appear to be a novice, I am.
Would love to send you the file I am working on…..not sure what your email address is for that….
In my workbook, I have a Data sheet, and several Pivot tables, on individual worksheets made from the one data table ( I have created random numbers for this test Bed file). I need to be able to send it to multiple sales folks and make it so that they only can see “their” data. One of the fields in the data is “Salesman Code”. I know I can hide the sheet with the data. However they each would know all of the “Salesman Codes”. So I am looking to figure out a way to make it that each sales person can only see the data filtered with their code.
Would I have to just create a separate Workbook for each Salesperson? If so, since I will be adding sales data on a monthly basis, would it be possible to update each of the data tables automatically from a Source data table that contained the data for all Salesman Codes? If not, this would be an arduous manual task.
Also, on the sheet titled “XTL” I want to have a couple columns of calculated values. One of them will be the average monthly sales for the months of Sep, Oct and Nov 2019. The second calculated column would be the average sales for the three months prior to the current date (last 3 rolling months average). The next columns would then be the sales data for the “current” months (Jan – Dec 2020)
It would look like:
Code Monthly Avg (Sep-Nov 2019) Monthly Avg (Last 3 rolling months) Jan 2020 Feb 2020 Mar 2020 Apr 2020 May 2020 Jun 2020 Jul 2020 Aug 2020 Sep 2020 Oct 2020 Nov 2020 Dec 2020
UM 345 378 361 401 399 412 Etc
Wow. Is this great or what….. Thanks for this info. Appreciate it.
Thank you for the detailed video, i was looking for ways to display filter selections, so happy to find your video! thanks a lot, very helpful!
Thanks was very helpful, and well explained
Is there a way when you enter a name in the “TEXTJOIN” field, the name will appear to the slicer?