FILTER function in Excel is used to sort data from a data set that matches a certain condition. It returns an array of data as a result.
FILTER Function Syntax
=FILTER(array, include, [if_empty])
Components of the FILTER Function
Now, let’s discuss the three components of the filter function:
- array – This is the data that is being provided as an input. In other words, this is the range of values that we are providing as an input to search the desired data from.
- include – This is the logical condition or criteria that needs to be defined to perform a search on the array range that is provided in the function. Note that this condition can range from simple to complex depending on what kind of search is needed on a set of data.
- if_empty – This is an optional field of input that you can provide which can be displayed in the event of no data being found that matches the search condition.
FILTER Function Example
Now let’s try using the FILTER function in Excel. Below is the sample data that we’re going to be working on. You can copy this table in an Excel worksheet to work on this example.
| Product Name | Cost Price | Sale Price | Markup % |
| Sigma | $14.75 | $20.00 | 35.59% |
| Univy | $13.40 | $20.00 | 49.25% |
| Extram | $4.67 | $15.00 | 221.20% |
| Tryout | $3.76 | $15.00 | 298.94% |
| Grender | $3.00 | $15.00 | 400.00% |
| Tulaly | $45.00 | $350.00 | 677.78% |
| Renger | $5.00 | $15.00 | 200.00% |
| Ultrap | $4.68 | $12.00 | 156.41% |
| Tunmip | $16.22 | $20.00 | 23.30% |
| Guters | $9.87 | $12.00 | 21.58% |
| Drewy | $12.78 | $15.00 | 17.37% |
| Jiras | $56.00 | $125.00 | 123.21% |
| Frendy | $150.00 | $300.00 | 100.00% |
| Taspit | $5.00 | $7.00 | 40.00% |
| Wencher | $57.00 | $125.00 | 119.30% |
| Trengy | $2.99 | $15.00 | 401.67% |
| Qerty | $15.24 | $20.00 | 31.23% |
| Ulipy | $12.87 | $15.00 | 16.55% |
| Hensmit | $10.99 | $12.00 | 9.19% |
| Desonit | $68.74 | $350.00 | 409.16% |
| Illitr | $10.00 | $12.00 | 20.00% |
| Gonger | $3.56 | $7.00 | 96.63% |
Task/Problem – We need to filter out all product names for which the markup percentage is greater than 100%.
Solution – Now, let’s work on the solution. In order to obtain the product names of all products whose markup percentage is greater than 100%, our array range will be the range for product names. Below is the snippet of this above table in Excel so you can understand the row and column references that go into the filter function to achieve the desired output, as explained in the task/ problem above.
Now, our function would be as below:
=FILTER(A2:A23, D2:D23>1,”No Data Exists”)
- A2:A23 is our array
- D2:D23>1 is the condition (Find markup % greater than 100%)
- “No Data Exists” is the optional input provided in the event that no matching data is found.
Result – We entered this formula in cell F2. This formula yielded an array of product names as highlighted within the red box in the below screenshot.
Additional Practice Scenarios
- Task 1: Find all product names whose sale price is greater than $15 AND markup percentage is greater than 100%.
- Solution: To write this formula, the ‘include’ condition has two criteria that need to go together. So, we use “*” sign to indicate AND logical operator. See formula below.
=FILTER(A2:A23, (C2:C23>15)*(D2:D23>1), “No Data Exists”) - Result: Tulaly, Jiras, Wencher, Desonit
- Solution: To write this formula, the ‘include’ condition has two criteria that need to go together. So, we use “*” sign to indicate AND logical operator. See formula below.
- Task 2: Find all product names for which either the sale price is greater than $15 OR the markup percentage is greater than 50%.
- Solution: To write this formula, the ‘include’ condition has two either/or conditions. So, we use “+” sign to indicate OR logical operator. See formula below.
=FILTER(A2:A23, (C2:C23>15)+(D2:D23>0.5), “No Data Exists”) - Result: Sigma, Univy, Extram, Tryout, Grender, Tulaly, Renger, Ultrap, Tunmip, Jiras, Frendy, Wencher, Trengy, Qerty, Desonit, Gonger
- Solution: To write this formula, the ‘include’ condition has two either/or conditions. So, we use “+” sign to indicate OR logical operator. See formula below.
- Task 3: Find all product names for which EITHER the cost price is less than $15 OR the markup percentage is greater than 50% and sale price is greater than $15.
- Solution: To write this formula, the ‘include’ condition has multiple criteria. So, we use “+” sign to indicate OR logical operator and “*” sign to indicate the AND logical operator. See formula below.
=FILTER(A2:A23, (B2:B23<15)+((C2:C23>15)*(D2:D23>0.5)), “No Data Exists”) - Result: Sigma, Univy, Extram, Tryout, Grender, Tulaly, Renger, Ultrap, Guters, Drewy, Jiras, Frendy, Taspit, Wencher, Trengy, Ulipy, Hensmit, Desonit, Illitr, Gonger
- Solution: To write this formula, the ‘include’ condition has multiple criteria. So, we use “+” sign to indicate OR logical operator and “*” sign to indicate the AND logical operator. See formula below.
Conclusion
In conclusion, there is a lot that Filter function in Excel can do, incorporating complex conditions to yield desired outputs. If you are proficient with the use of logical operators, you can use this function to its maximum potential. If you want to learn more on logical operators, check out this article here on the most common logical operators in Excel.
.
