How to Set a Filter in Google Sheets (An Easy Guide)

When working with large sets of data, you may want to arrange the data in such a way that it matches the criteria that you are looking for. That’s where Filters in Google Sheets come in.

Filters can help you bring out not one but multiple criteria of data and arrange them as you wish. Thus, in this article, we will look at exactly how we can set a filter in Google Sheets and run through some examples that cover its basic functionalities.

Let’s get started.

How to Set a Filter in Google Sheets

Filter Basics

Applying a filter in Google Sheets is as easy as it gets.

Here we have a simple dataset in a worksheet. It contains a lot of data making the use of filters crucial to analyze it.

sample dataset for how to set filter in google sheets

We can apply a filter to this dataset in two simple steps:

Step 1: Select any cell within the dataset. Google Sheets can automatically recognize if a cell belongs to a dataset or not by analyzing the adjacent cells of the active cell.

active cell in a dataset

Step 2: Navigate to the Data tab to find the Create a filter option.

Data > Create a filter

create a filter option from the data tab in google sheets

Clicking on it will instantly apply a filter on the dataset the active cell is in.

how to set a filter from the data tab in google sheets

Alternatively, you can also use the Create a filter button in the Toolbar to apply a filter:

create a filter can also be found in the toolbar

A big disadvantage of this automated method is that when you click on the Create a filter option, the data range is automatically adjusted to all adjacent cells:

the filter data range will automatically spread to all adjacent cells

To avoid this issue, we can always manually select the range of data that we want to be in the filter (headers included of course).

manually select the range of data to set the filter in google sheets

Can you Add Multiple Dataset Filters in Google Sheets?

In the following worksheet we have two datasets:

two datasets in a worksheet

A simple question that you may ask in this case is:

Can you add filters to both datasets in Google Sheets?

No, you cannot apply individual filters to both datasets.

There is still no way to select both datasets to apply filters.

you cannot apply more than one filter in a google sheets worksheet

Even if we select the entire worksheet, Google Sheets will not automatically recognize the presence of different datasets.

selecting the entire worksheet to set a filter in google sheets

The image above is the closest thing we can have to apply filters to both datasets at once. However, as you can see, the first rows of all the selected columns will become a filter.

To apply filters to different datasets, we must apply them individually. Since trying to apply another filter to a different dataset will only prompt you to remove the previous filter:

existing filter must be removed to add a new filter

Add Criteria and Sort Data Using Google Sheets Filters

Now that a filter is set in Google Sheets, let’s have a quick look at how we can work with it.

To open the filter menu, simply click on the filter icon beside the header of the column. The first filter criteria we’ve applied is to show all entries of the Cookie and Fruit Category:

filter by value criteria

This is the Filter by value criteria, where we can select the options that we want to show in the table.

Next, we have the Filter by condition criteria, where we can filter the column value according to a condition set by the user. Here, we have filtered all entries that have a Unit Sold value of less than 100:

filter by condition criteria

Finally, we can also set the arrangement of the data to sort by increasing or decreasing values.

sort options in a filter menu in google sheets

Here, we have set the dataset to show the highest values for the conditions first:

result after filter is set in google sheets

While sorting is not strictly a filter condition, you can still apply it using the Google Sheets filter.

Learn More: Sort By Column in Google Sheets (3 Easy Ways)

Save a Filter Arrangement Without Affecting the Original Filter in Google Sheets

There is a unique feature in Google Sheets that allows its users to save a filter arrangement separately without affecting the original dataset. It is called Filter Views.

Once a filter is applied to a dataset, you can save it by creating a filter view from either the Data tab or the Filter drop-down menu in the Toolbar.

filter views in google sheets from the data tab

You can name and further customize the filter and save it separately.

filter view interface in google sheets

All saved filter views can be accessed from the Filter drop-down menu.

saved filter views are listed in the filer drop-down menu

Advantages of using Filter Views:

  • Each filter view has a separate URL that can be shared with collaborators.
  • You can save and access multiple filter views in the same worksheet.
  • Each collaborator can create their own filter view without affecting the source data.
  • You can make a copy of the filter arrangement to use in a different filter.
  • You can create a temporary filter view without access to the source dataset.

Final Words

That concludes our simple guide on how to set a filter in Google Sheets. The filter is one of the more powerful tools available in the application, even though it comes with some limitations. However, those limitations and other collaborative limitations are easily remedied by using the filter views feature.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo