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.Turning a filter on or off
To enable autofilter, follow these steps:
- Select the range of cells you’d like to apply the filter to.
- 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.Create a filter view
When you create a filter view, it’s like saving a filter that you can use over and over again.
- Open the spreadsheet where you would like to create a filter view.
- Click the Data menu, hover over “Filter views…” and select Create a new filter view. You can also click the down arrow to the right of the filter icon and select Create a new filter view.
- Using the down arrows in the column headers, sort and filter the data to fit your desired view. Your filter view will be saved as you make changes.
- To close your new 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.
- Open the filter view you want to rename.
- Click the current name in the black bar of the filter view.
- Delete the text and type in the new name.
- Press Enter.
- Open the spreadsheet that you would like to filter.
- 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.
You can also save a filter that you create as a filter view to access at a later time.
- Apply a filter for your selected range of cells.
- Click the Data menu, hover over “Filter views…”, and select Save as filter view. Alternately, you can click the dropdown menu next to the filter icon and select Save as filter view.
- Your filter is now saved. The filter view is signified by dark grey highlighting your column and row headers, as well as a line above the column headers with the name and range of your filter.
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.
Note: Filter views are only available in the new Google Sheets. If you're working with a spreadsheet that doesn't have filter views available, either use a basic filter or move the spreadsheet to the new Sheets.
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.