Excel multiple filters

Hi,

From the language, i can guess that towards the right-hand side of the first dataset, there is another dataset and you wish to filter both datasets. If this is the case, then convert both ranges into Tables (one by one). Now you will be able to filter bot tables independently.

Hello Martina B,

Hope you are doing great and well today.

My name is Sarah, I'd be happy to assist you today.

With regards to your concern above, I will help you to get rid of the error from your device.

Filtering becomes indispensable when you have a larger and complicated dataset. Retrieving the desired data is quite time-consuming from such a dataset. Using filtering tools in Excel, you can easily find your data based on your requirement. The methods of multiple filters are especially amazing to display your interested data.

In this article, I’ll discuss the methods of using multiple filters including VBA code. Lastly, I’ll show the FILTER function that filters smartly and updates data automatically.

1. Multiple Filters in a Simple Way 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

For this, follow the below steps.

Firstly, select your dataset. Then open the Filter option. You can open the option in two ways. The first one is by clicking the Home tab>Filter option (from the Sort & Filter command bar). The other one is from the Data tab>Filter option. Multiple Filter in a Simple Way

After that, you’ll see the drop-down arrow for each field. Now, you have to filter your desired data. First, select the “category of the sites” field. Now, uncheck the box close to Select All to deselect all the data options. Then, check the box close to “education”. Later, press OK. Multiple Filter in a Simple Way

Again, click on the “Platforms” field and check the box close to the “Mobile” platform in the earlier way. Multiple Filter in a Simple Way After filtering the two fields, you’ll get the following number of visits.

Multiple Filter in a Simple Way 2. Multiple Filters Using the AutoFilter Option AutoFilter option in excel is used as an embedded button to filter out various types of required data in a data range or column.

If you want to find the “Name of the sites” having the number of visits between 5000 and 10000, and the “New subscribers” are greater than 200, you can do that by the following way.

Firstly, select the dataset and choose the Filter option. You can use an effective shortcut i.e. CTRL+SHIFT+L. Click on the drop-down arrow of the “Number of Visits” field. Then, choose the Between option. AutoFilter Option for Multiple Filters

Insert 5000 in the first blank space of the Custom AutoFilter dialog box, and then 10000 in the second space. Finally, press OK. AutoFilter Option for Multiple Filters

Again, open the dialog box for the “New subscribers” in the earlier way except you have to choose Greater than the option. Therefore, fill the space by typing 200. AutoFilter Option for Multiple Filters

And, you’ll get the following result for your query.

AutoFilter Option for Multiple Filters

3. Multiple Filters Using Advanced Filter Option In the previous two methods, you see the application of multiple filters separately for each field. You had no option to provide criteria.

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 the visits would be greater than 10000, and the number of the new subscribers would be greater than 400.

Now, write down the above criteria regarding their fields. I have written those criteria in the cell range of H4:J5.

Then open the Advanced Filter option by clicking Data tab>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.

Advanced Filter option

And you’ll see the following output.

Advanced Filter option output Similar Readings:

Filter Multiple Criteria in Excel (4 Suitable Ways) Filter Data in Excel using Formula How to Filter Multiple Columns Simultaneously in Excel (3 Ways) Search Multiple Items in Excel Filter (2 Ways) 4. Multiple Filters Using VBA Code 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 in our dataset.

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

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

Following things are necessary using the VBA AutoFilter.

Range: It refers to the cell range to filter e.g. B4:G19. Field: It is the index of the column number from the leftmost part of your dataset. The value of the first field will be 1. Criteria 1: The first criteria for a field e.g. Criteria1=”<10000” Criteria 2: The second criteria for a field e.g. Criteria2=”>15000” Operator: An Excel operator that specifies certain filtering requirements e.g. Operator:=xlOr, Operator:=xlAnd, etc. i. VBA Code for Multiple Filters Using OR Operator (Logic) If you guys want to filter the sites having the 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 code.

Sub filter_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 If you run the above code, you’ll get the following output.

VBA OR Operator for Multiple Filters

ii. VBA Code for Multiple Filters Using AND Operator (Logic) More importantly, if you want to get the educational sites having the number of visits between 5000 and 15000, you may use the following code.

Sub filter_sites() 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 And, you’ll get the following output.

VBA AND Operator for Multiple Filters

5. An Effective Alternative: Multiple Filters Using FILTER Function The first 3 discussed methods are quite functional though they have a serious drawback. 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.

The syntax of the function is

FILTER (array, include, [if_empty]) 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 if 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, the formula will be-

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

FILTER Function for Multiple Filters

And, you’ll get the following output.

After Filtering Using FILTER function Conclusion This is how you can apply the multiple filters in Excel. If you have any suggestions or confusion, please let me know in the following comments section.

Thanks for being with me.

If ever doesn't work I encourage you to contact our live person Microsoft Office Support to give you better remote assistance. Please see the link to connect with us: https://support.microsoft.com/en-us/microsoft-365

If you have any concern or clarification. Please comment below for more information.

Best regards,

Sarah

Hello Martina B,

A good day to you and hope you are doing great.

My name is Arielle Martina, I'd be happy to assist you today.

With regards to your above query, Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view the information by date. The Filter menu will appear. Check or uncheck the boxes depending on the data you want to filter, then click OK.

For more assistance with the live person chat support. I encourage you to contact our Microsoft Support to request it directly via remote assistance. Please see the link for more information to connect with us: https://support.microsoft.com/en-US/search/results?query=microsoft+support&isEnrichedQuery=false

If you have any concerns or clarification please reply in the below comment section. Many thanks!

Kind regards,

Arielle Martina