2 Ways to Filter for List of Items in Excel + Video Tutorial - Excel Campus
18

2 Ways to Filter for List of Items in Excel + Video Tutorial

Bottom line: Learn 2 ways to filter for a list of multiple items in Excel in this article and video tutorial.

Skill level: Beginner

2 Ways to Filter List of Items in Excel 640x360

Video Tutorial

The following video is from The Filters 101 Course.  In this video I explain two ways to apply a filter for a list of multiple items.  These techniques use the Filter Drop-down menus in Excel.

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Sample Video from The Filters 101 Course

The video above is from The Filters 101 Course.  Filters 101 is an online video course with over 40 video lessons like the one above.

Filters 101 Course Quick Overview 2 640x360

Click here to learn more about The Filters 101 Course

Filtering for Multiple Items

A few questions come in this week from Jennifer and Justin on how to apply a filter for a list of multiple items.  This can be a time-consuming task if you try to check all the boxes for different items in the filter drop-down menus.  Especially if the filter list contains a lot of items.

Scroll Filter Drop-down List to Filter Multiple Items

We find ourselves scrolling up and down the list to check each checkbox for all items in our list.  There are a few ways to speed up this process.  Let’s take a look at two methods that can save us time.

Method #1 – Add current selection to filter

The first method for filtering for a list of items uses an option in the filter drop-down list box called “Add current selection to filter”.  As the name suggests, this feature allows us to use the Search box to search for an item, then add the selected items to the current filter criteria.

Add current selection to filter - Filter Multiple Items

This technique works best when you have 3-5 items to filter for.  Here are the steps to use the Add current selection to filter method:

  1. Use the Search box in the filter drop-down menu to search for the first item.
  2. Click OK to apply the filter.
  3. Open the filter drop-down menu again.
  4. Use the Search box (keyboard shortcut: e) to search for the second item in your filter list.
  5. Click the “Add current selection to filter” checkbox.
  6. Click OK.  The existing filter criteria will be kept, and the new item will be added to the filter criteria.
  7. Repeat steps 3 to 6 for each additional item in your filter list.

This process can be time-consuming if you have more than a few items in your filter criteria.  See the video above for further explanation on this technique.

It’s also hard to see what items have been filtered for by scrolling through the checkbox list.  One tip for this is to hover your mouse over the filter drop-down button.  A screentip will appear and display the filter criteria.

View Filter Criteria of Add current selection in screen tip

My Filter Mate Add-in will also help with viewing the filter criteria for the filter menus.  Check out my post on 7 Keyboard Shortcuts for the Filter Drop-down Menus for more time-saving tips on the filter menus.

Method #2 – Add a Column with a COUNTIF Formula

The second method is a formula based approach that works great for longer lists of filter items.

In this example, we want to apply a filter to the data table for a list of countries.  This means we only want to see the rows that contain ANY of the countries in the filter list.  We can add a column to the data table with a COUNTIF formula.

We use the COUNTIF formula to determine if the country name in the same row in column C exists in the filter list.  The filter list is the list of items that we want to filter for.  This list can be on the same sheet or a different sheet.

COUNTIF Formula to Filter List for Mutiple Items

The formula will return:

  • 1 if the country exists in the filter list
  • 0 if the country does not exist.

We now have a column of 1’s and 0’s.

Finally, we can apply a filter to the column to filter for 1.  This filter will display all the rows where the country in column C matches a country in the filter list.

Filter COUNTIFS Column to Filter for List of Items

See my article on using COUNTIF instead of VLOOKUP for more details on the COUNTIF function.

We can also add a “=1” to the end of the formula to return a TRUE or FALSE, instead of 1 or 0.

Return a Boolean Value with COUNTIF instead of Numbers

The TRUE or FALSE values will make more sense if you are using the field in a pivot table or slicer, and want to quickly apply a filter for the country list.  The TRUE/FALSE is just easier to read and understand versus the 1 or 0.  However, the outcome will be the same.  See my article on the IF function for more about this TRUE/FALSE boolean logic.

I use Excel Tables for this example.  You do NOT have to use Excel Tables for this to work.

COUNTIF to Filter for List of Items with Regular Range References

The advantage of using Tables is that we can add new items to the filter list, and we don’t have to modify the formula.  Since the formula references the entire column of the Table, there is not maintenance here.  We just need to Re-apply the filters (keyboard shortcut: Ctrl+Alt+L) after adding new items to the filter list.  This saves time and prevents errors.  Win-win! 🙂

Check out my video on A Beginner’s Guide to Excel Tables to learn more about this awesome feature of Excel.

What about Partial Matches that Contain the Filter Criteria?

Arun had a great question in the comments below about filtering for partial matches that contain the filter criteria.  We are limited to 2 criteria with the Custom AutoFilter in Excel.

However, we can use this same COUNTIFS technique with wildcard characters to filter for partial matches that contain the criteria. Here is an example of how to modify the formula.

=COUNTIF(tblFilterList[Filter List],"*"&[@Country]&"*")

The asterisk character (*) is a wildcard character that represents any value.  To use the wildcard character in the COUNTIF function, we wrap it in quotation marks and use the ampersand symbol to join it to the criteria value.

Filter for List of Partial Matches - COUNTIF formula with Wildcard character

The argument evaluates to *[@Country]*. This means that COUNTIF will look for the country name within the cell, and return a 1 if the name is found within a cell in the range.

The asterisk represents any number of characters before or after the country name. This is the same as a ‘Contains’ type criteria. We can just put the asterisk at the beginning or end if we want to find matching cells that start or end with the lookup value.

We can also use the “?” wildcard character to represent a single character.  Here is an example.

Filter for a List of Partial Matches - Wildcard Single Character COUNTIF Excel

This works great for account codes and phone numbers.

What Techniques Do You Use to Filter Multiple Items?

These are two simple techniques to filter multiple items.  With everything in Excel, there are many ways to go about this.  Do you use a different technique?  If so, please let us know by leaving a comment below.  You can also leave a comment with any questions.

I also have a free 3-part video series on Excel Filters.  In video #3 of the series, we look at applying filters to multiple columns.

Check Out The Filters 101 Course

The video above is a sample lesson from The Filters 101 Course.  This course contains over 40 video lessons just like this one.  This is step-by-step training that will help you get the most out of the sort & filter features in Excel, to prepare & analyze your data faster.

Filters 101 Course Quick Overview 2 640x360

Click here to learn more about The Filters 101 Course

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 18 comments
Edil - June 5, 2017

Thank you Jon Acampora. This filtering method with the formula is very useful to me. When I have to filter on multiple criteria within the same column, up to now I’ve scrolled down the filter list and checked the items I wanted to filter by.

Is there a way to filter a column of descriptions on multiple criteria? E.g. I have a table of payments, one column of which is the description. Whenever I swipe with my bankcard to do a payment, it appears as “Meastro [location]” or “Cashnet [location]” where [location] is the place where I used my bankcard, so it can be different names. How can I best filter in place, to get all those records with “maestro [location]” and “cashnet [location]”?

Reply
Jonathan - June 5, 2017

You can use an advanced filter to filter for a list and you can also use wildcards in the search criteria.

Reply
Chaminda Basnayake - June 2, 2017

Thank you very much Jon. I am using filters since long time but I never thought in the way you explained.It is awesome.

Reply
Phil - June 1, 2017

Great video. Good information.

Reply
Peter Taggart - June 1, 2017

Thank you so much. Learning here with you has let me know how much I did not know was available in Excel to make my day a lot easier. The material is presented in a clear and concise fashion. Thanks

Reply
Michelle - June 1, 2017

Hi, Jon!

I really enjoy your tutorials…time-friendly and well-executed. What software do you use for your video tutorials? I have a need for a presentation I will be doing.
Thanks!
Michelle

Reply
    Jon Acampora - June 1, 2017

    Thank you, Michelle! I use Camtasia to record, edit, and produce the videos. The graphics at the beginning and end of the videos are all PowerPoint slides, and I use Camtasia to record the screen. I hope your presentation goes well. 🙂

    Reply
Gerald - June 1, 2017

Thanks very much for the second method. I would never have thought of it.

Reply
Arun - June 1, 2017

Hi Jon,

Thanks, I really liked the countif method to filter. Is there an easy way to search for filtering for multiple values with ‘contains…’. Currently excel filters are giving option to search for only 2 items at a time.

Arun

Reply
    Jon Acampora - June 1, 2017

    Hi Arun,
    Great question! Yes, we can use wildcard characters with the COUNTIF technique to apply a filter if the search range contains the criteria value. I added a section above about partial matches that contain the filter criteria. Check it out and let me know if you have any questions. Thanks!

    Reply
      Arun - June 1, 2017

      Thanks Jon for adding in the section. In fact my requirement is to query in the reverse direction.

      We need to search using text (in filter list) with contains argument for items in main list. In the example given above, if we type in “Chi” (filter list), the formula should give 1 for “China” in the country list. Will be helpful if you can help on this as well. Thanks again

      Reply
Musadaq - June 1, 2017

Great …

Thanks a lot.

Reply
Juan - June 1, 2017

Thank you very much Jon for sharing these fantastic tips! I am impressed that you can see the filters applied by hovering the mouse on the column title 🙂

Reply

Leave a Reply: