Of the many functionalities of Excel, Filter is undoubtedly one of the most frequently used by most users around the globe. The purpose of the Filter function is self-explanatory. It helps the users filter out a specific data set depending upon the criteria generally set using an argument.
The Filter function of Excel is a relatively new addition and has only been included in Office 365. Therefore, you won’t be able to access this function if you currently have Office 2019 or any earlier versions of office. The Filter is mainly a worksheet functionality under the Dynamic Arrays Function category inculcated within Excel.
The Filter is primarily a dynamic function that frees you up to change values at the source. In addition, the function also allows you to resize the data at the source array so that you can have a set of returned values that are automatically updated. In this article, you will learn the user’s guide to using the Filter function in Excel.
Relevant Arguments You Must Know About the Filter Function
Here is the syntax for the Filter function on Excel:
= FILTER(array, include, [if_empty])
Array
Array helps the user specify all the ranges and arrays you might want to fetch or filter.
Include
This argument helps the user provide the filtering criteria in the Boolean array form.
if _empty
It is considered an optional option where you can specifically require a value that the function can throw back if entries do not meet the provided criteria.
Features and Characteristics of the Filter Function
Filter function offers a wide range of features and characteristics to its users that are helpful today in many day-to-day tasks. Firstly, the Filter function allows you to filter all the arrays, regardless of their position. Therefore, horizontal and vertical are accessible by the function of the Filter.
Secondly, it is vital to ensure that you are using the Filter function between two workbooks operating separately. Because failing to do so will almost certainly return a #REF! unwarranted error.
Thirdly, if you properly organize all your data, the Filter function should render you the output you are looking for. Therefore, ensure you keep enough vacant cells to the right and below. Otherwise, your function might return a Spill error.
Last but not least, a mismatch of dimensions provided in the Include argument might trigger an incompatibility with the array argument resulting in a Value error. Therefore, it is vital to keep an eye out for such incongruity.
Excel Filter Function and Its Uses
The Filter function in Excel can be used for all kinds of scenarios. There are different criteria and logic you can set for the function.
This will help you extrapolate different kinds of data from a datasheet depending on your particular need. Let’s look at all the different kinds of data you can extrapolate with the help of this very useful Excel filter function.
Basic Filter Function
One of the most used Filter function use cases is the basic one. It allows you to extract data on specific things you want to look up. Say for example you have a store. The store has different branches in different cities. And there are salespeople working in those branches.
The branches sell different kinds of electronic goods. Well, with the Filter function, you can extract all kinds of specific data. If you want to see which branch sells the most TVs, you can just write the formula in that way. Excel will give you the output of which branches sold TVs. This is pretty useful when you are doing all kinds of data analysis.
Using Filter in Collaboration with the Exact Function
Another pretty neat trick is using the Filter function with the Exact function. This will help you extract data that match your given criteria or logic exactly. In general, the Filter function is not case-sensitive. However, with the Exact function, you can search for, well, exact matches in a given dataset.
Say you have a dataset. The rows are from A2 to C20. That means that there are three columns of data and 19 rows – the top is for the headings. That is why A1, B1, and C1 cells are not in the array. If you want to search for something exact, then the formula will be like this:
= FILTER(A2:C20, EXACT(A2:A20, “Branch Name”), “No matches”)
The role of the Exact function in Excel Filters is to Include arguments within the Filter function. It essentially tells the function what to search for. The Exact function can return True if it finds a match and False if it does not find a match. Therefore, the output should look as follows.
And, OR Operators in Excel Function
Using different operators like ‘And’ & ‘Or’ can help you navigate datasets with different criteria in mind. This will help you get results with more precision.
Let’s go back to the store example. Say you want to find the branches that sell TVs. But you also want to see which sales rep sold them. You can set up the formula to show TVs and by Sales rep.
You can create a small criterion on a different cell and go to Advanced Filter to set ‘And’ & ‘Or’ operators. This short tutorial video does a great job of explaining it. If you put the criteria side by side, Excel understands it as the ‘And’ operator.
When you put the criteria under the previous cell, Excel will take it as an ‘Or’ condition. The video does a pretty job of showing all these in an easy way.
Conclusion
The Filter is undoubtedly one of Microsoft’s most effective functions in Excel in recent years. If you can combine the Filter function with other existing operators and functions prudently, you can achieve tremendous productivity using just a few simple commands.
Practicing and using Filter oriented commands regularly is crucial if you wish to be an expert in using Filter functions daily. Hopefully, you have found this article helpful enough to discover how effective the Filter function can be. Thanks for reading till the end of the article.
