Spreadsheets are capable of holding a huge amount of information, so it can be a time-consuming—or virtually impossible—task to locate and analyze the data you need. Google Sheets has easy-to-use filters built into the app that you can apply directly to the data. But because Google Sheets is a collaborative cloud-based spreadsheet program, sometimes you need a way to filter data without hampering the experience of other users working on the same spreadsheet. In this article, we’ll show you two different ways to filter data in Google Sheets.
This is a straightforward way to filter data in Google Sheets. Imagine you are a teacher and you have the following spreadsheet: Students’ homework scores.
You want to see only the list of students whose average score is at least 80. To do that, click on the funnel icon on the menu. Then in column F (Average), apply the filter condition as shown below.
Once you click OK, the filtered result is as shown below.
Direct filters like this are great if you want everyone using the spreadsheet to see the same, filtered data.
If you want different users to be able to view different data, you want to share different filters with different people, or you want to save custom filters to use again in the future, try using a filter view.
Google Sheets Filter view
Filter views are a really useful tool to allow different users to filter the data they need from the same spreadsheet, without affecting what others can see. Here’s how to create one:
- Reset the filters applied in the previous step (simply click on the funnel icon again to turn off the filter).
- To add a filter view, click on the drop-down next to the funnel icon on the menu bar, then on Create new filter view.
You can apply filter conditions of your choice (just like with the direct filter) and also name this filter view so that you can use it again in the future. Give this filter view a name. Here we’ll call it “Average >= 80”.
When you apply the filter conditions, it is only your view that changes and it doesn’t affect what other people see in the spreadsheet. Use the settings (gear) icon to edit the properties, duplicate or delete it.
How do I reset a Google Sheets filter view?
Just click on the close (X) button on the top right corner of the black bar, next to the options icon. If you need to re-apply the same filter view at some point in the future, just click on the drop-down next to the funnel icon on the menu bar. There you can see the list of all the previously saved filter views. Choose from the list to immediately view the specific information you need.
Advantages of Google Sheets filter view
Unlike direct filters, you can have as many filter views as required, customized to certain users or individual needs. When you (or your colleague/client) access the spreadsheet, you can select whichever view you need at that particular point in time, without disrupting others working on the same Google Sheets file.
- Users need permission to edit a spreadsheet to create filter views. But any user can view and apply filter views – even if they only have “View Only” access.
- Filter view only works on a computer, not on a phone or tablet.
How to edit Google Sheets without affecting other users
Here you’ve learned two different ways to filter Google Sheets. You can filter your spreadsheet directly, or use filter views to work with a filtered layer of the data and save filters for the future. This is fine if you just want to view the data, but if you edit this filter view, you will edit the data in the whole spreadsheet. Everyone will see the changes.
As Google Sheets is designed for collaboration, any change made by any user is automatically saved and reflected everywhere — preserving a single source of truth. There is no built-in functionality in Google Sheets that allows you to edit filtered data without affecting other users in the same spreadsheet.
Sometimes, however, you might want to edit filtered data in Google Sheets. Instead of messy options like copy-pasting data or saving multiple versions, there’s another option.
Filter data to other spreadsheets
Imagine the following scenario: You manage a sales team processing many different sales leads. Each rep is responsible for a certain number of leads. Your team uses a form to gather all the information they need from the leads. This information then flows into a spreadsheet.
This spreadsheet is huge, full of historic data from all leads that ever came in. To separate the leads per rep, you can export filtered data from that main spreadsheet using Sheetgo. Your colleague Charlotte, for example, will only receive the leads she is responsible for, in her own personal spreadsheet. The same for other team members.
Each sales rep has a separate spreadsheet in which they can make changes, such as updating lead information or changing the lead status from “negotiations” to “won” or “lost” for example.
By creating another Sheetgo connection you can merge data from the different sales rep spreadsheets back into a master spreadsheet. From the merged sales lead information, you, the sales manager, have a clear overview of the status of all leads in a centralized spreadsheet. You can use this data to create a live dashboard to report from.
For more advanced data analysis, you can also filter using formulas, such as Google Sheets FILTER formula, to filter and return the rows in a range that meets specified conditions. For guidance, check out this post.