How To Apply A Filter In Excel

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

In this commodity, 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 Do Workbook

How to Utilise Multiple Filters in Excel

Before going to the primary analysis, permit'south have a glimpse at the post-obit 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 style of Platforms.

Now we'll see the application of multiple filters regarding different perspectives.

Permit'south get started.

one. Multiple Filters in a Uncomplicated Way

You tin easily organize your required information utilizing the Filter pick in Excel.

For case, if you want to become 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. And then open up the Filter choice. You tin open the option in ii ways. The commencement one is by clicking the Home tab>Filter pick (from the Sort & Filter command bar). The other one is from the Data tab>Filter option.

After that, y'all'll see the drop-downwards arrow for each field. Now, you have to filter your desired data. First, select the "category of the sites" field. Now, uncheck the box shut to Select All to deselect all the data options. So, check the box close to "education". Later, press OK.

Once again, click on the "Platforms" field and check the box close to the "Mobile" platform in the earlier fashion.

After filtering the two fields, you lot'll become the following number of visits.

ii. Multiple Filters Using the AutoFilter Option

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

If you want to find the "Name of the sites" having the number of visits betwixt 5000 and 10000, and the "New subscribers" are greater than 200, you can do that by the post-obit manner.

Firstly, select the dataset and choose the Filter option. You can employ an effective shortcut i.e. CTRL+SHIFT+L.
Click on the driblet-down arrow of the "Number of Visits" field. So, choose the Between option.

Insert 5000 in the kickoff blank space of the Custom AutoFilter dialog box, and then 10000 in the 2nd infinite. Finally, press OK.

Again, open the dialog box for the "New subscribers" in the before fashion except you have to choose Greater than the option. Therefore, fill the infinite by typing 200.

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

iii. 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 instance, you may specify the three criteria i.e. category of the sites would exist education, the number of the visits would be greater than 10000, and the number of the new subscribers would exist greater than 400.

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

So open the Advanced Filter choice past clicking Information tab>Filter>Advanced

Later on, 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 simply.

And yous'll see the following output.

Like Readings:

Filter Multiple Criteria in Excel (4 Suitable Means)
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 fourth dimension-consuming and a little scrap boring to get the required result using a formula.

Rather you tin can employ the VBA code in Excel which performs the outcome rapidly and accurately.

At present, let's run across how you lot can apply the VBA code in our dataset.

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

Here, we'll run into the ii applications of VBA AutoFilter using OR operator and AND operator respectively.

Following things are necessary using the VBA AutoFilter.

Range: Information technology refers to the prison cell range to filter east.1000. 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.thou. Criteria1="<10000"
Criteria ii: The second criteria for a field e.g. Criteria2=">15000"
Operator: An Excel operator that specifies sure filtering requirements eastward.g. Operator:=xlOr, Operator:=xlAnd, etc.

i. VBA Code for Multiple Filters Using OR Operator (Logic)

If you lot guys desire to filter the sites having the number of visits less than 10000 or greater than 15000, and the category of the sites would exist educational activity, then you can follow the following code.

          Sub filter_sites() Dim range_to_filter As Range  Ready 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 yous run the above code, you'll get the following output.

2. VBA Lawmaking for Multiple Filters Using AND Operator (Logic)

More importantly, if you want to become the educational sites having the number of visits between 5000 and 15000, you may use the post-obit 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.

five. An Effective Culling: Multiple Filters Using FILTER Function

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

That's why Microsoft brings an updated FILTER function which updates the filtered information 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 desire to get the name of sites, the number of visits, etc. for June.

In that example, the formula volition be-

=FILTER(B5:F19,MONTH(D5:D19) > 5,"No data")

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

And, you'll get the following output.

Decision

This is how you can apply the multiple filters in Excel. If you have whatsoever suggestions or defoliation, delight let me know in the following comments section.

Thanks for being with me.