The excel FILTER function simply returns the filtered data from the given range. It is a functional version of simple filtering from the home menu that we have been using for decades in excel. This function helps us filter large databases into smaller chunks that we only need to work on.
For now, this FILTER function is only available to Excel 365 online version. It is unavailable in Excel 2016 and older versions.
Syntax of the FILTER Function
Array : This is the range that you want to filter. It can be an hard-coded array or range reference. It can be an entire database like an Employee database.
Include : It is the column or row by which you want to filter with criteria. For example, the salary column in the employee database with the condition >$10000, etc. It is not necessary to be part of the database. The dimensions of this array should match with the filter Array.
[if_empty] : This is an optional argument. Use this when you want to define what to return if no value is found.
Let’s understand the FILTER function with an example
Here I have a sample data set of students and their obtained marks in a test.
I want to filter all the records whose marks are greater than 90. A simple formula for this problem would be:
We have supplied the whole table A3:C11 as the array. Since we want to filter by Score, we give reference of Score range and the criteria C3:C11>90. Finally, if no value matches the given criteria, we return "No such records".
This returns the record and spills into the sheet.
How it works
Think of it as a counting function that returns the count of matched conditions. The only major difference is that the FILTER function returns an entire recordset, not just the count. It can also be used as LOOKUP to return all matched values from the data set as we do in excel 2016 and older.
Filter the Dataset with Multiple Conditions
To filter the dataset with more than one condition we can use * for AND conditions and + for OR logic.
For example, from the above data, if I want to filter a record whose marks are greater than or equal to 90 and roll number is greater than 107 then the formula will be:
An example of OR logic: If you want to filter dataset where marks are greater than 90 or less than 70 (just for example). Than the formula will be:
So yeah guys, this was a quick introduction to the FILTER function of Excel 365. It is not yet available to desktop applications but it is expected to be released with Excel 2019 pro version.
I hope I was explanatory enough. If you have any doubt regarding this function or have any excel/VBA related query, ask it in the comments section below.
Related Articles:
The SORT Function in Excel | The sort function is new to Excel 365. This function dynamically sorts the supplied array into a new location like an array function:
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to look up value from different ranges and sheets.
How to use the COUNTIF function in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Dear,
If you have VBA code for excel new filter formula
=filter ( )
So kindly send me my email address.
Thanks