Filter your data

To easily view and analyze data in a spreadsheet, you can use filters to hide data that you want to temporarily take out of view. You can also create, name, and save filters, called filter views, for use without disrupting how others are viewing your spreadsheet.

Using filters in a spreadsheet

To temporarily hide data from view in a spreadsheet, you can add a filter.

Note: When you add a filter, it will filter the data for all of your spreadsheet’s viewers and collaborators, meaning any editor has the ability to change the filter.

From the Data menu, select Filter. Alternatively, you can select the Filter toolbar icon in the toolbar.

The filter will be applied to your selected range of cells.

To help you see what cell ranges have a filter applied, the column and row labels are colored green. Additionally, the filter toolbar icon will also change to green if a filter has been applied to any column in your spreadsheet.

Clicking the drop down icon in the header row will give you a set of filtering options.

From the drop-down menu, you can uncheck the data points that you want to hide from view and check data points that you want to keep in view. Once you’ve done so and clicked OK, the icon in the header row will change to a filter.

Note: Only one filter is allowed per spreadsheet in order to prevent collaborators from overwriting each other’s data. The filter will show for all editors and viewers, and it can be added or removed by any person with editing rights.

To disable autofilter, follow these steps:

From the Data menu, select Filter. Alternatively, you can deselect the filter icon in the toolbar.

The filter will be removed from your selected range of cells. Drop-down menus will disappear from the first row of the selected range of cells.

All filtered rows will be visible again, but data you’ve sorted will remain sorted.

Filtering will temporarily hide some of the rows in your spreadsheet, allowing you to view only the data you’d like to see. Once you’ve enabled the filter, click the drop down icon in the header row to view a set of filtering options. Then, you’ll be able to check and uncheck individual data points that you want to view or filter out.

If you want to complete a bulk action, you can click Select all or Clear to either check or uncheck all of your data points in a given column.

You also have the ability to search for particular data points within a column that has a filter applied. Typing "P," for example, will shorten the list to just the names that start with P. You can click on Select all or Clear to perform bulk actions on the visible items. For example, clicking Clear would deselect Peter and Phil.

When you have a filter applied to a range of cells, you can also sort the data. When you sort your data using the drop-down menu, only the data contained in the filter range will be sorted. You can sort a particular column by selecting the drop-down menu and clicking either Sort A → Z or Sort Z → A.

Create, name, and save a filter view

If you want to filter your data that you can save and come back to later, or that you don't want to disrupt how others are viewing the data, you can use a filter view.

Click the Data menu, hover over “Filter views…” and select the name of the filter view you want to use. You can also click the down arrow to the right of the filter icon and select the filter view.

Your filter will now be applied to the spreadsheet. To turn off the filter view, either click the X in the top-right corner of the spreadsheet or click the down arrow to the right of the filter icon and select None.

If you only have access to view a spreadsheet, you can still use filter views in two ways:

Apply existing filter views, which can be found by clicking the Data menu > hovering over Filter views or by clicking the dropdown arrow next to the Filter icon.

Sort and filter data on your own screen to create a temporary filter view that is accessible only to you. Because you don’t have edit access to the spreadsheet, the filter view won't be saved and won't be available if the spreadsheet is refreshed in the browser.

Only users with edit access to a spreadsheet can create filter views that are accessible to anyone viewing the spreadsheet.

Filters vs. Filter Views

Both filters and filter views serve the purpose of narrowing down the view of a spreadsheet to analyze specific data. However, filter views are more useful in certain circumstances.

While you can only save one filter, you can save multiple filter views to return to and share with others viewing your spreadsheet. You can also name a filter view, but not a filter.

Adding a filter will change the view for anyone viewing the spreadsheet, while filter views need to be explicitly turned on by each person. Each person in a spreadsheet could be viewing a different filter view at the same time.

Filters can’t be duplicated or copied, while filter views can be duplicated to quickly create another view with similar rules. Note that, while you can import and export filters, filter views are unique to Google Sheets and can’t be imported or exported.

When sorting data in filters, the underlying data is sorted so if you turn off the filter, the data stays sorted. Turning off filter views changes the data back to its original order.

Filter views can be used if you only have view access to a spreadsheet, while filters can't be used.

Basic filters are most useful if you want to force everyone viewing your spreadsheet to see a specific filter when they open it, and while viewing and editing. In addition, if you want your data to continue to be sorted after using the filter, you may also want to use filters instead of filter views.

Share this:

Matt is a Docs & Drive expert and author of this help page. Leave him feedback below about the page.