SheetFX
≡
<< Go back to all functions

FILTER

The FILTER function in Google Sheets is a powerful tool to return a filtered version of a source range, only including rows or columns that meet specified conditions. Whether you're analyzing data, organizing information, or creating custom views, the FILTER function simplifies the task. Dive into our comprehensive guide to master its application.

Function Syntax and Parameters

Syntax: FILTER(range, condition1, [condition2])

Parameters:

  • range: The range of cells to filter.
  • condition1: The first condition to apply when filtering.
  • condition2, ...: [Optional] Additional conditions to apply when filtering.

Step-by-Step Tutorial

  1. Using FILTER to filter rows based on a condition:

    • Example: If you have a range A1:C5 with the following values:

      | A | B | C | | --- | --- | --- | | 5 | 6 | 7 | | 8 | 9 | 10 | | 11 | 12 | 13 | | 14 | 15 | 16 | | 17 | 18 | 19 |

      And you want to filter the rows where the value in column A is greater than 10.

      The formula would be: =FILTER(A1:C5, A1:A5 > 10)

      The result would be:

      | A | B | C | | --- | --- | --- | | 11 | 12 | 13 | | 14 | 15 | 16 | | 17 | 18 | 19 |

  2. Using FILTER to filter columns based on a condition:

    • Example: If you have a range A1:C5 with the same values as above, and you want to filter the columns where the value in row 1 is even.

      The formula would be: =FILTER(A1:C5, MOD(ROW(A1:C5), 2) = 0)

      The result would be:

      | B | | --- | | 6 | | 9 | | 12 | | 15 | | 18 |

Use Cases and Scenarios

  1. Data Analysis: Extract specific data based on certain conditions.
  2. Report Generation: Create custom views by filtering data dynamically.
  3. Data Organization: Filter rows or columns to focus on specific data subsets.

Related Functions

  • QUERY: Improve data filtering with SQL-like queries.
  • SORT: Sort data in ascending or descending order based on specified keys or rules.

Related Articles