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.
Download Practice Workbook
How 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 Number of Visits 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.
Let’s get started.
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.
- 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.
- 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 number of visits.
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.
- Insert 5000 in the first blank space of the Custom AutoFilter dialog box, and then 10000 in the second space. Finally, press OK.
- 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.
And, you’ll get the following result for your query.
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.
And you’ll see the following output.
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.
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.
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.
And, you’ll get the following output.
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.