How to Filter Data in Google Sheets

To filter in Google Sheets, select your data range, choose Data > Create a filter, then open a column’s Filter control, choose the values to show, and click OK. On Android, iPhone, and iPad, start from More > Create a filter instead. A regular filter affects everyone viewing the spreadsheet, so use a filter view on a computer when you want a personal view.

Create a filter on a computer

Open your spreadsheet and select the range you want to filter. Choose Data > Create a filter, then click the column’s Filter control. Creating or changing a regular filter requires editing access.

You can also right-click the selected range and choose Create a filter. Alternatively, select the range and click the funnel-shaped Filter button in the toolbar. Each route gives you column controls for choosing what to display.

For a quick match, right-click a cell containing the entry you want and choose Filter by cell value. Use the column’s Filter control when you need several values or a different rule.

Choose the values you want to show

Click a column’s Filter control and open Filter by values. Use the search box to find entries, or browse the available values. The checkbox labels come from your spreadsheet’s contents.

Click Clear, then check the values you want to retain. Alternatively, click Select all and uncheck the values you want to exclude. Finish by clicking OK.

Filter by a condition or color

Open the column’s Filter control and choose Filter by condition to apply a rule. Select the condition you need, supply its criteria, and click OK.

For color filtering, open Filter by color in the same control. Choose the text color or fill color you want to retain. You can filter conditional-formatting colors, but you cannot filter alternating colors.

For a custom test, choose Filter by condition > Custom formula is. Enter a Boolean formula, then click OK to apply it. For data beginning in row 2, this example checks column A for text matching Good or Great:

=REGEXMATCH($A2,"Good|Great")

Save a personal filter view

On a computer, click a cell containing data and choose Data > Create filter view. Open a column’s Filter control, configure your selection or condition, and click OK. Your filter view leaves other collaborators’ views unaffected.

To keep the setup, click Save view, enter a name, and click Save. Saving requires edit access. With view-only access, you can create a temporary filter view, but you cannot save it.

Reopen a saved view through Data > Change view, then select its name. To share that particular view, copy its active browser URL and send the link to someone who has spreadsheet access.

For a structured table, use Views > Create filter view. To make a table in Google Sheets first, select the range and choose Format > Convert to table. Configure the column filtering and click Save view, or choose Views > View options > Save view. Reopen it through the Table menu by selecting the saved view’s name.

Filter in the phone or tablet app

In the Google Sheets app on Android, iPhone, or iPad, open the spreadsheet and tap More > Create a filter. Tap the column’s Filter control to open Sort and filter. Tap Search and enter a value, or browse the value list.

Uncheck unwanted values, or tap Clear and select the values you want to retain. Tap Select all to restore the complete selection. Value-selection changes apply automatically in the mobile apps.

To apply a rule or color, tap Filter by condition or Filter by color, then tap the down arrow and choose the required option. Complete the criteria and tap Done. To remove an individual condition, tap Filter > Filter by condition > Remove.

You need editing access to use regular mobile filters. Switch to a computer when you need a personal filter view. You can use the FILTER function in the mobile apps when you need separate results.

Put filtered results in a separate range

Select an empty output cell and enter a FILTER formula to create a separate, updating result range. Leave space for the output, then press Enter on a computer or tap Done on mobile. This example returns rows from A2:C100 where column C is greater than 1,000:

=FILTER(A2:C100,C2:C100>1000)

Replace the example range and criteria with the ones your spreadsheet needs. To require multiple conditions, add condition arguments. This version requires both a value greater than 1,000 in column C and East in column A:

=FILTER(A2:C100,C2:C100>1000,A2:A100="East")

For an either-or match, combine the conditions with a plus sign. This formula retains rows where column A equals East or West:

=FILTER(A2:C100,(A2:A100="East")+(A2:A100="West"))

You can also use another cell as the criterion to change the results without editing the formula. To choose from preset entries, create a drop down list in that cell. This example uses the value in E1:

=FILTER(A2:C100,A2:A100=E1)

Use formulas for more specific subsets

Use QUERY to filter a range with a WHERE clause. Enter the following formula in an empty output cell, then press Enter or tap Done on mobile. The final 1 specifies one header row:

=QUERY(A1:C100,"select * where C > 1000",1)

Adapt the range, selected columns, and condition to your data. Check columns containing mixed data types, because QUERY can return null values for minority data types.

Use UNIQUE to return distinct rows in a separate result range. To remove duplicates in Google Sheets from the original data, select the range and click Data, then Data cleanup, then Remove duplicates. That removes the repeated rows from the range itself, so copy the data first if you may need them. For separate results, enter this formula in an empty cell, then press Enter or tap Done on mobile:

=UNIQUE(A2:C100)

Use SORTN for a ranked subset, such as the ten highest values. This example returns up to ten rows ranked by column 3 in descending order. Enter it in an empty output cell, adjust the arguments for your ranking, then press Enter or tap Done on mobile:

=SORTN(A2:C100,10,0,3,FALSE)

Fix missing results or formula errors

If a value is missing from your filtered sheet, reopen Filter by values and review the selected entries. Search helps you locate a value, while the checkboxes determine what you retain. Adjust the selection and click OK again.

If a FILTER formula fails, check that each condition range matches the corresponding length of the range being filtered. Make sure you have space for the output and use separators appropriate to your spreadsheet’s locale. When no rows match your criteria, FILTER returns #N/A, so review the criterion if you expected results.

Remove a filter or close a view

To remove a regular filter on a computer, choose Data > Remove filter. You can also right-click and choose Remove filter. In the Android, iPhone, or iPad app, tap More > Remove filter.

For a filter view, click Close to leave the view. To permanently remove a saved filter view, choose Options > Delete view. Permanent changes require editing access.

Filters, filter views, and slicers control what you see, but they do not restrict access to the underlying data. Do not use them to conceal information from people who have access to the spreadsheet.

Frequently Asked Questions

Can you open a column filter with the keyboard?

Select the filtered column’s header cell and press Ctrl + Alt + R on Windows or ChromeOS, or Control + Command + R on Mac. Use Tab to move through the controls and Enter to expand options or toggle values, then activate OK. With screen reader support enabled, you can also choose Accessibility > Open filter dropdown menu.

How do you find the filtering command without using the menus?

Select your data range, then press Alt + / on Windows or ChromeOS, or Option + / on Mac to open Tool finder. Type Create a filter, select the matching command with Down arrow, and press Enter. Google assigns Ctrl + Shift + L to Left align by default.

How do you filter a pivot table?

Click the pivot table’s Edit button, then click Add beside Filters and choose a field. Open Showing all items using its down arrow, configure Filter by condition or Filter by values, and click OK. New source values may require updating a value-based filter.

Can one control filter charts and pivot tables?

Use a slicer to filter charts and pivot tables sharing the same dataset. Select the chart or pivot table, choose Data > Add a slicer, and choose the filtering column in the side panel. Click the slicer to configure Filter by condition or Filter by values, and add separate slicers for additional columns. To change an existing slicer’s criteria, click its Filter control; viewers can adjust existing slicers, but editors must create them.

Are slicer selections saved automatically?

Your slicer selections remain private and unsaved unless an editor sets them as the shared default. As an editor, click the slicer’s More > Set current filters as default to keep that configuration.

Can you return only rows that occur exactly once?

Enter =UNIQUE(A2:C100,FALSE,TRUE) in an empty output cell, then press Enter or tap Done on mobile. The final TRUE limits the results to rows that occur exactly once, excluding rows that have duplicates.

Can one FILTER formula filter both rows and columns?

To filter both dimensions, nest one FILTER call inside another. Each call’s conditions must match the corresponding row or column length of the range it filters.