Filtering in Google Sheets: How to Narrow and Sort Data

To filter data in Google Sheets, select a cell in your table and choose Data > Create a filter. Use the filter icon in a column header to choose which rows to show; filtering hides nonmatching rows without deleting them.

What filtering does in Google Sheets

A filter temporarily narrows a table to rows that meet criteria you choose. For example, a sales list might contain orders from several regions and months. Filtering the Region column to show “West” leaves the West rows visible and hides the others, while the underlying records remain in the sheet. Clear the filter to see the full list again.

Filters are useful when you want to inspect a subset of a larger dataset without making a separate copy. You can filter by the values already present in a column, by rules such as “is greater than,” or by other conditions. You can also sort visible data from a filter menu.

A filter is not the same as deleting rows, moving data to another sheet, or restricting access. It changes what is displayed, not which records exist. Anyone with sufficient access to the spreadsheet may be able to clear a filter and see rows that are currently hidden.

Create and apply a basic filter

Before applying a filter, make sure the data forms a consistent table. Put a clear heading at the top of each column, keep each record on one row, and avoid blank rows or unrelated content inside the range. If the sheet has several separate tables, select the intended range first so the filter does not include the wrong cells.

Click a cell in the table, or select the full range, including its header row.
Choose Data > Create a filter. Filter icons appear in the header cells of the selected range.
Click the icon in the column you want to filter.
Choose values or a condition, then click OK or apply the selection in the menu.

For example, if a table has columns named Date, Region, and Sales, click the Region header’s filter icon and select only “West.” Rows for other regions are hidden. To return to the full table, open that column’s menu and clear the filter or choose all values. You can apply criteria in more than one column; a row remains visible only when it meets the active filters.

If Google Sheets guesses the wrong range, undo the filter and select the exact table before creating it. A filter applied to a range with no genuine header row can treat the first data row as headings, making the menus confusing.

Filter by values, conditions, and color

Filtering by values is convenient when a column contains a manageable set of distinct entries. In the filter menu, use the checkboxes to select the values to display. This is handy for a list of departments or statuses, such as showing only “Open” and “In progress.” If there are many unique values, the menu’s search box can help locate one.

Choose Filter by condition when you want a rule rather than a hand-picked list. Available options depend on the kind of data in the column. Text can be filtered by rules such as containing or beginning with a phrase; numbers can use comparisons such as greater than or less than; dates can be filtered using date-related conditions. A custom formula condition is useful when the built-in choices do not express the rule you need, but the formula must evaluate appropriately for the rows in the filtered range.

Google Sheets also provides filtering by fill color or text color when color is used consistently as a marker. For instance, a team might color overdue items red and then show rows with that fill color. Color can be a quick visual aid, but it is a fragile substitute for a dedicated Status column: colors may be applied inconsistently, and they do not explain what the color means to another reader.

By values: show selected entries, such as two named regions.
By condition: show entries that meet a rule, such as a sales amount over a threshold.
By color: show rows marked with a chosen fill or text color.

Sort rows without separating their data

Sorting changes the order of records; filtering changes which records are visible. The filter menu can do either. For example, you can filter a Status column to show open tasks, then sort a Due date column from earliest to latest to prioritize them. When sorting from a column’s filter menu, make sure the filter range covers every column belonging to each record. Otherwise, sorting only one column could separate values from the rows they describe.

Sorting is not a way to permanently remove records. Similarly, filtering does not sort the full dataset unless you explicitly choose a sort option. If you only need to sort, use the sort option in the relevant menu or select a complete range and use the sorting commands under Data. Check that the header row is identified correctly so it stays at the top rather than being sorted as data.

Use filter views for separate ways of looking at a sheet

A standard filter is shared as part of the sheet’s current view: changing it can change what other collaborators see in that sheet. A filter view is a saved, named set of filtering and sorting choices that lets someone examine the same data differently. In Google Sheets in a browser, open Data > Filter views > Create new filter view, then set criteria using the header menus. You can name the view and close it when finished.

Filter views are useful when colleagues need different slices of one shared table. One person might save a view for West-region orders while another uses a view for overdue tasks. They do not need to keep changing the shared standard filter back and forth. A filter view can also be useful for returning to a recurring review setup instead of rebuilding the same criteria each time.

To use an existing view, open Data > Filter views and select its name. People with access to the spreadsheet may be able to open a saved view, depending on their permissions. Sharing a view is not a security measure: it does not hide the underlying records from someone who can access the sheet. Filter views are a Google Sheets feature; Excel has its own filtering and view features, but the menus and collaboration behavior are not identical.

When a FILTER formula is a better fit

The sheet’s FILTER function creates a separate result containing rows that meet conditions. Unlike a menu filter, it leaves the source table’s visible rows alone and displays matching records wherever the formula is entered. That is useful for a report, dashboard, or another tab that should update as source data changes.

Suppose A1:C5 contains a header row followed by four orders, with order numbers in A, regions in B, and sales amounts in C. The records are 101 / West / 140, 102 / East / 90, 103 / West / 75, and 104 / West / 220. With the source data in A2:C5, regions in B2:B5, and amounts in C2:C5, this formula returns orders 101 and 104 because they are West sales over 100:

=FILTER(A2:C5,B2:B5="West",C2:C5>100)

Place the formula in an empty area with enough room for the matching rows and columns to expand. Each condition range must align in size with the data range. If there are no matches, FILTER returns a no-match error; you can wrap it in IFNA to display a friendlier message. For example, with the same table in A2:C5 and regions in B2:B5, the following returns “No matching rows” when no order is in the North region:

=IFNA(FILTER(A2:C5,B2:B5="North"),"No matching rows")

Use menu filters when you want to interactively inspect the original table. Use the formula when you need a live, separate output that can feed another report. In Microsoft Excel, the Data tab’s Filter command provides comparable interactive filtering, and modern Excel versions also have a FILTER function; the interface and some behavior differ from Google Sheets.

Common filtering problems and fixes

The filter icon is missing: Check that a filter was created with Data > Create a filter and that you have permission to edit the sheet. Confirm you selected the table range rather than an unrelated cell.
Some rows do not respond to the filter: They may be outside the filtered range. Recreate the filter with the full dataset selected, including all relevant columns and rows.
Expected values are absent: Check spelling, extra spaces, inconsistent capitalization, and whether some cells are blank. “West” and “West ” may not behave as the same text value in every operation.
A formula result will not expand: Clear cells in the destination area that could block the array output, and check that the condition ranges match the data range in height.
A sort appears to scramble the table: Undo immediately if possible, then confirm the entire record range was included before sorting. A blank row or a partial selection can make the intended table boundary unclear.

For reliable results, keep one record per row, use consistent data types in each column, and give columns descriptive headers. If you need to preserve a particular view for yourself or collaborators, create a filter view rather than repeatedly changing a shared filter.

Frequently Asked Questions

Does filtering in Google Sheets delete rows?
No. A filter hides rows that do not meet its criteria. Clear the filter to show them again; use a deletion command only when you intend to remove records.

Can I filter more than one column at a time?
Yes. Apply a criterion in each relevant column. The visible rows must satisfy all active column filters, so combining Region = West with Status = Open shows only records meeting both conditions.

How do I remove a filter in Google Sheets?
To clear a particular criterion, open its header menu and clear the selection or restore all values. To remove the filter controls, choose Data > Remove filter.

What is the difference between a filter and a filter view?
A regular filter changes the sheet’s current filtered display. A filter view saves a named arrangement of criteria and sorting so people can use different views of shared data without continually changing the standard filter.

Can I filter data with a formula?
Yes. Google Sheets’ FILTER function returns matching rows in a separate output range. Use the header filter menus instead if you want to hide nonmatching rows in the original table.

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.