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

3 ways to display a list of Multiple Items in a Pivot Table Filter

Video: 3 Ways to Display (Multiple Items) Filter Criteria

Watch in full screen HD or on YouTube.

Download File

Download the sample Excel file to follow along.

Display Multiple Items In Pivot Table Filter.xlsx (100.6 KB)

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.

Multiple Items - No Built-in way to see selected filters

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.

  1. Select any cell in the pivot table.
  2. Select the Analyze/Options tab in the ribbon.
  3. Click the Insert Slicer button.
  4. Check the box for the field that is in the Filters area with the filter applied to it.
  5. Press OK.
Solution 1 - Insert a Slicer for the Filter Area Field
Click to Enlarge

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.

Filter Drop-down Menu and Slicer display same list of selected filter items

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.

  1. Select the entire pivot table.
  2. Copy and paste it to a blank area in the worksheet.
  3. In the new pivot table, move the field in the Filters area to the Rows area.
  4. Remove all other fields in the pivot table so there is only one field in the Rows area.
  5. 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.
Solution 2 - Copy the pivot table and put the filters field in the rows area
Click to Enlarge

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.

Create comma separated list of filter items with the TEXTJOIN function

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.

  1. Type =TEXTJOIN( in the cell where you want to display the list.
  2. 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.
  3. 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.
  4. 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.
  5. Close the parenthesis on the formula and hit Enter to see the results.
  6. 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. ” | ”

Use the Pipe Character for the delimeter in TEXTJOIN

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! 🙂

33 comments

Your email address will not be published. Required fields are marked *

  • 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 ?

  • 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’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??

  • 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.

  • Hi Jon,
    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 ?

    Thanks,
    Chidu

  • Hi Jon,
    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?

  • Hi Jon,

    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!

    Many thanks
    Mark

  • 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.
    Thanks

    • Hi Neil,
      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.

  • Hi Jon,
    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.
    Cheers, 🙂

  • 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.

  • 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..!

  • 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!

    • Hi Dan,
      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.

  • Excellent presentation. I particularly liked the SLICER option and will definitely being using it with my Pivot tables. Thank you

  • Thank you Jon, Just learning how to use slicers, and never thought about using it this way! Always appreciate your simple examples!

  • Hi Jon

    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.

    Kind regards

    Brenda

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar