Images are reused AI-generated editorial illustrations, not documentary photographs or verified product screenshots.
A Google Sheets FILTER formula returns matching rows or columns into a separate output area. It does not hide rows in the original sheet, and it is different from a filter view. Choose it when a derived list helps the task, then test the condition ranges and the case where no records match.
Choose the returned range and condition
For synthetic task names in A2:A4 and statuses in B2:B4, =FILTER(A2:A4,B2:B4="Ready") returns the matching names. Keep the condition the same length as the returned range. Google’s documentation does not permit mixing row and column conditions in a single FILTER call. Make the intended direction clear.
Leave room and handle no matches honestly
The result can occupy several cells. Check for existing output content before adding the formula. FILTER returns #N/A when no values meet the conditions. Distinguish that expected empty case from a broken range or malformed formula; suppressing every error with a blank can conceal a genuine configuration problem.
Review source changes and sharing
Add and remove synthetic records to check whether the defined ranges include them. Verify exact status values and whitespace where relevant. A filtered subset is not an access-control mechanism: someone with access to the workbook may still see the original sheet. Use an appropriate separate sharing arrangement for restricted information.
A practical checklist
- Distinguish FILTER output from a filter view.
- Align returned and condition ranges.
- Keep the output area clear.
- Test a genuine no-match case.
- Review source growth and sharing permissions.
Worked example
Illustrative example: two of three synthetic tasks have status Ready. The formula returns their names without moving or hiding the source rows. Changing every status to Waiting produces the documented no-match outcome. The team handles that case deliberately and does not describe the derived list as a private client portal.
Common questions
Does FILTER change the source? It returns a separate result. Is an empty result always a formula mistake? No. Can it restrict access to hidden source records? No; review workbook permissions.
What to do next
Use a small matching and nonmatching fixture before expanding the range. Clear output behavior is more useful than a list that appears correct only while the sample stays unchanged.
Sources and further reading
Related reading
- How to Offer Spreadsheet Cleanup as a Freelance Service
- Exporting Your Data Before Canceling a Software Subscription
Google Sheets SUMIF Formula: Match One Condition to the Correct Amounts
Google Sheets IMPORTRANGE: Review Access Before Connecting Workbooks