How to Filter in Google Sheets [Complete Guide]

How to Filter in Google Sheets 4
Key Takeaways: How to Filter in Google Sheets

To Filter in Google Sheets

Creating a Filter:

  1. Select any cell within your dataset.
  2. Go to Data.
  3. Create Filter.
  4. A filter icon will appear on your headers and a border around your data table.

Basic Filtering:

  1. Click on the filter icon on the column you want to filter.
  2. You can sort your data A-Z, Z-A, or sort by conditions like text contains, the date is, greater than, etc.
  3. You can also filter by values by checking or unchecking specific entries.
  4. After applying a filter, the data displayed is a “filter view” leaving your original data untouched.

OR

  1. You can layer filters on the same dataset.
  2. 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.

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.

Step 1

Sample data. I have some employee lists with their name, department, designation, salary, and ages. We will apply the filter to this data.

How to Filter in Google Sheets 1

Step 2

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

How to Filter in Google Sheets 2

Step 3

Go to Data > Create Filter

How to Filter in Google Sheets 3

Step 4

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

How to Filter in Google Sheets 4

Step 5

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.

How to Filter in Google Sheets 5

Step 6

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.

How to Filter in Google Sheets 6

Step 7

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.

How to Filter in Google Sheets 7

How to Filter in Google Sheets 8

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.

How to Filter in Google Sheets 9

How to Filter in Google Sheets 10

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.

How to Filter in Google Sheets 11

Step 8

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.

How to Filter in Google Sheets 12

Step 9

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.

How to Filter in Google Sheets 13

How to Filter in Google Sheets 14

Step 10

Now, in this field, you will provide the condition

How to Filter in Google Sheets 15

How to Filter in Google Sheets 16

How to Filter in Google Sheets 17

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.

Step 11

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.

How to Filter in Google Sheets 18

How to Filter in Google Sheets 19

After you are done with your filter condition, or values click on the Ok button below, and it will filter out your data instantly.

How to Filter in Google Sheets 20

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.

Step 1

I have this sample data and the filter is created already.

How to Filter in Google Sheets 21

Step 2

Now I want to filter people who have Production in their dept.

How to Filter in Google Sheets 22

Step 3

This will show me a temporary view of my data with only those who have sales in their dept column.

How to Filter in Google Sheets 23

Step 4

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.

How to Filter in Google Sheets 24

How to Filter in Google Sheets 25

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.

Case 1

Here I have my data set only so the filter will automatically pick up the right header

How to Filter in Google Sheets 26

Case 2

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.

How to Filter in Google Sheets 27

Case 3

Here I have my data table with no empty row in between, so here, the filter will automatically pick up the wrong header.

How to Filter in Google Sheets 28

For Case 3, you should select the data set and then go for creating a filter.

I hope this rule is clear to everyone.

Useful Notes

  • 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.

Conclusion

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general