How to use the FILTER function in Google Sheets

The FILTER function in Google Sheets allows you to filter data and return rows that meet specific criteria. Below is how to use the FILTER function in Google Sheets .

  • Google Sheets - Google Sheets
  • Google Sheets for iOS
  • Google Sheets for Android

Overview of the Filter function formula in Google Sheets

By using this Google Sheets function, you can get multiple matches. Because the data is filtered using the function, the results will be updated automatically.

How to use the FILTER function in Google Sheets

The Google Sheets Filter function formula includes: Range - Range, Condition 1 - Condition 1, and [Condition 2…]. The first two factors are mandatory, while the second and subsequent conditions are optional. Google Sheets does not limit the number of conditions in the Filter function.

=filter(range, condition 1, [condition 2]…)
  • Range : Range to filter data in an array. It can contain any number of rows or columns.
  • Test condition 1 : Condition that determines whether a value is true or false in a column or row.
  • [Condition 2…] : It refers to additional rows or columns containing logical values: TRUE or FALSE according to the criteria in the filter.

Example of using the Filter function in Google Sheets

You can find information about sales and profits of each item in different states in the US when using the Filter function in Google Sheets. When a column containing the state name is condition 1, you can create a drop-down list for it.

Create drop-down lists for test conditions

1. Right-click a cell to open the drop-down menu.

How to use the FILTER function in Google Sheets

2. Scroll down and go to the Data validation section .

How to use the FILTER function in Google Sheets

3. In the data validation section, set the criteria to List from a range.

How to use the FILTER function in Google Sheets

4. Click the squares in the criteria section to select a range or formula.

How to use the FILTER function in Google Sheets

5. In this case, the States column will be the range. You need to select items from cells A2 to A13 here.

How to use the FILTER function in Google Sheets

6. Save the Data Validation settings by clicking Save .

How to use the FILTER function in Google Sheets

This action will create a drop-down menu containing items specific to the selected range.

How to use the FILTER function in Google Sheets

Now that you have created the drop-down menu, start filtering Grocery Items, Sales and Profit by States using the Filter function on Google Sheets.

Implement the Filter function

Here the range is data in 3 columns: Sales, Grocery items and Profit. Now select cells B2 to D13 in the first argument of the Filter function.

How to use the FILTER function in Google Sheets

Suppose you want to add more data to the column selected as a range. Remember the second value in the open range. You can do this by deleting the number of cells, here it is 13.

How to use the FILTER function in Google Sheets

As soon as a new value is added, the Filter function will calculate these values ​​without requiring you to filter them again.

The condition value in this example is the States column. Select the States column and set the condition that that column = with the data in cell G5.

How to use the FILTER function in Google Sheets

In this example, Range Area B2:D has the same height as condition area A2:A.

After pressing Enter to deploy the formula, it will filter the data in the Grocery items, Sales, Profits columns according to the given conditions.

How to use the FILTER function in Google Sheets

Also, note that the Google Sheets Filter function only includes valid values. If the value checks true, its value in the array is considered, otherwise the function ignores it and moves on to the next step.

In this example, when you select Utah from the drop-down list, you'll see the related data in the filter area.

How to use the FILTER function in Google Sheets

Filter data by checking multiple conditions

Now we'll update the Google Sheets Filter function to include values ​​greater than 5000 as the second condition. To filter data that meets both conditions, you need to add a new condition to the filter formula.

1. Go to cell F8 where you added the formula earlier.

How to use the FILTER function in Google Sheets

2. Select the Profit column as the range for the second condition, the new formula is as follows:

=Filter(B2:D,A2:A=G5,D2:D>5000)

How to use the FILTER function in Google Sheets

3. Press Enter to execute the formula.

How to use the FILTER function in Google Sheets

The profit from selling flour in Utah is 1240, which does not meet the second condition, so the Filter function of Google Sheets ignored it. Similarly, you can filter this data by trying more conditions.

Above is how to use the Filter function in Google Sheets . Hope the article is useful to you.

Sign up and earn $1000 a day ⋙

Leave a Comment

Guide to participating in Dynamax & Max battles in Pokemon Go

Guide to participating in Dynamax & Max battles in Pokemon Go

The exciting new Dynamax feature is here in Pokémon GO. Here's what you need to know about how to Dynamax and Max in Pokémon Go.

Instructions for changing Weibo account name

Instructions for changing Weibo account name

Weibo social network also has the same setting options as other social networking applications such as changing Weibo account password or changing account name to a new name you like.

Instructions for creating and using BAT files on Windows

Instructions for creating and using BAT files on Windows

Batch files are a way for computer users to get work done. They can automate everyday tasks, shorten the time it takes to do something, and turn a complex process into something anyone can operate.

Instructions for downloading Minecraft for free on iPhone

Instructions for downloading Minecraft for free on iPhone

With the instructions in the article you can download and install Minecraft for free on iPhone/iPad

Instructions to change the default PDF reader on Windows 10

Instructions to change the default PDF reader on Windows 10

There are many different ways to change the default PDF reader on Windows 10, helping you get the PDF reader on your computer as you like through PDF reading software or even reading PDF files using a browser.

How to convert video to GIF on iPhone

How to convert video to GIF on iPhone

The Shortcuts app on iPhone has a shortcut to convert videos to GIFs with very simple operations.

Step by step guide to creating and interpreting numerology charts by name

Step by step guide to creating and interpreting numerology charts by name

Numerology reveals exactly what's in your name. From destiny to soul path, here's how to calculate your numerology chart based on your name.

How to choose video caller in WhatsApp group

How to choose video caller in WhatsApp group

WhatsApp has been updated with a new feature to select video callers in groups, no need to make a call with all members in the group. Here is a guide to select video callers in WhatsApp groups.

How to use Meta AI chatbot on Messenger

How to use Meta AI chatbot on Messenger

Meta AI has been updated on Messenger for you to experience this chatbot, interact with the chatbot for any issue you care about.

Instructions for inserting stickers into photos on iPhone

Instructions for inserting stickers into photos on iPhone

If you have a personal photo that you want to cover some information or even a certain image, you can insert stickers into the photo on iPhone.

How to delete Google account

How to delete Google account

For some reason, you no longer want to use your current Google account, and want to completely delete it. So how can you permanently delete your Google account?

How to use ChatGPT on WhatsApp

How to use ChatGPT on WhatsApp

You can now chat with the official ChatGPT on WhatsApp without resorting to third-party chatbots.

How to search for files using Copilot on Windows 11

How to search for files using Copilot on Windows 11

On Windows 11 you can now use the Copilot app to find, open, and ask questions about files stored on your computer.

How to remove objects in photos on iOS 18

How to remove objects in photos on iOS 18

iPhone 15 and later after upgrading to iOS 18 are equipped with new Apple Intelligence technology, capable of removing objects in photos via the Clean Up tool.

How to create Snoopy style images using ChatGPT

How to create Snoopy style images using ChatGPT

Not only does ChatGPT feature AI photo generation using your description, you can now create Snoopy style photos very simply.