PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS-FILTER

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

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.
COUNTRYALTITUDEMAXIMUM TEMPMINIMUM TEMP
INDIA3003010
US400354
UK350255
JAPAN150208
SPAIN160239

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

FILTER FUNCTION IN EXCEL CREATION AND RUNNING
EXCEL:CREATION AND RUNNING OF FILTER FUNCTION

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.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

PREVIOUS ARTICLE
NEXT ARTICLE

LEARN

Encourage us by following at …

Facebook
Twitter
Pinterest

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

-We might be compensated for the visits or purchases you make by clicking the ads on this website .  They help us to keep bringing excellent quality articles for you.
-Click on the pictures if you can’t read. All of them are High Resolution .
-If you can’t find what you were looking for, kindly message in the chat box.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: