Key takeaways
- FILTER returns a live array, so the source range and output area must be planned.
- Control cells turn a filtered view into a simple report without editing formulas.
- Handle empty results deliberately instead of letting an error dominate the sheet.
- Use QUERY or a pivot table when the view needs grouping or aggregation.
On this page
What FILTER and SORT actually return
FILTER returns every row that meets a condition, and SORT returns a reordered copy of a range. Both produce arrays that spill into neighbouring cells. The formula lives in one cell, but the result occupies a block, so the space below and to the right must stay empty.
This is the main difference from a filter applied through the toolbar. A toolbar filter hides rows in place and affects everyone viewing the sheet. A FILTER formula creates a separate view, which is safer for a report and easier to reuse.
- One formula cell controls the whole output block.
- The output block must not overlap other content.
- The source range should be stable and free of blank rows.
- Edits inside the spilled block will break the formula.
Start with a clean source range
A filtered view is only as reliable as the table underneath it. Keep one header row, no merged cells, no blank rows in the middle, and consistent data types in each column. Dates stored as text will not compare correctly with a date control cell.
Use an open-ended range such as A2:H so new rows are included automatically, or a named range if the table is formal. Avoid referring to an entire column when the sheet contains notes or totals below the data.
Drive the view with control cells
A control cell turns a formula into a report. Put a data validation dropdown in a labelled cell and let FILTER read it. A user can switch region, status, or owner without touching the formula, and the view updates immediately.
For multiple conditions, multiply the boolean tests or use the multiplication operator inside FILTER. Keep each condition in its own pair of brackets so the logic is readable. Test the combination where one control is set to All, because that is where formula logic usually fails.
- One control cell per filter dimension.
- An explicit All option instead of an empty cell.
- Labels next to each control, not inside the formula.
- A visible note explaining which source the view reads.
Sort the result, then shape it
Wrap FILTER in SORT to order the output by a column in the result, not by the source column letter. The column index refers to the filtered array, which is a common source of confusion when the source range does not start at column A.
Use SORTN when you need a fixed number of rows, such as the top ten exceptions. Use CHOOSECOLS or an array literal when the report should show a subset of columns in a different order. Each wrapper adds calculation work, so keep the source range no wider than necessary.
Handle empty results on purpose
When nothing matches, FILTER returns an error rather than an empty table. Wrap it with IFERROR to show a clear message, or with IFNA if you only want to catch the no-match case. A blank report with no explanation looks broken; a short message tells the reader the filter is working.
Check the spill area as well. If the output block collides with existing content, Sheets returns a reference error even when the filter logic is correct. Leave a generous empty area below the formula or place the view on its own tab.
Know when to use something else
FILTER is for row-level views. If the report needs grouped totals, conditional aggregation, or several joined tables, QUERY is usually clearer and faster to maintain. If the view needs drag-and-drop grouping, a pivot table is the better tool.
Large source ranges with many volatile formulas can make a sheet slow. If the view is recalculating constantly, move the source to a separate file, reduce the range, or replace the formula layer with a scheduled QUERY or an Apps Script refresh.
- Row-level selection: FILTER.
- Grouped totals and conditions: QUERY.
- Interactive summarisation: pivot table.
- Very large or shared sources: move the data out of the report file.
Frequently asked questions
Why does FILTER return a REF error?+
The output area is usually blocked by existing content. Clear the cells below and to the right of the formula, or move the view to an empty tab.
Can FILTER use more than one condition?+
Yes. Combine conditions with multiplication for AND logic, and test each control cell separately. Use an explicit All option rather than leaving a control blank.
Does FILTER update when new rows are added?+
It does if the source range is open-ended or a named range that expands. A fixed range such as A2:H100 will ignore rows added beyond row 100.
Need help applying this?
Improve your excel & google sheets.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Google Sheets data validation and protection
Data validation shapes what users enter; protection limits what they can change. Together they prevent most spreadsheet accidents, but neither is security.
Google Sheets dashboards with pivot tables
A pivot dashboard is a data source, a pivot layer, and a presentation sheet. Get the source clean, keep the pivot stable, and make refresh an explicit step.