The FILTER function is one of the best functions added to Excel Online and Microsoft 365 for dynamic data filtering. It helps users display only relevant information without changing the original data.
- Instead of manually filtering a long list to find orders above a certain quantity, use FILTER to display only those rows instantly.
- Need to analyze data for a specific customer? The FILTER function pulls all relevant records with just one formula.
- You can also use this function as argument in other dynamic arrays.
Overview of the FILTER Function in Excel
Data filtering is a core tool in Excel. The FILTER function simplifies this by using formulas to filter and display only relevant data.
Instead of manually filtering data each time, the FILTER function allows you to automate this task with a simple formula.
FILTER Function Parameters
The FILTER function requires only two main parameters:
- Array: Specify the data range or array from which to return results.
- Criteria: Define the condition to filter data. It's not necessarily a column selected in the array.
- [Optional] No Result: Use a default message if no data matches the criteria.
=FILTER(array, criteria, no_result)
Example 1: Extract Rows for Customer "Peter"
To filter data for a specific customer, such as Peter, use the following steps:
- Select the data range (in this example, the table's name is tbl_Sales).
- Define the column containing the criterion value (customer name).
- Enter the value of the criterion (e.g., "Peter").
=FILTER(tbl_Sales, tbl_Sales[Customers]="Peter")
Example 2: Exclude Specific Values
FILTER accepts all types of logical conditions. For instance, to display all fruits except apples, use this formula:
Example 3: Filter by Quantity Greater Than 20
To filter data for quantities greater than 20, use this formula:
Example 4: Customize No Result Message
If no rows match your condition, the FILTER function returns an error. To prevent this, use the third argument to display a custom message instead.
- In our example, we don't have a customer named "Mariana".
- To prevent an error, we will return "No Result".
=FILTER(Tbl_Sales, Tbl_Sales[Customers]="Mariana", "No Result")
Example 5: Return a Different Column from the Filter Criteria
The FILTER function allows you to display columns that are different from the ones used in the filter criteria 😮. For example, you can return only the 'Fruit' column while using 'Customer' names as the condition.
