Excel FILTER function - dynamic filtering with formulas

This tutorial demonstrates how to dynamically filter data in Excel using formulas, offering a powerful alternative to the static AutoFilter and Advanced Filter features. Unlike traditional filtering methods, formulas automatically update when data changes, eliminating the need for repeated filtering. This guide covers the FILTER function, available in Excel 365 and Excel 2021.

Understanding the FILTER Function

The FILTER function dynamically filters a data range based on specified criteria. Its syntax is:

FILTER(array, include, [if_empty])

• array: The data range to filter.
• include: A Boolean array (TRUE/FALSE) determining which rows to include. Its dimensions must match the array.
• if_empty: (Optional) A value returned if no rows meet the criteria.

Basic FILTER Formula

Let's filter rows where the "Group" column equals "C":

=FILTER(A2:C13, B2:B13="C", "No results")

This formula filters data in A2:C13, including only rows where column B equals "C". "No results" is displayed if no matches are found. It's best practice to reference criteria in separate cells for easier modification. For example:

=FILTER(A2:C13, B2:B13=F1, "")

where cell F1 contains "C". An empty string ("") returns nothing if no matches are found.

The FILTER function spills results into adjacent cells. Ensure sufficient empty cells to avoid the #SPILL! error. The results are dynamic; they update automatically when the source data changes.

Advanced Filtering Examples

• Multiple AND Criteria: Use multiplication (*) to combine multiple conditions. For example, to filter for group "A" and wins >=2:

  =FILTER(A2:C13, (B2:B13="A") * (C2:C13>=2), "No results")

• Multiple OR Criteria: Use addition ( ) to combine multiple conditions. To filter for 0 or 4 wins:

  =FILTER(A2:C13, (C2:C13=0) (C2:C13=4), "No results")

• Combined AND/OR Criteria: Combine multiplication and addition. For example, to filter for 2 wins AND (group "A" OR group "B"):

  =FILTER(A2:C13, (C2:C13=2) * ((B2:B13="A") (B2:B13="B")), "No results")

• Filtering Duplicates: Use COUNTIFS to count occurrences and filter those greater than 1:

  =FILTER(A2:C20, COUNTIFS(A2:A20, A2:A20, B2:B20, B2:B20, C2:C20, C2:C20)>1, "No results")

• Filtering Blank Cells: Use the "" operator:

  =FILTER(A2:C12, (A2:A12"") * (B2:B12"") * (C2:C12""), "No results")

• Filtering Cells Containing Specific Text: Use SEARCH and ISNUMBER:

  =FILTER(A2:D13, ISNUMBER(SEARCH("han", B2:B13)), "No results")

• Filtering and Aggregating: Combine FILTER with SUM, AVERAGE, MAX, MIN, etc.:

  =SUM(FILTER(C2:C13, B2:B13=F1, 0)) (Sums wins for group in F1)

• Case-Sensitive Filtering: Use EXACT:

  =FILTER(A2:C13, EXACT(B2:B13, "a"), "No results")

Filtering Specific Columns

• Adjacent Columns: Specify only the desired columns in the array argument.

• Non-Adjacent Columns: Nest FILTER within another FILTER, using an array constant ({1,0,1} for columns 1 and 3) for the inner FILTER's include argument.

Limiting the Number of Rows

Use INDEX and SEQUENCE to limit the number of rows returned:

=IFERROR(INDEX(FILTER(A2:C13, B2:B13=F1), SEQUENCE(2), SEQUENCE(1, COLUMNS(A2:C13))), "No result") (Returns the first 2 rows)

Troubleshooting FILTER Errors

• #CALC!: Missing if_empty argument and no matches found.
• #VALUE!: Incompatible dimensions between array and include.
• #SPILL!: Overlapping data or insufficient empty cells.
• #NAME?: Function misspelled or used in an unsupported Excel version.
• #REF!: Source data in a closed workbook.

This comprehensive guide empowers you to leverage the FILTER function for efficient and dynamic data filtering in Excel. Remember to download the practice workbook for hands-on experience.

The above is the detailed content of Excel FILTER function - dynamic filtering with formulas. For more information, please follow other related articles on the PHP Chinese website!