Google Sheets are spreadsheets similar to Microsoft Excel and these sheets are associated with the Google Drive account. The Google Sheets are equipped with many features that make it easy for users to collect and manage the data in it.
As Google Sheets are consists of a huge amount of data and this huge data can consist of hundreds of rows and columns. We can extract the required rows and columns by using the “FILTER” function.
This blog will explain the usage of the “FILTER” function in Google Sheets to sort out data with the demonstration of some examples.
What is the General Usage Syntax of the “FILTER” function in Google Sheets?
Google Sheets allow managing the data by applying different functions. Like other functions, we can use the “FILTER” function to sort out the information. The general syntax of using the “FILTER” formula is:
=FILTER(range, condition1, [condition2, …])
The explanation of the parameters used in the above general syntax is:
- First, use the “equal (=)” sign to initialize the formula
- Define the range of the function by defining the rows and columns on which the function is supposed to apply. Multiple conditions can be defined in the function.
- Define the conditions according to which the data is supposed to be sorted.
The “FILTER” function can be applied in Google Sheets by following the steps:
- First, select the cell of the row and column where the result of the function is supposed to be displayed.
- Then, define the range of the cells on which the formula should be applicable
- Then define one or more than one condition to sort the data.
In the next sections, the usage of the above function is defined with the help of examples.
Example: Use the “FILTER” Function with the Comparison Condition
We have a result sheet of the students of the class which is displayed in the figure below:
Now, we will sort out the students having marks greater than “32”. For this, we will select the cell below the marks of the last student:
Now apply the function whose range and conditions are:
Range= B3:B6 (This is the range of the “Student Names” column)
Condition=E3:E6>33 (The marks greater than the “33” in the “Marks” Column)
So the function will be:
=FILTER(B3:B6, E3:E6>33)
After writing the function with its condition, press the “ENTER” key:
The names of the students having more than 33 marks have been displayed by using the “FILTER” function.
Example: Use the “FILTER” Function with the Logical Condition
Another use of the “FILTER” function is with the logical expression. For example, if we want to filter the students having more than 33 marks and also having “A” grades, then we will use one more condition:
Condition: Sheet1!D3:D6=”A” (This will sort filter the students who have “A” grade)
So, the function will be:
=FILTER(Sheet1!B3:B6, Sheet1!E3:E6>33, Sheet1!D3:D6="A")
The output will be:
This shows that only “John” is the student who has passed with an “A” grade.
Example: Use the “FILTER” Function with the Match Technique
There is another technique of sorting the data by matching the words. For example, in the above example, we sort out the students whose “Result Status” was matching with the “Pass”.
In this example, we will obtain the students who passed without having the “A” grade by a different approach. We will find the result status which is not matching with the “Fail”.
The condition for it will be:
Condition: Sheet1!D3:D6<>”A”
So the function will be:
=FILTER(Sheet1!B3:B6, Sheet1!E3:E6>33, Sheet1!D3:D6<>"A")
The output of the above applied function will be:
The “Paul” and “Rahul” are the students who passed without having an “A” grade.
Conclusion
The “FILTER” function is used on Google Sheets to extract the specified information. The filter function helps Google Sheets users to sort the information. In this blog, the usage of the “FILTER” function is explained in detail. The general syntax is also explained with the demonstration of some examples,
