3 Ways to Display (Multiple Items) Filter Criteria in a Pivot Table - Excel Campus
25

3 Ways to Display (Multiple Items) Filter Criteria in a Pivot Table

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 25 comments
Mark Hallam - September 22, 2017

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

Reply
Faheem Afridi - March 25, 2017

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

Reply
Neil - March 18, 2017

Some great learnings in that video. Thanks Jon. Can you select the filter values from within the slicer?

Reply
    Jon Acampora - March 29, 2017

    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.

    Reply
MF - February 27, 2017

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

Reply
Christopher Thompson - February 27, 2017

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.

Reply
Eddie Rivera - February 24, 2017

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

Reply
    Jon Acampora - February 28, 2017

    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.

    Reply
Dan Hays - February 23, 2017

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!

Reply
    Jon Acampora - February 28, 2017

    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.

    Reply
Nancy Pereira - February 23, 2017

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

Reply
Nigel - February 22, 2017

Clear and concise bit of coaching – thanks, Jon

Reply
Lisa - February 22, 2017

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

Reply
Teresa - February 22, 2017

Hi Jon,

As always, good stuff! Thank you

Reply
Gordon Miller - February 22, 2017

Many thanks for sharing Jon. Genius is simplicity.

Reply
Brenda Walters - February 22, 2017

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

Reply
    Jon Acampora - February 28, 2017

    Thank you Brenda! I appreciate your support and look forward to having you join us in one of the courses. 🙂

    Reply
Muhammad Fathy - February 22, 2017

Thanks Jon, Awesome formula “Textjoin”

Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x