One of the more common tasks in Excel is filtering data. Probably in every aspect of your Excel use you are using the filter, filtering can be done with single items as well as multiple items. Today we are going to show you how to search multiple items in the filter.
First things first, let’s get to know about today’s example dataset.
We have a simple table having a few random persons from different countries and their respective favorite sports and favorite player. Using this dataset we will filter with different items.
Note that it is a basic dataset, in the real-life scenario you may encounter many complex and large datasets.
You are welcome to download the practice workbook from the link below.
Search Multiple Items in Excel Filter
1. Using Basic Filter Option
The basic filter option can be useful to search multiple items. Let’s explore this tool.
I. Using Filter Directly
We can use the basic filter option directly to search multiple items. You will find this Filter option in the Sort & Filter section from the Data tab.
First, select the range of data you want to use the filter, and then click the Filter.
You will find the filter icon at the bottom corner of the column’s headers.
Now we need to click on any of the filter icons, for which we want to filter our data. For example, we are going with the Country column.
All the countries’ names will be visible. Since our agenda is to use several items for filtering, we will select a few countries from there.
First of all, select a country. Here we have selected Australia.
One item has been selected, now we need to select a couple of items more (since we are going to search multiple items).
Here we have selected Canada and the US. You can prefer yours. Now click OK.
Only the data from these three countries have been in front of us.
We have filtered our dataset with multiple items (countries). Not only within a single column but also for multiple columns we can perform our search.
In our example, now we are going to filter through the Favorite Sports columns. You need to click the filter icon from that column.
Now select any of the options from there, here we are selecting Football and Tennis.
Here we will find the filtered data.
II. Using a Helper Column for Filtering
In the earlier section, we have directly used the Filter option. Now we are going to see how to filter using a helper column.
Here we have to list the items first which we want to find within our dataset.
We have listed three countries separately from our dataset. And introduced a helper column.
We will fill this Helper (Helping) Column using a formula formed by the COUNTIF function. COUNTIF counts the cells with criteria. To know more about the function, visit this COUNTIF article.
The formula will be
H4:H6 is the range reference for our searching counties, and C4 is the first cell from the Country column.
We have found the instance number of the country (the US) in the searching country list.
Exercise Excel AutoFill to fulfill the Helper (Helping) Column. Where countries matched we found 1 otherwise 0.
Now use the Filter option on the Helper (Helping) Column and select 1 from there.
Here we have found the data from our desired countries.
2. Using Advanced Filter Option
We can use the Advanced Filter option to search multiple items. Let’s explore the method.
I. Multiple Values for Single Column
We can search multiple values in a single column. Here we have listed a couple of sports.
Make sure the search column has the same name as the original column. Now, click the Advanced Filter from the Sort & Filter option in the Data tab.
An Advanced Filter dialog box will pop up in front of you.
Then you need to select the List range and the Criteria range.
Here we have selected our dataset into the List range and the search column into the Criteria range. Now click OK.
Our searched sports were Football and Cricket. And we have found only these sports in our dataset.
II. Multiple Values for Multiple Columns
We can use multiple columns while searching values. In our example, we are going to filter from the Country and the Favorite Sports.
Here we have taken the US and India for searching within the Country column and Football and Cricket for the Favorite Sports column.
Now exercise the Advanced Filter and set the ranges to the respective fields.
Here we have selected our dataset into the List range and the search columns into the Criteria range. Now click OK.
We have found the dataset concerning the values from our search items.
Note that when we have used multiple columns, the filtering process will assume them as individual rows.
That’s all for the session. We have listed a couple of ways to search multiple items in the Excel filter. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. Notify us of the approaches which we might have missed here.