FILTER function

What you will build

Create a live view containing only paid orders. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Complete Google Sheets source table before formula verification.

Open the sample workbook in Google Drive (access required) ↗

Work through the example

Step 1: Use H2 as the starting cell for the dynamic output and make sure H2:M4 is empty before entering the formula.

Step 2: Enter FILTER in H2. The result spills across H2:M4 and keeps only the three rows whose Status is Paid.

Formula to try

=FILTER(A2:F6,F2:F6="Paid")

The result cell is selected with the formula visible in the formula bar and the complete source table available for comparison.

How this formula works=FILTER(A2:F6,F2:F6="Paid")

Overall meaning of the whole formula
- Filter A2:F6 and return only rows whose Status in column F is "Paid".
FILTER(range, condition1, [condition2, ...])
- `range` is required; it accepts the source range to return. In this lesson the argument passed is `A2:F6`.
- `condition1` is required; it accepts a TRUE/FALSE condition aligned with the filtered rows or columns. In this lesson the argument passed is `F2:F6="Paid"`.
Whole-expression evaluation
- A2:F6 is the full set of data rows to return.
- F2:F6="Paid" creates a TRUE/FALSE include array from the Status column.
- FILTER returns only rows whose corresponding condition is TRUE; this sample has three Paid rows.
- The result can occupy multiple cells; the spill area must be clear, and any companion condition ranges must have dimensions compatible with the data being processed.
Why the displayed final result appears
- FILTER returns only rows whose corresponding condition is TRUE; this sample has three Paid rows.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Date	Region	Agent	Units	Amount	Status
2026-09-01	North	An	2	120	Paid
2026-09-03	North	Chi	1	80	Paid
2026-09-04	South	An	4	320	Paid

Expected result: three rows with Status = Paid spill from H2:M4.

Common mistake and correction

If the result is unexpected, inspect the source cells, destination and referenced range in the formula. FILTER spills a multi-cell result; existing values in its output area can cause an error. Matching range dimensions matter.

Independent practice

After following the example, complete this variation without editing the source data: Replace Paid with Open and compare the resulting rows with the source.

Key takeaways

✓Create a live view containing only paid orders.
✓Use H2 as the blank output start cell and keep H2:M4 clear so FILTER can spill.
✓FILTER spills a multi-cell result; existing values in its output area can cause an error. Matching range dimensions matter.
✓Replace Paid with Open and compare the resulting rows with the source.

Continue learning

Previous lesson: AND and OR functions; Next lesson: SORT function.

What you will build

Create a live view containing only paid orders. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the sample workbook in Google Drive (access required) ↗

Work through the example

Step 1: Use H2 as the starting cell for the dynamic output and make sure H2:M4 is empty before entering the formula.

Step 2: Enter FILTER in H2. The result spills across H2:M4 and keeps only the three rows whose Status is Paid.

Formula to try

=FILTER(A2:F6,F2:F6="Paid")

How this formula works=FILTER(A2:F6,F2:F6="Paid")

Overall meaning of the whole formula
- Filter A2:F6 and return only rows whose Status in column F is "Paid".
FILTER(range, condition1, [condition2, ...])
- `range` is required; it accepts the source range to return. In this lesson the argument passed is `A2:F6`.
- `condition1` is required; it accepts a TRUE/FALSE condition aligned with the filtered rows or columns. In this lesson the argument passed is `F2:F6="Paid"`.
Whole-expression evaluation
- A2:F6 is the full set of data rows to return.
- F2:F6="Paid" creates a TRUE/FALSE include array from the Status column.
- FILTER returns only rows whose corresponding condition is TRUE; this sample has three Paid rows.
- The result can occupy multiple cells; the spill area must be clear, and any companion condition ranges must have dimensions compatible with the data being processed.
Why the displayed final result appears
- FILTER returns only rows whose corresponding condition is TRUE; this sample has three Paid rows.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Date	Region	Agent	Units	Amount	Status
2026-09-01	North	An	2	120	Paid
2026-09-03	North	Chi	1	80	Paid
2026-09-04	South	An	4	320	Paid

Expected result: three rows with Status = Paid spill from H2:M4.

Common mistake and correction

Independent practice

After following the example, complete this variation without editing the source data: Replace Paid with Open and compare the resulting rows with the source.

Key takeaways

✓Create a live view containing only paid orders.
✓Use H2 as the blank output start cell and keep H2:M4 clear so FILTER can spill.
✓FILTER spills a multi-cell result; existing values in its output area can cause an error. Matching range dimensions matter.
✓Replace Paid with Open and compare the resulting rows with the source.

Continue learning

Previous lesson: AND and OR functions; Next lesson: SORT function.

FILTER function

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

FILTER function

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments