Got a spreadsheet with a lot of data? Wanna hide some of the clutter so you can help make sense of it? Filters are what you're looking for.
To turn on filtering for an entire spreadsheet, just click the filter icon (it looks like a funnel) above the sheet.
Next, open the filter menu at the top of any particular column. From there, you can control which rows of data are hidden, and which ones are shown. (In the example below, only rows with a value greater than 50 in “Units” column will be shown.)
When setting up your filter, you can select a single column, multiple columns, or a custom range within and/or across columns. In any case, a filter menu will be placed at the top of each column within the range.
How do each of the filters work?
Filtering by condition
There are five conditions you can apply to your selected data:
- Equals shows only rows that match the requested value
- Greater than shows only rows that exceed the requested value
- Less than shows only rows that fall below the requested value
- Greater than or equals shows rows that exceed or match the requested value
- Less than or equals shows rows that fall below or match the requested value
Alphabetical filtering: These filters are relatively straightforward for numerical data, but how do they apply to words and letters and other non-numerical things? In those cases:
- “Equals” shows only exact matches for the letters/word you put in the value field
- “Greater Than” and “Less Than” show rows that are alphabetically before or after the letters/word you put in the value field.
Filtering by multiple conditions: There are two value fields in the conditional filtering section of the filter menu. If you need to filter a column based on more than one condition, you can use both of them to accomplish that.
(If you don't need more than one condition, just leave the second field blank.)
Filtering by specific values
In the values section of the filtering menu, you'll see a list of each of the discrete values that occur within your selection, as well as the number of times it occurs. Click the checkmark to include/exclude these rows from your filter.
For particularly long lists of values, you may find it useful to search for the ones you're looking to include/exclude. You can use the gray search field to do exactly that.
- To remove individual filters you've applied to your data, open the filter menu for the column you want to stop filtering, then click “Remove Filter.”
- To remove all your filters, click the filter button (the one that looks like a funnel) above your spreadsheet. It'll change from blue to black, and poof, your filters will disappear.
Note that filtered views can't be saved for later restoration. If you want them back, you'll have to set them up again manually.
Who sees your filters?
When you set up a filter, everyone who's a member of the spreadsheet sees that filtered view. Why? Because collaboration isn't just about adding and editing data—it's also about interpreting it and sharing insights. Shared filter views help achieve that.
That said, if you're just tinkering around with filters for you own edification, you want want to consider turning them off when you're done.
- If you lock edits to a spreadsheet (in the Spreadsheet > Advanced menu) after turning filters on, other members can set up their own personal filters while viewing the spreadsheet. Those filters won't be shared with other members, and they'll be reset whenever the page is refreshed.
- People who arrive at a filtered spreadsheet through a view-only URL will also be able to set up their own filtered view. Again, that view won't be shared with anyone else, and will be reset whenever the page is refreshed.