Don’t forget to share this post

Google Sheets is often used to store data and share it with others. For example, say you want to share your Google Sheets, including sales data, with all your sales teams. The spreadsheet contains no confidential or sensitive information, so there’s no need to hide or protect any sections.

However, it’s a large spreadsheet, so users will need to filter and sort to find what they want. This can be disastrous, as any direct filtering or sorting will also affect other users. In other words, you’d be wasting one of the major advantages of Google Sheets as a collaboration tool.

Fortunately, there is a simple way to avoid that mess by using Google Sheets’ Filter View. In this article, you will learn how to filter Google Sheets without affecting other users. You will learn how to create, save, and reuse Filter Views, as well as how to share them with other users.

What are Filter Views in Google Sheets?

Filter Views are Google Sheets' solution to a common problem. The ability to sort and filter data in tables becomes increasingly important the larger the table grows; the more spreadsheets are used collaboratively, the trickier this becomes. The great thing about Filter Views is that they only affect the way the data is seen by the person using it.

You can create as many Filter Views as you want and save them with whatever name you choose. Once saved, you can edit or delete these views at any time, as well as share them with other users easily and quickly. This allows you to build up a useful collection of views that can be customized by role, department, or any criteria you want. 

What is the Difference Between Filter and Filter View in Google Sheets?

Both sorting and filtering are actions that modify the way the data is seen by all users, so it can lead to confusion, wasted time, and mistakes if used in a shared spreadsheet. Unlike filters, Filter Views only affect the way you see the data. This means you can sort and filter the data any way you want without having to worry about ruining anyone else’s day.

However, keep in mind that any edits will be saved and applied to the spreadsheet, affecting everyone. If you have editing permission on the spreadsheet and you modify, add, or remove any content, the changes are real even when you are in a Filter View.

How to Create a Filter View in Google Sheets?

The example used below is a spreadsheet containing all sales for a company with four sales teams. This means it’s frequently necessary to filter the data by sales team. I will create different Filter Views for each team and sort the data alphabetically by sales agent.

Create the View

Follow the steps below to create a Filter View in Google Sheets.

  1. 1. Open the spreadsheet in Google Sheets.
How to Use Google Sheets Filter View Without Affecting Other Users Open Sheets
How to Use Google Sheets' Filter View Without Affecting Other Users - Open Sheets
  1. 2. Go to Data > Filter Views > Create new filter view.
How to Use Google Sheets Filter View Without Affecting Other Users Create New Filter View
How to Use Google Sheets' Filter View Without Affecting Other Users - Create New Filter View
  1. 3. You’ll be in Filter View mode as soon as you’ve clicked.
How to Use Google Sheets Filter View Without Affecting Other Users Filter View
How to Use Google Sheets' Filter View Without Affecting Other Users - Filter View
  1. 4. You can rename the view directly on the Filter View bar, as shown below.
How to Use Google Sheets Filter View Without Affecting Other Users Rename View
How to Use Google Sheets' Filter View Without Affecting Other Users - Rename View

Configure the View

You can now configure the view by filtering, sorting, and hiding columns.

Filter Columns

Click on the funnel icon beside the column name. In this case, I want to filter the “sales_team” column to only include the sales by Southteam, as shown below. Click “OK” to apply.

How to Use Google Sheets Filter View Without Affecting Other Users Filter Within View
How to Use Google Sheets' Filter View Without Affecting Other Users - Filter Within View

The view is now filtered to sales made by sales agents in the Southteam.

How to Use Google Sheets Filter View Without Affecting Other Users Filtered
How to Use Google Sheets' Filter View Without Affecting Other Users - Filtered

Sort Columns

You can also sort the columns by clicking on the funnel icon next to the column name. In this case, I want to sort the “sales_agent” column in alphabetical order, from A to Z, so I clicked on the first option.

How to Use Google Sheets Filter View Without Affecting Other Users Sort Column
How to Use Google Sheets' Filter View Without Affecting Other Users - Sort Column

By now, the view contains only the sales by the Southteam, sorted by sales agent, in alphabetical order.

How to Use Google Sheets Filter View Without Affecting Other Users Filtered Sorted
How to Use Google Sheets' Filter View Without Affecting Other Users - Filtered & Sorted

Hide Columns

Since this view only contains data for Southteam, I don’t really need the “sales_team” column, so I’ll hide it. Simply select the column you want to hide and right-click to access the options. Choose “Hide column”.

How to Use Google Sheets Filter View Without Affecting Other Users Hide Column
How to Use Google Sheets' Filter View Without Affecting Other Users - Hide Column

Linking to a Filter View in Google Sheets is as easy as opening that view and copying the URL from your browser.

How to Use Google Sheets Filter View Without Affecting Other Users Link to Filter View
How to Use Google Sheets' Filter View Without Affecting Other Users - Link to Filter View

You can share the link directly with your colleagues so they can access that view. However, keep in mind that they may be able to do more than just view based on their existing permissions. In other words, if the user has editing permissions within the spreadsheet, these permissions will continue even while in Filter View mode.

If you share the link with users that only have viewing permissions in the spreadsheet, they will not be able to do any editing.

How to Use Google Sheets Filter View Without Affecting Other Users View Only Access
How to Use Google Sheets' Filter View Without Affecting Other Users - View Only Access

However, they will still be able to sort, filter, copy, and print data within the Filter View. If you click on the funnel icon, to the left of the green button that says “View only”, you will see various options.

How to Use Google Sheets Filter View Without Affecting Other Users View Only Options
How to Use Google Sheets' Filter View Without Affecting Other Users - View Only Options

You can create a “new temporary filter view”, which will be available only to you. “Delete all filter views” will only delete them for that particular user, not from the main spreadsheet. Click on “Filter view options” to see actions specific to the Filter View in use. In this case, only the “duplicate” option is available. If you grant the user editing permission, they will also be able to update the range, as well as rename or delete the Filter View.

How to Use Google Sheets Filter View Without Affecting Other Users Filter View Options
How to Use Google Sheets' Filter View Without Affecting Other Users - Filter View Options

How to share parts of your Google Sheets with Layer?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

As you have seen, Google Sheets’ Filter Views are flexible and easy to use. You can save as many Filter Views as you need, and sharing them is as simple as copying and pasting a URL. You also know that Filter Views cannot be used to control access and permissions within the Sheet. If you have sensitive or confidential information in your Sheets, Filter Views cannot help you with this.

By now, you know how to create a Filter View, how to customize and save it, as well as how to share it quickly with other users. To learn more about collaboration and permissions in Google Sheets, check out these related articles.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Aug 11 2022, Updated Sep 23 2022