INTRODUCTION
In practical applications, in maximum cases , we have a large data which can’t be managed by just having a look on the data and manually selecting it.
The other way is to use the control + f for searching and then typing but it is again a cumbersome task to call this again and type the search word.
To overcome such situations we have an option called FILTER. Previously filter was not present as a function but now MICROSOFT EXCEL has introduced it as a function which gives us more control over the filtering of the data.
FILTER FUNCTION gives us the option of filtering out the data which doesn’t fulfill our condition.
In this article we’ll learn about the FILTER FUNCTION and various uses of FILTER FUNCTION .
PURPOSE OF FILTER FUNCTION IN EXCEL
FILTER FUNCTION allows us to filter the data on the basis of a given criteria.
We make use of the filter function if we need to take out a set of data on the basis of a condition.
It returns the complete row of the selected data.
PREREQUISITES TO LEARN FILTER FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- FILTER FUNCTION, if we know how to use.
- Some information about the financial terms is an advantage for the use of such formulas.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: FILTER FUNCTION
THE SYNTAX OF THE FILTER FUNCTION IS GIVEN AS BELOW:
FILTER ( the range to be filtered, criteria, what if nothing is found)
the range to be filtered is the complete data from which we need to filter.
criteria is the condition on which the filtration will take place.
what if nothing is found is the value which will appear if no value satisfies the condition
EXAMPLE :FILTER FUNCTION IN EXCEL
DATA SAMPLE
Let us find out the way we can make use of FILTER FUNCTION in excel.
We have a data of the different cities and the maximum and minimum temperature during the year.
| COUNTRY | ALTITUDE | MAXIMUM TEMP | MINIMUM TEMP |
| INDIA | 300 | 30 | 10 |
| US | 400 | 35 | 4 |
| UK | 350 | 25 | 5 |
| JAPAN | 150 | 20 | 8 |
| SPAIN | 160 | 23 | 9 |
We’ll find out the following answers using FILTER FUNCTION
- FILTER OUT THE COUNTRIES HAVING ALTITUDE GREATER THAN 300
- FIND THE COUNTRIES WITH MAXIMUM TEMPERATURE IS 30
- FIND THE COUNTRIES WITH MINIMUM TEMPERATURE LESS THAN 1 DEGREES
STEPS TO USE FILTER FUNCTION
We’ll use the FILTER FUNCTION for solving the queries on the given table.
1.FILTER OUT THE COUNTRIES HAVING ALTITUDE GREATER THAN 300
We’ll use the following function to get the answer
=FILTER(D5:G9,E5:E9>300,””)
The first argument is the complete range of the table. The second argument is the column on which the condition will be applied. Condition is that altitude in the column should be greater than 300.Third argument is the value which must display if the result is an empty array i.e. nothing is found satisfying the given condition.
2.FIND THE COUNTRIES WITH MAXIMUM TEMPERATURE IS 30
We’ll use the following function to get the answer
=FILTER(D5:G9,F5:F9=30,”NOTHING FOUND”)
The first argument is the complete range of the table just like the earlier case. The second argument is the column on which the condition will be applied. This time it is the MAXIMUM TEMPERATURE COLUMN. Condition is that Maximum temperature in the column should be equal to 30.Third argument is the value which must display if the result is an empty array i.e. nothing is found satisfying the given condition.
3.FIND THE COUNTRIES WITH MINIMUM TEMPERATURE LESS THAN 1 DEGREES
We’ll use the following function to get the answer
=FILTER(D5:G9,G5:G9<1,”NOTHING FOUND”)
On the same lines, the first argument is the complete range of the table. The second argument is the column on which the condition will be applied.The column selected is the MINIMUM TEMPERATURE. Condition is that minimum temperature in the column should be less than 1.Third argument is the value which must display if the result is an empty array i.e. nothing is found satisfying the given condition.
CREATION AND OUTPUT OF THE EXAMPLE
OUTPUT EXPLANATION
We click the enter after entering the function and following outputs are found
1.FILTER OUT THE COUNTRIES HAVING ALTITUDE GREATER THAN 300
The answer is US AND UK as these two countries only satisfies the condition.
2.FIND THE COUNTRIES WITH MAXIMUM TEMPERATURE IS 30
The output comes out to be INDIA only as it is the only country with maximum temperature is 30.
3.FIND THE COUNTRIES WITH MINIMUM TEMPERATURE LESS THAN 1 DEGREES
The output comes out to be NOTHING FOUND as no data satisfies the given condition.