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
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.
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.
Step 2: Navigate to the Data tab to find the Create a filter option.
Data > Create a filter
Clicking on it will instantly apply a filter on the dataset the active cell is in.
Alternatively, you can also use the Create a filter button in the Toolbar to apply a filter:
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:
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).
Can you Add Multiple Dataset Filters in Google Sheets?
In the following worksheet we have two datasets:
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.
Even if we select the entire worksheet, Google Sheets will not automatically recognize the presence of different datasets.
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:
- Use Data Validation and Filter in Google Sheets (4 Ways)
- How to Filter with QUERY for Multiple Criteria in Google Sheets (An Easy Guide)
- Google Sheets: The FILTER Function (A Comprehensive Guide)
- Google Sheets: Filter for Multiple Criteria with Formula (2 Easy Ways)
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:
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:
Finally, we can also set the arrangement of the data to sort by increasing or decreasing values.
Here, we have set the dataset to show the highest values for the conditions first:
While sorting is not strictly a filter condition, you can still apply it using the Google Sheets filter.
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.
You can name and further customize the filter and save it separately.
All saved filter views can be accessed from the Filter 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.
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.
- Find and Replace Blank Cells in Google Sheets
- How to Use Wildcard in Google Sheets (3 Practical Examples)
- Filter with the AND Condition in Google Sheets (An Easy Guide)
- How to Filter with the OR Condition in Google Sheets (3 Easy Ways)
- Filter for Multiple Conditions in Google Sheets (2 Easy Ways)
- Filter Entries if it Does Not Contain Value in Google Sheets (2 Easy Ways)
- Applying Filter with REGEXMATCH Function in Google Sheets (Easy Examples)
- How to Delete Filter Views in Google Sheets (An Easy Guide)