How To Filter Multiple Columns In Excel

One of the more common tasks in Excel is filtering data. Probably in every aspect of your Excel employ you are using the filter, filtering can be done with single items as well as multiple items. Today we are going to evidence you how to search multiple items in the filter.

Commencement things first, let's get to know virtually today's example dataset.

Nosotros take a unproblematic table having a few random persons from different countries and their corresponding favorite sports and favorite role player. Using this dataset we will filter with different items.

Note that information technology is a basic dataset, in the real-life scenario you may encounter many complex and large datasets.

Practice Workbook

Yous are welcome to download the practice workbook from the link below.

Search Multiple Items in Excel Filter

i. 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 direct to search multiple items. You will notice this Filter option in the Sort & Filter section from the Data tab.

Outset, select the range of data you want to use the filter, and then click the Filter.

You volition find the filter icon at the lesser corner of the column'southward headers.

Now nosotros need to click on whatever of the filter icons, for which we desire to filter our data. For example, we are going with the Country cavalcade.

All the countries' names volition be visible. Since our calendar is to use several items for filtering, nosotros will select a few countries from there.

First of all, select a land. Here we have selected Australia.

One particular has been selected, now we need to select a couple of items more (since we are going to search multiple items).

Hither nosotros have selected Canada and the US. Y'all can prefer yours. Now click OK.

Only the data from these three countries have been in forepart of the states.

Nosotros have filtered our dataset with multiple items (countries). Not simply within a single column just likewise for multiple columns we can perform our search.

In our example, now nosotros are going to filter through the Favorite Sports columns. You demand to click the filter icon from that column.

At present select any of the options from there, hither we are selecting Football game and Tennis.

Hither we will observe the filtered data.

II. Using a Helper Column for Filtering

In the earlier section, nosotros have directly used the Filter selection. Now nosotros are going to see how to filter using a helper cavalcade.

Hither we have to list the items beginning which we want to find inside our dataset.

Nosotros take listed three countries separately from our dataset. And introduced a helper cavalcade.

We will fill this Helper (Helping) Column using a formula formed past the COUNTIF function. COUNTIF counts the cells with criteria. To know more about the function, visit this COUNTIF commodity.

The formula will exist

H4:H6 is the range reference for our searching counties, and C4 is the outset cell from the Country cavalcade.

We have establish the instance number of the state (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 employ the Filter option on the Helper (Helping) Column and select i from in that location.

Here we have found the data from our desired countries.

Similar Readings:

How to Apply Multiple Filters in Excel [Methods + VBA]
Filter Multiple Criteria in Excel (4 Suitable Ways)
How to Filter Multiple Columns in Excel Independently

2. Using Advanced Filter Option to Search Multiple Items

We can use the Advanced Filter selection to search multiple items. Permit's explore the method.

I. Multiple Values for Single Column

Nosotros can search multiple values in a unmarried cavalcade. Hither we have listed a couple of sports.

Make certain the search column has the same name as the original column. Now, click the Advanced Filter from the Sort & Filter pick in the Data tab.

An Advanced Filter dialog box will pop upwards in front of you.

And then you lot need to select the Listing 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 constitute only these sports in our dataset.

II. Multiple Values for Multiple Columns

Nosotros can use multiple columns while searching values. In our case, we are going to filter from the Country and the Favorite Sports.

Hither we have taken the U.s. and India for searching within the Country cavalcade and Football and Cricket for the Favorite Sports cavalcade.

Now exercise the Advanced Filter and prepare the ranges to the respective fields.

Here we have selected our dataset into the Listing range and the search columns into the Criteria range. Now click OK.

We take found the dataset apropos the values from our search items.

Annotation that when we have used multiple columns, the filtering process volition presume them as individual rows.

Conclusion

That's all for the session. We have listed a couple of ways to search multiple items in the Excel filter. Hope you will detect this helpful. Feel free to annotate if annihilation seems difficult to understand. Let us know which of the methods yous are going to utilise. Notify us of the approaches which we might have missed hither.

Farther Readings

How to Filter Cells with Formulas in Excel (2 Ways)
Filter Multiple Columns Simultaneously in Excel (3 Ways)
Excel Filter Data Based on Cell Value (6 Efficient Ways)
How to Filter Multiple Rows in Excel (11 Suitable Approaches)