To use FILTER in Google Sheets, enter the range you want to return, then add a condition that checks the corresponding rows. For example, =FILTER(A2:D100,C2:C100="Done") returns the rows in A2:D100 where column C is Done. Put the formula in an empty destination and leave room for the result to expand.
Add another condition argument when each row must pass both tests. For either-or logic, combine the tests inside one condition. Make the condition ranges start and end on the same rows as the source range. Google Sheets returns #N/A when no rows match, so verify your criteria before adding a friendly no-results message.
- Start the source and condition ranges on the same data row.
- Use separate condition arguments for AND logic.
- Combine two tests in one condition for OR logic.
- Keep the result area clear so it can expand.
- Use a filter view instead when you only need to inspect the original table.
Choose a formula result or an interactive filter view
FILTER creates a new result from a source range. It can give a project dashboard a live list of current tasks, or show records for a selected session. The formula does not rearrange the underlying source rows. If you only want to inspect the table directly, Sheets’ filter or filter-view controls may be the better tool. Choose the method based on whether you need a separate output.
A filter view is useful for exploring
A team member can use an appropriate filter view to inspect selected rows of a shared table. The view is about how the original sheet is displayed. A FILTER formula creates its own cells in an output range. If another part of the workbook should refer to a live list, a separate formula result can be useful.
Use a clear source and destination
Keep the master table in one place. Put the filtered result in an empty area or on a reporting sheet. Add headers above it if you are filtering only data rows. Use descriptive headings so a reader knows which criteria produced the result. Avoid placing the formula where its output would collide with the source.
Back to contents · Next section
Use FILTER with one condition
Suppose A2:D100 contains Task, Owner, Status and Due date. To show rows where Status is Done, use this formula.
=FILTER(A2:D100,C2:C100="Done")The first range tells Sheets which values to return. The second expression produces a TRUE or FALSE result for each source row. Rows whose status matches are returned with all four selected columns. The ranges start at row two because row one contains headings. Put the formula in an empty area and check a known Done task.
Return fewer columns if that is the task
If you only need the task names, use A2:A100 as the return range and keep C2:C100 as the condition range. The ranges can use different columns, but the number and positions of their data rows must correspond. Check that the formula returns the intended item when the condition is true.
=FILTER(A2:A100,C2:C100="Done")Use a cell as the criterion
A selector cell can hold a dropdown such as Done or In progress. If that selector is G1, compare the status column with G1. When the selector changes, the result should update. Keep the selector outside the area the result will fill.
=FILTER(A2:D100,C2:C100=G1)Decide what an empty selector should mean. It can match blank statuses if you use this formula unchanged. If the report should ask the reader to choose a value first, handle that state explicitly and test it.
Back to contents · Next section
Use several conditions when every test must pass
Separate condition arguments are combined as AND conditions. To return tasks marked Done and assigned to Maya, check both columns in the same formula.
=FILTER(A2:D100,C2:C100="Done",B2:B100="Maya")A row marked Done and assigned to another owner does not pass both tests. A task assigned to Maya but still In progress also stays out. Check one row from each case to verify the formula behaves as expected.
Filter using a number or date
For a data table where column D contains quantities, you can include rows with at least 10. The values need to be stored in a form that compares as numbers as intended.
=FILTER(A2:D100,D2:D100>=10)For dates, use a date value or a date stored in a selector cell. Check whether the values include times and whether the rule should include the start or end date. For example, a report of dates on or after the date in G1 can use D2:D100>=G1 when the column holds the appropriate dates. Verify the first and last records near the boundary.
Use aligned ranges
If the data range begins at row two and ends at 100, use corresponding condition ranges. A condition beginning at row one includes the header and misaligns the test. A range ending at 99 has a different height. Check the start and end of every condition when you extend the source.
Back to contents · Next section
Use OR logic when either of two choices should match
To show tasks marked Done or Needs review, combine the two tests into one condition. Adding their TRUE and FALSE values and checking for a result greater than zero keeps rows where at least one test passes.
=FILTER(A2:D100,((C2:C100="Done")+(C2:C100="Needs review"))>0)If you write the two equalities as separate condition arguments, each row would have to satisfy both. A single-status cell generally cannot equal two different labels at the same time. Use the combined expression for the intended OR rule.
Combine OR logic with an additional AND condition
Suppose you only want Maya’s tasks, and their status may be Done or Needs review. Put the OR expression in one condition and the owner test in another.
=FILTER(A2:D100,((C2:C100="Done")+(C2:C100="Needs review"))>0,B2:B100="Maya")Check one task for Maya with each allowed status, then one for another owner. Only the intended tasks should appear. If a condition includes several levels, add parentheses so it is clear what belongs together. Test rows that should be excluded as well as rows that should be included.
Keep the rule understandable
Name selector cells or label them clearly where appropriate. A complicated report can use a helper column to make a business rule explicit. For example, a helper can identify rows eligible for review, and FILTER can return eligible rows. Verify the helper’s logic first. Do not hide a complex rule behind a formula nobody can check.
Back to contents · Next section
Handle no matching rows without hiding a real problem
Google documents that FILTER returns #N/A when it finds no values meeting the conditions. Check whether the absence is expected. Look at the exact status, any spaces and the selector value. Verify that the formula reads the right columns and includes the relevant rows. If the no-match outcome is normal, IFNA can show a clear message.
=IFNA(FILTER(A2:D100,C2:C100=G1),"No matching rows")Check the source before adding an error wrapper
An error can come from the data or the formula rather than from a genuinely empty result. IFNA handles #N/A, which can also arise from source formulas. Inspect the underlying FILTER result when diagnosing a problem. A broad error wrapper can make broken logic look like an empty report. Verify the source and conditions before relying on the message.
Give a blank selector its own message
If the selector should be required, test it before calling FILTER. This example asks the user to choose a status when G1 is empty.
=IF(G1="","Choose a status",IFNA(FILTER(A2:D100,C2:C100=G1),"No matching rows"))Check three cases: no choice, an allowed choice with matches and a choice with no matches. The report should show a different and useful result in each case. Keep the selector in a place the output cannot cover.
Back to contents · Next section
Fix mismatched ranges and results that cannot expand
The condition range has the wrong height
Compare the first and last rows of the source and conditions. A2:D100 and C2:C100 correspond row for row. If the source grows to row 150, extend the conditions as well. Use an appropriate consistent range if the report will expand, and check new records deliberately.
The formula shows an expansion error
Look for values, merged cells or other occupied cells in the intended destination. Move the formula to a clear area or resolve only the conflicting contents you are authorised to change. A formula can return more rows than it did earlier when the source changes. Leave room for growth.
The output looks like the wrong column
Check what the first argument returns. A condition on Status can return the entire row or only Task depending on the selected source columns. If the output has no headers, add them in a separate row and verify their alignment. A correct filter can look wrong when labels are shifted.
The text criteria look right but match nothing
Open the source cell and check its actual value. An imported label can contain leading or trailing spaces. A date or number can be stored as text. Compare it with the selector and clean an approved working copy where appropriate. If the sheet uses a different formula separator because of its locale, adapt the syntax to what the spreadsheet expects.
Back to contents · Next section
Filter columns when the output should go across the sheet
Google documents filtering either rows or columns in a single FILTER call. If you filter columns, the condition needs to match the width of the returned range. For example, if B1:E1 contains the header category for four columns and B2:E10 contains data, the following formula keeps columns whose category is Include.
=FILTER(B2:E10,B1:E1="Include")Check the source and condition orientation. Row conditions go with row filtering, and column conditions go with column filtering. When you need to reduce both directions, use separate steps or a nested workflow appropriate to your sheet. Verify the resulting rows and columns independently.
Give the result headers when they matter
The example returns B2:E10 data and does not include row one. If the reader needs headings, add suitable ones or choose a source range that contains them when the layout permits. Confirm that the headers align after the condition removes a column. A result with correct values and wrong headings can be misleading.
Back to contents · Next section
Work through a simple reporting sheet
Create a master table with Task, Owner, Status and Due date. Enter a few test rows, including Done, In progress and Needs review. Put a status selector on a separate reporting sheet or away from the output. Add the FILTER formula and check one known match. Change the selector and verify the displayed rows.
Add another condition
Add an owner criterion and test the AND case. Then try the OR example with two statuses. Record which rows should appear before checking the output. If you get an empty result, compare the exact criterion and source values. If the result is too large, inspect whether the formula uses OR where you wanted AND.
Test the growing data source
Add a harmless row within the included range and see whether the report picks it up. Then check a row outside a fixed range. Decide whether to extend the range or change the workbook structure. A live result can only use source data that the formula actually refers to.
Verify the output before sharing
Check the intended criteria, first row, last row and empty-state message. If the report is going to a different audience from the source, use an approved delivery route. A formula view does not create a separate access boundary for the underlying spreadsheet. Send the required authorised copy or access setting.
Back to contents · Next section
Add a date window without dropping the last day
A date report often needs a start and an end. Confirm whether column D stores date values, date-times or text. If it holds date-times, comparing to an end date at midnight can exclude entries later that day. A useful pattern is to include the start and exclude the next day after the end when your selectors hold ordinary date values. Put the first date in G1 and the last date in H1.
=FILTER(A2:D100,D2:D100>=G1,D2:D100<H1+1)Check a record on the start day, one later on the end day and one just after the window. If the column contains text rather than dates or the selectors include times, confirm or fix the data before using this pattern. The example assumes the date selectors represent whole calendar days.
Keep empty dates out when the report needs a scheduled record
A blank date is a separate case. If the rule should only include dated entries, check for a nonblank value as well. Use a sample with a missing date and verify the output. If the sheet uses a formula that returns an empty string, decide whether that should count as empty under your rule. Be explicit before a date summary drives a decision.
Keep the window and status tests separate
A weekly dashboard might show only tasks that are Done and fall inside a date range. Add separate conditions for status and each boundary. Test a task that is Done outside the window and a task within the window that is still In progress. Both should be excluded if all three tests must pass.
=FILTER(A2:D100,C2:C100="Done",D2:D100>=G1,D2:D100<H1+1)Use the correct date column for the question. A Due date and a Completed date have different meanings. If you are reporting work completed during the week, a deadline column might give a plausible list that answers the wrong question.
Back to contents · Next section
Use an “All” option in a reporting dropdown
A dropdown used as a selector can include All. A simple equality comparison would look for the literal value All in the source. Give the formula a separate branch that returns ordinary records when All is selected. In this example column A contains a task name for every real record, and G1 is the selector.
=IF(G1="","Choose a status",IFNA(IF(G1="All",FILTER(A2:D100,A2:A100<>""),FILTER(A2:D100,A2:A100<>"",C2:C100=G1)),"No matching rows"))Check that column A is an appropriate nonblank key. If a real row can lack a task name, choose a more reliable key column. The All branch excludes unused rows. The specific-status branch also excludes unused rows and compares the status with G1. Test both branches and the blank selector before sharing the dashboard.
Decide whether All should ignore every filter
A dashboard can have a status dropdown and an owner selector. If All refers to status, the owner condition may still need to apply. Design the formula around that meaning. Test All with an owner selected and compare the result with the source. Avoid using a broad branch that silently drops the owner restriction.
Use plain labels for the reporting rule
Write a clear label such as Status above the selector. If All means “all current tasks for the selected owner,” make that clear where the reader needs it. Keep the selector outside the result range. A dashboard works best when its behaviour is clear from the worksheet, without requiring the next editor to reverse-engineer every condition.
Back to contents · Next section
Create a view that returns only the fields the reader needs
Suppose a worksheet has Task, Owner, Status and Due date. A student might need only Task and Due date. A separate report can select the appropriate columns using a suitable range or supported array construction, then filter by the aligned status column. First verify the complete rows and then decide which fields should appear.
Keep conditions attached to the same source rows
If you return only column A and use column C as the condition, both ranges still need to refer to corresponding rows. Changing the return width does not change that requirement. If you copy the result to another sheet, use the correct sheet references. A source tab named Tasks can be referenced explicitly.
=FILTER(Tasks!A2:D100,Tasks!C2:C100=G1)Here G1 belongs to the sheet containing the formula. Confirm that it contains the intended choice. If the selector lives on a different sheet, reference that location deliberately. Check the named source sheet and data ranges before extending the report.
Add the headers separately or include them intentionally
A data-only range beginning in row two leaves out the header. Put appropriate headings above the output. Check their order whenever you change the columns returned. If you also display a message for no matches, make sure it sits in an understandable place under the headings. A header is part of the report, even when it is outside the formula.
Treat access as a separate decision
A filtered worksheet inside the same file can still be part of a spreadsheet that contains the full source. If the recipient should only receive a particular subset, use an approved separate output or sharing arrangement. Verify which information the recipient can actually access. A filtered display is a report design choice, not a permission boundary.
Back to contents · Next section
Troubleshoot filters after the source grows or changes format
A report may work today and miss a new record tomorrow because it has reached the last row in a fixed range. Open the formula and inspect the source and every condition. If you extend the range, extend all row-based conditions to match. Then add or inspect a known record near the new boundary and verify that it appears.
Use open-ended ranges consistently when appropriate
An open-ended range can include future rows. If you use A2:D as the return range, the corresponding row condition should also start at row two and extend appropriately. Check that unrelated tables or notes below the source do not become part of the report. For large or complex workbooks, choose a range structure that fits the actual performance and maintenance needs.
=FILTER(A2:D,C2:C="Done")The example matches the status label and leaves rows with blank statuses out. If you change the criterion to an empty selector, consider whether unused rows could match. Add a suitable nonblank key condition if needed and test the no-selection case.
Inspect imported statuses and dates
A new data source can introduce text where the earlier table used dates, or a status label with a trailing space. Compare one known record from before and after the import. Verify the input values and use an approved cleaning or type-conversion step when required. Fix the source or working copy before hiding errors in the report.
Check the result space after someone adds a note
An output can expand into an area where another editor typed a value. Move notes away from the result range or place the report on a separate sheet as appropriate. If a new result suddenly cannot expand, inspect the destination before rewriting the condition. Keep the output’s expected width and growth in mind.
Back to contents · Next section
Practise with a test table and expected results
Use four harmless rows. Give Maya a Done task and an In progress task. Give Arjun a Needs review task and a Done task. Before writing any formula, predict the results. Done should return two rows. Done and Maya should return one. Done or Needs review should return three. Maya and either of those statuses should return one. Run the formulas and compare the results.
Change one condition and inspect the difference
Change Maya’s In progress task to Needs review. Recheck the AND and OR results. The Done-only counts stay the same, and the OR report gains one row. If the result does not change as expected, inspect the exact label and the referenced range. Try a blank selector and a value with no matches. These checks reveal whether the report handles normal use.
Add a date boundary
Give two tasks dates at the beginning and end of a test window. Give a third task a time later on the end day if your sheet stores date-times. Use the inclusive-start and exclusive-next-day formula and verify the expected rows. Then change the end date and check the new boundary. Keep the test dates separate from any real work.
Test a copied formula on another sheet
Put the source table on Tasks and place the report on a different sheet. Use explicit source references and a local selector. Check that the output has enough empty space. Add one new source row and confirm that the range and conditions include it. Use the same test when you later change the master table.
Back to contents · Next section
Review the report before someone uses its numbers
Read the label above the report and confirm that the formula matches it. Inspect one included record, one excluded record and the last row you expect to include. Check the empty result message, source range and output area. If the report is a deliverable, verify whether the recipient needs a live spreadsheet or a fixed authorised copy.
Keep a result reproducible
Where the workflow allows, record which source and criteria produced an accepted output. A live filter can change after the source is edited. If the task requires a fixed snapshot, create the permitted copy after checking its values. Preserve the approved source and the record of what was shared where necessary.
Ask a reader to explain the result
A teammate should be able to tell which status, owner or date range the report uses. If they cannot, improve the label or worksheet layout. Confirm that the output headers match the fields. This review matters as much as the formula itself when people use the report to choose what to work on next.
Back to contents · Next section
Filter rows and columns in separate steps
A single FILTER call uses conditions in one orientation. If a report needs certain rows and certain columns, build the row result first and inspect it. Then use a compatible column filter on that result. In this example, A2:D100 contains four source columns, column C holds status, and A1:D1 contains the category Include or Exclude for each source column.
=FILTER(FILTER(A2:D100,C2:C100="Done"),A1:D1="Include")The inner FILTER keeps matching rows while retaining the four-column shape. The outer FILTER keeps the columns whose corresponding category is Include. Verify the inner result and the column labels separately before relying on the combined expression. If the inner formula has no matches, handle the no-match state in a way that makes sense for the final report.
Keep headings aligned after a column filter
If you use a separate header row, filter or arrange it to match the columns that remain. Check that each displayed heading sits over the right field. A value can be accurate and still mislead the reader under the wrong heading. Test with a source where the first and last columns have different labels.
Avoid filtering the same information twice unintentionally
If the source is already a formula result, check which rows it contains before applying another condition. A visible filter view and a formula output are different layers. Decide which source the report should read and verify a row that is present in one view but not the other. The final report should show exactly the intended selection.
Back to contents · Next section
Test a FILTER formula after sharing or copying the sheet
When you copy a report to another spreadsheet or move its selector, inspect the references. The formula may still read the old source or an empty selector. Check which sheet contains the inputs and which contains the output. Choose a known record and change its status in an authorised test copy. Confirm the report updates from the intended source.
Check what happens when a required source value is unavailable
If a status or date is missing, decide whether the row should be excluded, flagged or shown separately. Add a suitable explicit test rather than letting a blank criterion accidentally include it. Check a blank row, a real row with a missing field and a complete matching row. The report should behave predictably in all three cases.
Keep a small control total
For a simple status report, compare the number of returned rows with a manually checked sample or another approved count. When there is a difference, inspect the exact comparison, the date boundaries and the source range before changing the formula. Keep the check tied to the same criteria, so the two results are comparable.
Back to contents · Frequently asked questions
Frequently asked questions
What does FILTER do?
It returns rows or columns from a source range when corresponding conditions are true.
How do I filter with two AND conditions?
Add both conditions as separate arguments. Each returned row must satisfy both.
How do I filter with OR?
Combine the tests into one condition, such as adding the two Boolean results and checking whether the sum is greater than zero.
Why is FILTER returning #N/A?
Google Sheets uses #N/A when there are no matches. Check the criteria, source values and any source errors before wrapping the result in IFNA.
Why can’t the result expand?
The destination may contain values or other obstacles. Move the formula to a clear area and leave space for growth.
Can I filter columns instead of rows?
Yes. Use a condition that matches the width of the returned range. Keep row and column conditions separate in a single FILTER call.
Does FILTER change the source table?
The formula returns a separate result. Edit the source when you want to change the underlying records.
How do I filter a date range that contains times?
When selectors are whole dates, use an inclusive start and an exclusive boundary at the next day after the end date. Verify the source type and a record late on the last day.
Can a dropdown control FILTER?
Yes. Compare the condition range with the selector cell and handle blank or All separately when those choices have special meanings.
Why are new rows missing from my report?
Check the last rows of the return and condition ranges. Extend them consistently and verify a known new record.
Can a separate report use data on another sheet?
Yes. Reference the source sheet explicitly and keep the return and condition ranges aligned. Verify which sheet contains the selector.
Helpful reading
- Google Docs Editors Help on the FILTER function
- Google Docs Editors Help on IFNA
- Google Docs Editors Help on filters and filter views
- How to Share a Google Drive File Without Access Problems
- How to Convert Excel to PDF Without Cutting Off Columns
- How to Make a Dropdown List in Google Sheets
- How to Highlight and Remove Duplicates in Google Sheets
- How to Split Text Into Columns in Google Sheets
Test one included row and one excluded row
A useful filtered result follows an understandable rule, has room to expand and still matches the intended source when new records arrive.