Filter function
The filter function enables you to filter out certain data values or datasets. In simple language, it isolates certain data from the workbook. It allows users to filter a range of data based on supplied criteria or what criteria you decide. It has made work easy as you can get data of certain objects alone. At the moment, the function Is only available for Microsoft 365 subscribers.
=FILTER(A5:20,C5:C20=H2,"")
For example, we have a workbook with data set on how we have been selling cars worldwide. In our data set, we want to filter Mercedes only.
1. Open your excel file.
2. Select all cells you want to filter the information.
3. Apply the formula in cell H2 =FILTER(A2:E18, C 2:C18=H2,"). If you are a 365 subscriber, you will be given a filter by option. Choose Mercedes and click the range you want.
A2:E18 selected the number of cells in the range where you want to get your information.
C2:C18 is the column that contains the data.
H2: pace where filter by is applied you want your data to appear.
When you hit enter after getting what you want to filter out. It will indicate all Mercedes in the workbook and every information you want.
The filter function can also be used to return multiple criteria.
The function can also be used to check multiple criteria. For example, we want to get information about ANTARCTICA and MERCEDES
1. Open the workbook.
2. Create a filter by option. By applying the formula. .=FILTER(A2:E18,C2:C18=H1,)*(A2:A18=H2),"")
3. Hit enter.
The last bracket with only quotation marks tells the formula to return nothing if the output is zero. When you add a filter, add other criteria. It can be done by writing the formula or adding manually.
The filter function can also return multiple criteria and sort the data.
Here we will have to use the FILTER function and SORT function. For this, we will have to use the FILTER function and SORT function simultaneously. For example, we need to see how many Audi is in Europe, and their prices should be in descending order.
=SORT(FILTER(A2:D18,(C2:C18=H1)*(A5:A20=H2),""),4,-1)
we can also use – and + to sort data through.
In this case, we are using (+) to return all Audi values in our range or ANTARCTICA.
=SORT(FILTER(A2:D18,(C2:C18=H1)+(A5:A20=H2),""),4,-1)
Here is information about Audi in Antarctica will return with their prices sorted to start from the cheap car to the most expensive one.
None of the formulas above require an absolute reference. They exist in one cell, but their outputs are in neighboring cells.
These are the simplest filter function formula. The function is not hard to apply to any user. As long as you know the range of cells you want to select. Criteria are something else; you have to identify the information you want to filter. The function only works if it gets the information you want. Sorting can be complex; avoid it if you don't understand it. Filter by option is only available to 365 ms office subscribers. If you have not subscribed, the function won't work.