Session 2 - Data Literacy: Beyond the Basics: Googlesheets formula: =FILTER

3. Visulising your Data

3.5. Googlesheets formula: =FILTER

Using Google Sheets: Filtering Data with =FILTER

This guide explains how to use the FILTER function in Google Sheets to extract data from a range based on specific criteria. This is incredibly useful for creating targeted views of your data, analyzing subsets of information, and generating dynamic reports.

What is =FILTER?

FILTER allows you to select rows from a range that meet one or more conditions. It returns a new range containing only the rows that satisfy the criteria. The original data remains unchanged.

Syntax:

=FILTER(range, condition1, [condition2, ...])

Let's break down each part:

=FILTER: This is the function name.
range: This is the range of cells you want to filter (e.g., "A1:D10", "Sheet1!A1:B5").
condition1: This is the first condition that must be met. It usually involves a comparison (e.g., "A1:A10 > 70", "B1:B10 = "Maths"").
[condition2, ...]: (Optional) Additional conditions. All conditions must be true for a row to be included in the filtered result.

Step-by-Step Example:

Open the Spreadsheet: Open the Google Sheet containing the data you want to filter.
Enter the Formula: In the cell where you want the filtered data to begin, type the FILTER formula. For example, to filter data in the range A1:D10 where values in column C are greater than 80:
```
=FILTER(A1:D10, C1:C10 > 80)
```
Data Appears: The rows that meet the condition(s) will appear in the destination range.
AND Logic (Multiplication *): In Google Sheets' FILTER function, when you multiply two or more conditions, it's treated as an "AND" operation. This means that all the conditions must be true for a row to be included in the filtered results. Think of "TRUE" as 1 and "FALSE" as 0. When you multiply conditions, if even one condition is FALSE (0), the result is 0 (FALSE), and the row is excluded.

Example: (C1:C10 > 80) * (D1:D10 = "Pass") means "show rows where the value in column C is greater than 80 AND the value in column D is 'Pass'."
OR Logic (Addition +): When you add two or more conditions, it's treated as an "OR" operation. This means that at least one of the conditions must be true for a row to be included. If any of the conditions result in a "TRUE" (a value greater than 0) the result of the addition will be greater than zero, and therefore the row will be included.

Example: (C1:C10 > 80) + (D1:D10 = "Pass") means "show rows where the value in column C is greater than 80 OR the value in column D is 'Pass'.

Important Considerations:

Conditions: Conditions can use various operators like >, <, =, <>, >=, <=. You can also use text comparisons (e.g., `= "Maths"`).
Multiple Conditions: Use commas to separate multiple conditions. All conditions must be true for a row to be included.
Array Formulas: FILTER is an array formula, meaning it can return a range of cells. It automatically spills into adjacent cells as needed. This means the results of the FILTER formula will automatically fill the cells below and to the right of the cell where you enter the formula. If you have data in the cells where the filter is trying to display its results, you will get an error.
Blank Cells: FILTER will ignore blank rows in the `range`.
No Matching Data: If no rows match the criteria, FILTER will return a `#CALC!` error. You can wrap the `FILTER` in an `IFERROR` to handle this. For example:
```
=IFERROR(FILTER(A1:D10, C1:C10 > 80), "No matching data")
```

Ways you might want to use this:

Filtering Pupil Data by Grade Level

A teacher wants to see only the data for pupils in a specific stage (e.g., S3). They use FILTER to extract the rows where the "Stage" column matches "S3".

Finding Pupils Who Scored Above a Certain Mark

A teacher wants to identify pupils who scored above a certain mark on an assessment. They use FILTER to select rows where the "Assessment Score" column is greater than the desired threshold.

=FILTER(A2:B, ...): This starts the FILTER function and specifies the range of data we want to filter (columns A and B, starting from row 2).
B2:B > 80: This is the condition. It checks if the values in column B (starting from row 2) are greater than 80.
The filter function will return all of the rows from A2:B where the score in column B is greater than 80.

Filtering for Pupils in a Specific Class

A teacher with multiple classes wants to view data for only one class. They use FILTER to extract rows where the "Class" column matches the desired class name.

Combining Multiple Criteria

A teacher wants to find pupils in S2 who scored above 80 on a particular test. They use FILTER with two conditions: "Grade Level" = "S2" and "Test Score" > 80.

Creating a List of Pupils Needing Additional Support

A teacher wants to create a list of pupils who are struggling in a particular subject. They use FILTER to select rows where the "Subject" column matches the subject and the "Performance" column indicates "Needs Improvement" or similar.

Generating a Report for Specific Criteria

A teacher needs to create a report showing data for a specific group of pupils (e.g., those receiving additional support). They use FILTER to extract the relevant data and then use it to generate the report.

Analyzing Data by Gender

A teacher might want to compare performance between male and female pupils. They can use FILTER to create separate views of the data for each gender.

Tracking Progress Over Time

A teacher could use FILTER to look at pupil data from a specific assessment period to track progress made since then.

You may want to watch this tutorial