Filter a spreadsheet

Complete “Filter a spreadsheet” using the visible Task, Owner, Status sample so each action and the final state can be checked against the source data.

What you will build

Learn two different ways to narrow a task list without deleting source records: turn on a sheet filter for A1:C5, then use the FILTER function to build a separate dynamic list of unfinished tasks.

Learning objective

Turn on a sheet filter for A1:C5 with Data > Create a filter, then enter FILTER in E2 to return rows whose Status is not Done. Explain the difference between hiding rows in the source table and returning matching rows in a separate spill range.

Open the practice workbook

Open the practice workbook below, or copy the TSV into A1 if you want a fresh sheet. The working table is A1:C5 with the headers Task, Owner and Status.

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.

Google Sheets with the complete A1:C5 task dataset visible before any filtering action.

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

Work through the example

Step 1: Select the complete range A1:C5 so Task, Owner and Status stay together. Keep the full range selected for the next step.

The complete range A1:C5 is selected so Task, Owner, and Status remain together.

Step 2: With A1:C5 selected, click Data > Create a filter. This turns on filter controls for the whole table; it does not delete or rewrite any source row.

Google Sheets Data menu open with A1:C5 still selected and the pointer directly on Create a filter.

Step 3: Confirm that a filter icon appears in each header cell. A sheet filter changes which source rows are visible in place. The FILTER function in the next step works differently: it returns matching rows into a separate result range.

Filter controls are visible in all three header cells, with the pointer on the Status filter control.

Formula to try

Step 4: Click E2, enter =FILTER(A2:C5,C2:C5<>"Done"), and press Enter. Leave E2:G5 empty first so the result has room to spill.

=FILTER(A2:C5,C2:C5<>"Done")

How this formula works=FILTER(A2:C5,C2:C5<>"Done")

FILTER(range, condition) returns only the rows from a source range that meet a condition.
A2:C5 is the range to return. It contains the four task records across Task, Owner and Status, without the header row.
C2:C5 is the condition range. It contains the Status values that line up row by row with A2:C5.
<> means “not equal to” in Google Sheets. Therefore C2:C5<>"Done" asks Sheets to keep rows whose Status is anything except Done.
"Done" is text, so it is wrapped in quotation marks. The comparison produces TRUE for Design/Working and QA/Planned, and FALSE for Brief/Done and Release/Done.
FILTER keeps the rows where the condition is TRUE, so the verified output is Design | Binh | Working and QA | Chi | Planned. Because the result is an array, it spills automatically from E2 into the cells to the right and below.

Cell E2 is selected with =FILTER(A2:C5,C2:C5<>"Done") visible in the formula bar and the two unfinished rows spilled into E2:G3 while the full source table remains visible.

Task	Owner	Status
Design	Binh	Working
QA	Chi	Planned

This is the verified FILTER output in E2:G3. The two rows whose Status is Done are excluded from the result.

Checkpoint

Checkpoint: A1:C5 must still contain all four source records. When C3 changes from Working to Done, the FILTER result must shrink to one row; restoring C3 must bring the two-row result back.

Common mistake and correction

Correction: return to the exact range and command named in the steps, then compare the live sheet with the Task, Owner, Status source fields.

Independent practice

In another blank area, write a FILTER formula that returns only rows owned by Binh. Then compare that separate result with what a sheet filter does to the original A1:C5 table. Keep the source values unchanged.

Key takeaways

✓Complete “Filter a spreadsheet” using the visible Task, Owner, Status sample so each action and the final state can be checked against the source data.
✓Select the complete range A1:C5 so Task, Owner and Status stay together. Keep the full range selected for the next step.
✓With A1:C5 selected, click Data > Create a filter. This turns on filter controls for the whole table; it does not delete or rewrite any source row.
✓If the result differs, repeat “Select the complete range A1:C5 so Task, Owner and Status stay together. Keep the full range selected for the next step.” and then “With A1:C5 selected, click Data > Create a filter. This turns on filter controls for the whole table; it does not delete or rewrite any source row.”. Verify the working table still uses Task, Owner, Status before changing source values.
✓In another blank area, write a FILTER formula that returns only rows owned by Binh. Then compare that separate result with what a sheet filter does to the original A1:C5 table. Keep the source values unchanged.

Continue learning

Previous lesson: Sort data in Google Sheets; Next lesson: Freeze rows and columns.