- The fastest way to Filter in Google Sheets: Creating a Filter: Select any cell within your dataset > Go to Data > Create Filter > A filter icon will appear on your headers and a border around your data table.
- The fastest way to Filter in Google Sheets: Basic Filtering: Click on the filter icon on the column you want to filter > You can sort your data A-Z, Z-A, or sort by conditions like text contains, the date is, greater than, etc > You can also filter by values by checking or unchecking specific entries > After applying a filter, the data displayed is a “filter view” leaving your original data untouched.
- The fastest way to Filter in Google Sheets: You can layer filters on the same dataset > For example, filter by the “Department” column for “Marketing,” and then filter the “Age” column for “22” to see employees who match both criteria.
In this article, we will learn how to Filter in Google Sheets. The filter is a very common function. But Google Sheets Filter has two types, the first filter is the Filter function, which we have already covered in this Google Sheets series. Another filter is the filter feature which is not a function. So, today we are going to learn how to use Filter in Google Sheets.
Filters help us to filter our data by different parameters and then display only the filtered data. You may have a large data set and it has so many people. But, for any reason, you need to see only people who are absent today, so here you can use a filter and from a large list you can only filter absent people and then apply it to show only those who are absent. In this way, the data is not compromised nor copied, we have only the original data but has different views, it can be changed from its filter view position to the original one. This is the biggest benefit of using the filter in Google Sheets.
Table of Contents
Importance of Filtering data in Google Sheets
We have learned how to use the Filter function in Google Sheets, and we have seen how easily we can filter out data and keep only useful data for a view. Similarly, the filter is a visual element that helps us filter data by various parameters, we can perform different kinds of filtering with our data. and can see different views.
When we have large organization data, and now we are asked to show some specific data, the best method we will use is Filter. Because filters allow you to create a temporary view of your filtered data without copying it or without compromising on it. So, therefore, we need to learn how to Filter in Google Sheets.
How to Filter in Google Sheets?
We have only two simple locations from where we can Filter in Google Sheets. We will see both methods, but most importantly we will see how to use all the parameters for filtering, and we will apply different conditions, and values to see how the filter element respond to it and make a filter view copy of our data. Let’s start with a basic filter and then we will move to advanced filters.
How to Filter in Google Sheets – Create a Filter
In this section, we will learn how to Filter in Google Sheets, and we will see how to create a simple filter from scratch on a sample employee’s data. We will then apply several conditions and values to it to see different filter views. Let’s get started.
Sample data. I have some employee lists with their name, department, designation, salary, and ages. We will apply the filter to this data.
Firstly, make sure you don’t have any other data on your sheet (such as a header)
Now, select any cell within the data table
Go to Data > Create Filter
You will see a green filter icon will instantly appear on the headers of your data table, and an outer border will also appear on your entire data table
Now, you need to know which column you want to filter, let’s say you want to Filter the department, then click on the filter icon on the department column.
Now you have a context menu opened, there are so many options.
If you focus, you have mainly two functions here, one is Sort, and the other is Filter.
The first three options are for sorting
Sort A – Z will sort this column A to Z and will adjust other relative values according to the new order.
Sort Z – A will sort this column Z to A, and also change the order as per the new sequence of the values in this column.
Sort by Color will sort your data based on colors in your data, since we don’t have any color so both the Fill color and Text color options are disabled.
Now we have Filtered by color, but there are two main options disabled (because we don’t have any color), and none is enabled which has no effect, and it’s selected by default because we don’t have any colors.
Now, we have a filter by condition drop-down. It’s very important.
Here we can Filter the selected column by a condition.
Below we can choose from so many given conditions such as
- None – It means no condition
- Is Empty – It means the cell(s) should be empty to validate this condition
- Is not empty – Cell(s) must not be empty
- Text contains – You will provide a String, and that string must contain the evaluated cells
- Text does not contain – Provided string must not be present in the evaluated cells
- Text starts with – You will provide a starting point, and the targeted cells must start with it too.
- Text ends with – The provided ending point must match the ending point of the evaluated cells.
- Text is exactly – You will provide a text that is exactly equal to the evaluated cells.
- Date is – You will provide an exact date to match
- Date is before – You will provide a date, and the condition will only look for the before data
- Date is after – It will look only for after data that you have provided.
- Greater than – You will provide a number, and it should be greater than to be considered by the filter condition
- Greater than or equal to – You will provide a number and must be greater than or equal to
- Less than – The provided number must be less than the evaluated cells.
- Less than or equal to – Here the provided number can be less than or at least equal to
- Is equal to – Here the provided number must be equal to
- Is not equal to – Must not be equal to the evaluated cells
- Is between – Must be in between your provided value
- Is not between – Must not be in between.
- Custom formula is – This is the most exciting condition, here you can add a custom formula to make a custom condition for filters.
Now, in this field, you will provide the condition
Tip: In step 9 we select a condition, and in step 10 we define that condition, or you can say we assign a value to that condition.
Now, we have the last option, which is to Filter by values. Here we don’t need to specify any condition, we can directly use our original data values and can make a filter.
Here we can check or uncheck values.
For instance. If you want to see the only entries that have Marketing in their department column, then you will check Marketing, and uncheck the rest.
After you are done with your filter condition, or values click on the Ok button below, and it will filter out your data instantly.
If you see less data, it’s only a “filter view”, your original data is not removed
You can press Ctrl + Z to undo and see your original data again, or you can remove the filter.
So, this is how to Filter in Google Sheets. I hope you find this tutorial helpful.
How to Filter in Google Sheets – Use Multiple Filters
In this section, we will learn how to filter in Google Sheets, and how to use different filters for different data columns. We can do it if the data allows, from Google Sheets and filter element this is allowed, but you must have that kind of data to validate multiple conditions. So, let’s apply the filter to more than one column.
I have this sample data and the filter is created already.
Now I want to filter people who have Production in their dept.
This will show me a temporary view of my data with only those who have sales in their dept column.
Now, I want to do further filtering on this view, I will add another filter on the Age column, and I will select 22 as the age, now the filter will check only on the current filter view and will bring people who have 22 in their Age.
You can see the order of the filters, 2 people are of age 22, but when I applied the second filter there is only 1 visible because the first one could not satisfy the first condition. I hope this is clear to all of you.
How to Filter in Google Sheets – The Header Rule
This is not a method but a thing to know when you are using filters in Google Sheets. When we want to add a filter to a data table on our sheet, we don’t need to select it, we can select any cell that exists within the data table and create a filter, and it will automatically pick the right header. But if you have a table and above you have some other data, then you must have an empty row between your data and other content, so the filter will automatically pick the right header even if you don’t select your data set, but if there is no empty row between then it will pick the wrong header.
Here I have my data set only so the filter will automatically pick up the right header
Here I have my data table but also some heading content above it, but I have an empty row between both of these, so here the filter will also automatically pick up the right header.
Here I have my data table with no empty row in between, so here, the filter will automatically pick up the wrong header.
For Case 3, you should select the data set and then go for creating a filter.
I hope this rule is clear to everyone.
- If you have multiple filters in your Sheets then it’s better to name your Filters
- You can save Filter views, and then you can quickly see them from Data > Filter Views
- You can Remove Filter from Data > Remove Filter
- Even after you have deleted the Filter, the Filter views will remain available and viewable.
Frequently Asked Questions
Can I Use Custom Sort in Google Sheets to Filter Data?
Yes, you can use custom sort in Google Sheets to filter data. Sorting data in google sheets allows you to organize your information effortlessly. With the custom sort option, you can specify your desired criteria for sorting, such as alphabetically, numerically, or by a specific column. This feature simplifies the process of arranging and analyzing data within your Sheets.
Today we learned how to Filter in Google Sheets. We saw how to create a filter, and how to use them very easily, I discussed every small feature of it to make you understand it fully. I hope you like this tutorial and that you have learned something new about the filter in Google Sheets. Keep learning with Office Demy.