Google Sheets Filter: Show Only the Rows You Need

To filter data in Google Sheets, select a cell in your table and choose Data > Create a filter; then use the filter button in a column header to choose which rows to show. Filtering hides rows that do not meet your criteria—it does not delete them—so you can focus on a subset while keeping the full dataset available.

What a filter does in Google Sheets

A filter temporarily displays only rows that match conditions you choose. For example, a customer list can be narrowed to one region, or a task tracker can show only items marked Open. The other rows remain in the sheet but are hidden from view until you change or remove the filter.

Filtering is different from sorting. A filter narrows the visible records; sorting changes their order. You can use both together—for instance, show only orders from one month and then sort the remaining orders by date. A filter also differs from deleting rows: deleting removes data, while filtering changes only what is currently displayed.

Filters are most reliable when the data is arranged as one rectangular table: one header row followed by records, with a consistent field in each column. A blank row or a separate note inside the table can make Sheets interpret the range in an unexpected way. If a sheet contains several unrelated tables, create a filter for each table rather than applying one filter across all of them.

Create a basic filter

In Google Sheets in a web browser, click a cell inside your dataset. Choose Data > Create a filter. Sheets adds filter buttons to the header row and outlines the filtered range. If the data has no header row, add clear column names first; otherwise, the first data row may be treated as the header.

Check that the range includes every column and record you want to filter, but not unrelated data.
Choose Data > Create a filter. If prompted or if the wrong range is outlined, adjust the selected range before creating the filter.
Click the filter button in the header of the column that contains the field you want to use.
Choose values to keep or select a condition, then apply the selection.

For example, suppose column A contains product names, column B contains categories, and column C contains stock counts. Open the filter button in column B and keep only the category Kitchen. The sheet will show the matching product rows, including their stock counts, while hiding rows from other categories. The header stays visible so you can still identify the columns.

To remove the filter and show all rows again, choose Data > Remove filter. Removing it does not undo edits or restore a prior sort order; it simply turns off the filter controls and reveals the records in the range.

Filter by values or by condition

The column menu offers two useful approaches. Filter by values presents the distinct entries found in that column, often with a search box and checkboxes. It works well when the possible values are known, such as a list of department names or order statuses. Clear Select all, then select only the entries you want to see. Search can help locate a value in a long list.

Filter by condition is better when you need a rule instead of a particular list of entries. Depending on the kind of data in the column, options include rules such as text contains, text starts with, date is before, is greater than, or is empty. For a sales column, for instance, a condition greater than 500 can show high-value records without needing to check each amount individually.

Use Filter by values for a specific, manageable set of categories, names, or statuses.
Use Filter by condition for thresholds, date ranges, text searches, blanks, or other rule-based criteria.
Check the column’s underlying data when a condition appears not to work. Numbers stored as text may not behave like numeric values, and inconsistent spelling can split one intended category into several values.

Date and number filters depend on the data being recognized as dates and numbers. A date that was pasted as plain text may not sort or compare as expected. Likewise, values such as 1,200, $1,200, and text that merely looks numeric can be interpreted differently if the column is inconsistently formatted. Correcting the source data is usually more dependable than trying to compensate with increasingly complicated filter choices.

Combine filters across columns

You can apply a filter in more than one column at once. For example, filter the Status column to Open and the Region column to West. Sheets then displays rows satisfying both selections: the record must be Open and in the West region. This is useful for progressively narrowing a large table without changing its contents.

Within one column, selecting several values generally means keep any of those selected values. If you select Open and Pending in a status column, rows with either status can remain visible. When you add a condition in another column, it narrows that result further. Before concluding that data is missing, inspect each column’s filter menu; an earlier selection may still be active.

For a more involved workflow, make the criteria explicit. A support team might choose Status is Open and Priority is High, then sort by the date received. That shows urgent open cases while retaining the rest of the records in the sheet. If several people need different views of the same data, filter views are often a better fit than changing the shared basic filter.

Use filter views for separate or shared workflows

A basic filter changes the filtered display for the sheet, which can affect what collaborators see while they are working. A filter view lets a user create and save a particular filtered arrangement without forcing everyone to use the same view. This helps when a shared sheet is used by, for example, sales, operations, and customer support teams that need different subsets.

To create one in the browser, choose Data > Filter views > Create new filter view. Set the criteria using the column controls, then give the view a recognizable name in the filter-view bar. Reopen it later from Data > Filter views and choose the saved view. The exact availability and controls can depend on access permissions; someone with view-only access may not be able to create or change saved views.

Use a basic filter when one current selection is appropriate for the people editing the sheet. Choose a filter view when you need a saved perspective, want to switch among different sets of criteria, or do not want to impose your current view on collaborators. Neither approach creates a separate copy of the records, so edits to the underlying data can affect what appears in a view.

Use the FILTER function for a formula-driven result

The filter controls are convenient for interactive work. The Google Sheets FILTER function is useful when you want matching rows to appear as a formula result in another area or sheet. Unlike the toolbar filter, a formula returns a separate result range and updates as the source data changes. It does not hide rows in the original table.

Assume product names are in A2:A6, owners in B2:B6, and status in C2:C6, with headers in row 1. If C2:C6 contains Open, Closed, Open, Pending, and Open in order, enter =FILTER(A2:C6,C2:C6="Open") in an empty cell. The result is rows 2, 4, and 6 of the source range—the products and owners on those three Open records. Keep the output area clear so the returned rows have room to expand.

For multiple criteria, provide an additional condition range of the same height. With the same layout and priority values in D2:D6, =FILTER(A2:C6,C2:C6="Open",D2:D6="High") returns only rows that are both Open and High priority. The condition ranges must align with the rows being filtered. If no rows match, FILTER returns a no-match error rather than an empty table; you can plan for that possibility in the sheet or use a suitable error-handling formula.

Choose the formula when results need to feed another calculation or remain visible in a separate report area. Choose the column filter when you want to inspect and interact with the original table. A formula result is not a replacement for a filter view: it does not provide the same saved interactive controls, and users should edit the source records rather than treating the generated output as the original data.

Common filter problems and fixes

If the filter button is missing, confirm that a filter is active and that the intended range was selected. Choose Data > Create a filter after clicking within the table. If the buttons appear on the wrong row, check whether the range includes an extra title row or omitted the actual headers. A clean, single header row makes the controls easier to interpret.

If expected records disappear, open each relevant column menu and check the selected values and conditions. Filters from multiple columns work together, so a restrictive choice in one column can account for an unexpectedly small result. Also look for hidden rows, blank cells, extra spaces, and inconsistent spellings such as West and west. Those can make records appear to be missing or create separate checkbox entries.

If new rows are not included, check whether they were added inside the filtered range or below it. Data added beyond the range may not inherit the active filter. Expand or recreate the filter range to cover the added records. For ongoing datasets, leaving space for new rows or using a well-maintained continuous table range can reduce this issue.

If collaborators report that their view changed, determine whether you used a basic filter or a filter view. A basic filter is shared at the sheet level; a filter view is intended to let people work with separate saved views. When several people are editing, agree on the range and use filter views where independent criteria are important.

When filtering is the right choice

A filter is a strong choice for exploring a list, reviewing exceptions, or temporarily focusing on records that meet a few criteria. It is simple to change and does not require a formula. A filter view is preferable for repeated team workflows, while FILTER is useful for a live, formula-generated extract. Sorting is preferable when every record should remain visible but be arranged in a more useful order.

Filtering is not a way to secure sensitive information. Hidden rows remain part of the spreadsheet and may be revealed by someone with access. If certain people must not see particular records, use appropriate sharing and access controls or keep restricted information in a separate file with limited permissions. Hiding data with a filter is a convenience, not a privacy boundary.

Google Sheets and Excel both have filter features, but their interfaces and some advanced behavior differ. The menu paths above are for Google Sheets in a web browser; do not assume an Excel ribbon path applies. If you are following instructions for a different spreadsheet program, check that program’s own menus and documentation.

Frequently Asked Questions

How do I filter one column in Google Sheets?
Click a cell in the table, choose Data > Create a filter, and click the filter button in that column’s header. Select the values to keep or choose a condition.

Does filtering delete or change rows?
No. A filter hides rows that do not match the current criteria. Remove the filter to display them again. Deleting or editing records is a separate action.

Can I filter by more than one condition?
Yes. Apply criteria in multiple column menus to narrow the displayed rows. A row must satisfy the active criteria across those columns. For a formula-based extract, FILTER can also take multiple condition ranges.

How do I show blank cells only?
Create or use a filter on the relevant column, open its filter button, and choose a blank-related option under Filter by condition if available. Check that cells are genuinely empty; a formula returning an empty string is not always equivalent to a cell with no content.

Why can’t my collaborator see the same filter?
A basic filter affects the sheet’s shared display, while a filter view is designed for a separate saved view. Check which one is active, and confirm that the collaborator has permission to access or edit the sheet and its filter views.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.