How to Use Filter Function in Google Sheets (5 Examples)

how to use Filter Function in Google Sheets 27

Hello, welcome to another article. In this article, we will learn how to use the Filter function in google sheets.

The filter function as the name suggests used to filter the data. But, it’s not this simple. The filter is one of the most powerful functions in google sheets. Its dynamic function means that if the source data is changed, then the result will also be affected. The filter function can be used in hundreds of possible ways using various conditions, and combinations of the conditions. we can use other mathematical functions within the filter function, and also, we can use the conditional statements link AND and OR. Let’s move forward and learn in detail.

Use cases of Filter Function in Google Sheets

We often work with data ranges that are huge and have a lot of combinations of data that can be filtered to analyze the data from many aspects to determine handy insights. The filter function can be the one that is used for filtering the same data set in many various ways using different functions and logic. It gives us actual insights into the data that cannot be analyzed manually. Therefore, we need to learn the filter function. Another reason is that the function is dynamic, which means we can create real-time dashboards using the filter function and drop-downs in google sheets.

See also  How to Compare Two Columns In Google Sheets (4 Methods)

How to use Filter Function in Google Sheets

From this section we will see various use cases to use the Filter Function, first, let’s see the syntax then we will move on to learn how to use the filter function in google sheets.

FILTER(range, condition1, [condition2, ..])

range: The source data range you want to filter using the Filter function.

condition1: A data range row or columns correspondent (this should be an equal number of rows as your source range has). It returns a Boolean value based on the condition.

[condition2]: This is an optional argument, it is the second condition to use when using OR, AND, and other logical conditions. This also should be equivalent to the previous argument (the number of rows should be equal). It also returns a Boolean value based on a given condition.

How to use Filter Function in Google Sheets – Using A Single Condition

In this first section, we will learn how to use the filter function with a single condition. This is going to be the basic usage of this function. For this, we will need some data to work on. So, see the below steps and follow me with the example.

Step 1

A sample dataset

how to use Filter Function in Google Sheets 1

Step 2

In any other cell, write the filter function starting with “=

how to use Filter Function in Google Sheets 2

Step 3

Pass the data range as the first argument.

how to use Filter Function in Google Sheets 3

Step 4

Pass the condition1

how to use Filter Function in Google Sheets 4

Step 5

You can hard-code the value, or you can pass the cell reference of that value

how to use Filter Function in Google Sheets 5

Step 6

Hit Enter key, and you got the result

how to use Filter Function in Google Sheets 6

This is how simply you can filter out anything from the dataset based on a single condition.

See also  How to Merge Cells in Google Sheets (Complete Guide)

How to use Filter Function in Google Sheets – Using Multiple Conditions

In this section, we will learn how to use the filter function in google sheets using multiple conditions. We had a condition in the previous section where we filtered out the data of a person having an age greater than 23, with the same condition we can add another condition such as age is greater than 23 and the profit is greater than 15000. This can be simply done following the below steps.

Step 1

Write the formula same as the previous

how to use Filter Function in Google Sheets 7

Step 2

Add a comma, and write another condition

how to use Filter Function in Google Sheets 8

Step 3

Hit Enter key and you’re done.

how to use Filter Function in Google Sheets 9

Note that whenever you compare a column in a condition inside the Filter function, you select the entire column (excluding headers) to match the number of rows with the sort data, here my source data has nine rows, so in every condition when I compare any column, I will have to select the whole column. If the number of rows is unmatched, you will get an error.

See also  How to Use REGEXMATCH in Google Sheets [Complete Guide]

Now let’s move forward and learn the Filter function with some complex use cases.

How to use Filter Function in Google Sheets – using OR Condition

In this section, we will learn how to use Filter Function in Google sheets with OR condition, we have already seen the AND condition in the second method where we learned to add two conditions. To use OR condition we need will utilize the same example. Let’s say I want to check the Name section and find out any name, and then I will add another condition where I will add another name, because I am not sure about the name, so I will pass two possible names and the correct one will be returned.

Step 1

Filter anything using a simple single condition method.

how to use Filter Function in Google Sheets 10

Step 2

Add a + sign, and research for a similar data point for which you are not sure

how to use Filter Function in Google Sheets 11

Step 3

Hit Enter key, and you will see the correct value will be returned from two defined values.

how to use Filter Function in Google Sheets 12

This is how OR logic works, using the same logic you can make hundreds of use cases to filter the data.

how to use Filter Function in Google Sheets 13

how to use Filter Function in Google Sheets 14

See also  How to Make a Pie Chart in Google Sheets

How to use Filter Function in Google Sheets – With Other Functions

In this section, we will learn how to use the Filter function in google sheets with other functions such as average, max, min, etc.

This is another common use case where we need to have the value based on a mathematical calculation, so the filter function helps us and solve this problem in a single step, we don’t have to find the average separately then pass it inside the Filter function, we can do both steps inside Filter function.

We will use a simple use case to understand how it works. We make nested functions inside the filter function. Let’s see how it’s done.

Step 1

Write the Filter function and pass the entire dataset

how to use Filter Function in Google Sheets 15

Step 2

Pass the column from which you want to filter the data

how to use Filter Function in Google Sheets 16

Step 3

Now I want the values that are greater than the average value, so I don’t need to calculate the average value first instead I can use the following syntax.

how to use Filter Function in Google Sheets 17

B2:B10>AVERAGE(B2:B10))

The above statement says that find filter values from B2:B10 only when the value is greater than the average value of B2:B10.

Step 4

Press Enter, and you have got the result

how to use Filter Function in Google Sheets 18

This is how we can use other functions inside the Filter function to make a shorter syntax and perform some complex tasks.

See also  How to Use Transpose Function In Google Sheets (Easy Guide)

How to use Filter Function in Google Sheets – to Filter Top 3 & Top 5

In this section, we will learn how to use the Filter function in Google Sheets to Filter the top 3 & top 5.

We can bring top (any number), there is no limitation but the number should be less than the number of rows of the source data.

We can find the top 3 or top 5 from above, and also from the bottom.

Step 1

Write the filter formula and pass the entire data range

how to use Filter Function in Google Sheets 19

Step 2

Just like we used the average formula in the previous section, here use the Large function with a condition “greater than equal to” for the upper top 3 or 5.

how to use Filter Function in Google Sheets 20

Step 3

Pass a number 3, or 5, or any number to define how many rows you want to return.

how to use Filter Function in Google Sheets 21

Step 4

Hit Enter key, and you have got the result

how to use Filter Function in Google Sheets 22

Note: We can SORT the filtered data using a sort function

how to use Filter Function in Google Sheets 23

how to use Filter Function in Google Sheets 24

This is a logic in which we compare the large values inside a column and give a number to specify how many rows to return.

To find the same data from the bottom we will use the Small function.

Only Step

Use the Function Small instead of Large, and change the condition to “less than equal to”.

Hit Enter key and you have got the top values from the bottom.

how to use Filter Function in Google Sheets 25

how to use Filter Function in Google Sheets 26

See also  How to Combine First and Last Name in Google Sheets

How to use Filter Function in Google Sheets – Making a Mini Dashboard

Using a dropdown list, a chart, and a filter function, we can make a simple dashboard like the below.

how to use Filter Function in Google Sheets 28

Download/Copy Google Sheets Practice Workbook

Get a free template and make a copy to play around with. Be experimental and explore new features.

Notes

  • The Filter function is dynamic. If the source data is changed, the resultant cell will also change according to the effect of change.
  • The filter function returns an unsorted range
  • The filter function can be used with more functions, try it out and make your logic
  • The filter function can be used with many other functions
  • It returns an Array value, which can show up as an error if there is already something existing in the cells in which the array needs to be spread. It will not overwrite the data, instead, it will show up an error so you can manually remove your already written data that is overlapping the array value result

FAQs

How filter function works dynamically?

Assume you have 4 columns in your data that show the profit of four different months, now in a cell you can create a drop-down having all four values. Now you add a filter function in which you passed the reference of the cell in which your dropdown exists. Now on the change of the value, each time in the drop-down cells the values of the data coming from the Filter function will automatically update every time. This is called the dynamic behavior of the Filter function. As we have done in the above section.

See also  How to Combine and Concat Cells in Google Sheets (6 Methods)

Conclusion

Wrapping up how to use the Filter function in google sheets. We have learned this function in every detail. The filter function is very commonly used along with the query function, both can be used interchangeably in many scenarios. We first saw what is Filter function, then we saw why should we learn this function, and where it’s used. We learn the syntax then we saw how to use the Filter function with a single argument/condition. Then we saw multiple conditions, OR operator inside Filter function, other Functions inside Filter function, and then we saw how to drive top 3, top 5 using Filter Function. This article was designed for beginners and mid-level users. I tried to keep things simple and easy for everyone to understand easily. That’s all from how to use the Filter function in google sheets.

I hope you find this article helpful. Please give it a like and don’t forget to share and Subscribe to the Office Demy blog to get future updates about the upcoming tutorials. Thank you. Keep learning with Office Demy

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general