How to Filter Data in Google Sheets

A filter hides the rows you don't want to see, without deleting anything. It's the fastest way to look at only one category, one customer or one month in a long table, and to clear it again in one click.

Create a filter

1. Click any cell inside your table (or select the whole table including the headers).
2. Go to Data → Create a filter. A filter icon appears in every header cell, and the column letters above the table turn green.
3. Click the filter icon in the column you want to filter.
4. Under Filter by values, untick the values you want to hide. Use Clear to untick everything and then tick only the ones you want.
5. Click OK.

Filter by condition

In the same filter menu, open Filter by condition to filter on a rule instead of specific values, for example:

• Greater than 50 to see large expenses.
• Text contains "invoice".
• Is empty to find rows with missing data.
• Date is after a specific date.
• Custom formula is for anything else, such as =MONTH(D:D)=9.

Sort from the filter menu

The filter menu also has Sort A → Z and Sort Z → A. These sort the whole filtered table by that column, keeping rows together. For more sorting options, see how to sort data in Google Sheets.

Remove the filter

Go to Data → Remove filter. All hidden rows come back.

Filter without affecting other people

A normal filter changes what everyone sees in a shared sheet. If others are working in the file too, use a filter view instead:

1. Go to Data → Create filter view.
2. Set your filters as usual. Only you see the result.
3. Close the view with the X at the top right. Saved filter views can be reopened later from Data → Change view.

The FILTER function

If you want the filtered rows copied to another place and kept up to date automatically, use a formula:

=FILTER(A2:C7, A2:A7="Food")

Add more conditions separated by commas; a row appears only if it meets all of them:

=FILTER(A2:C7, A2:A7="Food", C2:C7>10)

Frequently asked questions

Why doesn't my filter include all rows?

The filter range stopped at an empty row. Remove the filter, select the whole table including every row, and create it again.

Why can't I find "Create a filter"?

There's already a filter on the sheet. In our test, the Data menu then showed Remove filter in its place. Each sheet can have only one; use filter views if you need more.

Why does it say "You can't create a filter over a header containing vertical merges"?

The range has cells merged across several rows. Unmerge them, or start the filter below a merged title. See the vertical merges error.

Do SUM and COUNT ignore hidden rows?

No, they include filtered-out rows. Use =SUBTOTAL(109, C2:C7) to add up only the visible rows. With the Food filter on in our test, SUM still gave 244.60 while SUBTOTAL gave 97.50.