How to Apply Multiple Filters in Excel (6 Suitable Methods)

Filtering becomes indispensable when you have a larger and more complicated dataset. Retrieving the desired data is quite time-consuming from such a dataset. Thus, you should know how to apply multiple Filters in Excel. The methods of multiple Filters are especially amazing to display your interested data.

In this article, we’ll discuss the methods of how to apply multiple Filters including VBA code in Excel. Also, we’ll show the FILTER function that filters smartly and updates data automatically.

Download Practice Workbook

You can download the practice workbook from here:

6 Methods to Apply Multiple Filters in Excel

Before going to the main analysis, let’s have a glimpse at the following dataset. Here, the Names of 15 Sites are given along with their Category. Besides, the Visits Number and New Subscribers are provided based on the Date and mode of Platforms.

Now we’ll see the application of multiple filters regarding different perspectives. For conducting the session, we are using Microsoft 365 version. So, let’s get started.

1. Multiple Filters in Simple Way within Different Columns in Excel

Here, you can easily organize your required data utilizing the Filter option in Excel. For example, if you want to get the number of visits for the Educational sites and the Mobile platform, you can simply use the Filter option.

So, for this, follow the below steps.

• Firstly, select your dataset.
• Secondly, from the Home tab> click the Filter option (from the Sort & Filter command bar). Additionally, you can open the Filter option in another way. Furthermore, that one is from the Data tab> click Filter option.

After that, you’ll see the drop-down arrow for each field.

Now, you have to filter your desired data.

• First, select the “Category” field.
• Then, uncheck the box close to Select All to deselect all the data options.
• Then, check the box close to “Education”.
• Later, press OK.

• Again, click on the “Platforms” field and check the box close to the “Mobile” platform in the earlier way.

After filtering the two fields, you’ll get the following visits number.

Read More: How to Filter Multiple Columns Simultaneously in Excel (4 Ways)

2. Using AutoFilter Option to Filter Multiple Values in Excel

AutoFilter option in Excel is used as an embedded button to filter out various types of required data in a data range or column.

So, if you want to find the “Sites Name” having a visits number between 5000 and 10000, and the “New subscribers” are greater than 200, you can do that the following way.

• Firstly, select the dataset and press CTRL+SHIFT+L.

• Then, click on the drop-down arrow of the “Visits Number” field.
• After that, go to the Number Filters menu.
• Then, choose the Between option.

At this time, a new dialog box named Custom Autofilter will appear.

• Firstly, insert 5000 in the first blank space of the Custom AutoFilter dialog box.
• Secondly, write 10000 in the second space.
• Finally, press OK.

As a result, you will see the filtered Visits number.

• Similarly, click on the drop-down arrow of the “New Subscribers” field.
• Then, go to the Number Filters menu.
• After that, choose the Greater Than option.

Similarly, the dialog box named Custom Autofilter for “New subscribers” opens.

• Then, fill the space by typing 200.
• Subsequently, press OK.

And you’ll get the following result for your query. So, we thought it is clear to you how to apply multiple Filters in Excel.

Read More: Excel VBA: Filter Table Based on Cell Value (6 Easy Methods)

3. Filters Multiple Columns Simultaneously Using Advanced Filter Feature

In the previous two methods, you see the application of multiple filters separately for each field. Moreover, you had no option to provide criteria.

Actually, using the Advanced Filter option, you can specify criteria for the fields.

For example, you may specify the three criteria i.e. category of the sites would be education, the number of visits would be greater than 10000, and the number of new subscribers would be greater than 400.

• First, write down the above criteria regarding their fields. Here, we have written those criteria in the cell range of B22:D23. Actually, you must write the criteria horizontally.

• Then open the Advanced Filter option by clicking the Data tab > Sort & Filter > Advanced.

• Later, specify the range of your whole dataset from where you want to filter in the List range option and provide the criteria in the Criteria range.
• Furthermore, if you don’t need similar data, check the box close to Unique records only.
• Subsequently, press OK.

And you’ll see the following output.

Read More: Filter Different Column by Multiple Criteria in Excel VBA

Similar Readings

• How to Filter Column Based on Another Column in Excel (5 Methods)
• Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
• How to Filter Excel Pivot Table (8 Effective Ways)
• Excel VBA: Filter Based on Cell Value on Another Sheet (4 Examples)
• How to Remove Filter in Excel (5 Easy & Quick Ways)

4. Multiple Filters Employing VBA in Excel

If you have a larger dataset, it is time-consuming and a little bit boring to get the required result using a formula.

Rather you can utilize the VBA code in Excel which performs the result rapidly and accurately.

Now, let’s see how you can apply the VBA code to our dataset.

Here, we’ll see the two applications of VBA AutoFilter using OR operator and AND operator respectively.

4.1. Multiple Filters Using OR Operator (Logic)

If you guys want to filter the sites having a number of visits less than 10000 or greater than 15000, and the category of the sites would be education, then you can follow the following steps.

• Firstly, from the Developer tab > click on Visual Basic.

• Then, open a module by clicking Insert > Module.

• After that, write down the following code in Module 1.

Sub filter_my_sites()
Dim range_to_filter As Range
Set range_to_filter = Range("B4:G19")
range_to_filter.AutoFilter field:=5, Criteria1:="<10000", Criteria2:=">15000", Operator:=xlOr
range_to_filter.AutoFilter field:=2, Criteria1:="Education"
End Sub

• At this time, from the Developer tab > go to Macros.

• Then, choose filter_my_sites from the Macro name and press Run.

If you run the above code, you’ll get the following output.

4.2. Multiple Filters Using AND Operator (Logic)

More importantly, if you want to get the educational sites having a number of visits between 5000 and 15000, you may use the following code.

Sub filter_mysites_2()
Dim range_to_filter As Range
Set range_to_filter = Range("B4:G19")
range_to_filter.AutoFilter field:=5, Criteria1:=">=5000", Criteria2:="<=15000", Operator:=xlAnd
range_to_filter.AutoFilter field:=2, Criteria1:="Education"
End Sub

• After running the code, you’ll get the following output.

So, we thought it is clear to you how to apply multiple Filters in Excel using VBA.

Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)

5. Use of FILTER Function to Apply Multiple Filters

The first 3 discussed methods are quite functional though they have serious drawbacks. You cannot update the filtered data automatically. For this, you have to again the methods for filtering new data.

That’s why Microsoft brings an updated FILTER function which updates the filtered data automatically. Moreover, you will get this function only in the Excel 365 version.

The syntax of the function is

The arguments are-

• array: Range or array to filter.
• include: Boolean array, supplied as criteria.
• if_empty: Value to return when no results are returned. This is an optional field.

Furthermore, you can filter the dataset based on the date. Suppose you want to filter the whole dataset for only the month of June. That means you want to get the name of sites, the number of visits, etc. for June.

• In that case, write the formula in the H5 cell. Here, you should keep enough space for the filtered data otherwise it will show some error.

Here, B5:F19 is our dataset, D5:D19 is for the date, the syntax MONTH(D5:D19) > 5 returns the date for June.

• Then, press ENTER.

And, you’ll get the following output.

Read More: How to Add Filter in Excel (4 Methods)

6. Use of Excel Table to Apply Multiple Filters

You can use an Excel table to apply multiple filters. The steps are given below.

Steps:

• Firstly, select the data range.
• Secondly, from the Insert tab >> choose the Table feature.

At this time, a dialog box named Create Table will appear.

• Now, make sure that you have selected the data range in the Where is the data for your table? box. Here, if you select the data range before then this box will auto-fill up.
• Then, check the My table has headers option.
• Finally, press OK.

After that, you’ll see the drop-down arrow for each field.

• Then, follow the steps of method-1 and you will get the output.

Read More: How to Use Sort and Filter with Excel Table (4 Suitable Ways)

How to Filter Multiple Comma Separated Values in Excel

For this section, we will use a different data table. Which contains the Site Name, Category, Visits Number, and Platforms.

Now, if you want to get the number of visits for the Educational sites and the Mobile platform, you can follow the steps.

•  Now, select the dataset and press CTRL+SHIFT+L.

So, you’ll see the drop-down arrow for each field.

• Then, click on the drop-down arrow of the “Category” field.
• After that, go to the Text Filters menu.
• Then, choose the Contains.. option.

At this time, a new dialog box named Custom Autofilter will appear.

• At first, write Education in the first space.
• Then, press OK.

So, you will see the Category is filtered.

After that, for filtering Platforms follow the steps of method-1 and you will get the final output.

Read More: How to Filter Multiple Values in One Cell in Excel (4 Methods)

Practice Section

Now, you can practice the explained method by yourself.

Conclusion

This is how you can apply the multiple filters in Excel. If you have any suggestions or confusion, please let us know in the following comments section.

Thanks for being with us.

Related Articles