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 managing Microsoft account registration

Guide to managing Microsoft account registration

When you sign up for a Microsoft subscription with recurring billing, you can change how you pay at any time. You can also cancel or turn off recurring billing for a Microsoft subscription when you no longer need it.

How to use Android Picture-in-Picture mode

How to use Android Picture-in-Picture mode

Android Picture-in-Picture mode will help you shrink the video and watch it in picture-in-picture mode, watching the video in another interface so you can do other things.

How to create favorite album on Windows 11

How to create favorite album on Windows 11

The Favorites Album feature is built into the Windows 11 Photos app, saving your most favorite images.

Instructions to quickly delete large folders on Windows 11

Instructions to quickly delete large folders on Windows 11

When deleting large folders on Windows 11, the deletion speed will be slower. If you feel that this deletion process is too time-consuming, the article below will help you with additional methods to delete large folders on Windows 11.

How to watch Apple Music Replay 2024

How to watch Apple Music Replay 2024

Starting with iOS 18.1, users will be able to watch Apple Music Replay 2024 in the Music app without having to visit the website.

How to cancel iCloud + subscription on iPhone

How to cancel iCloud + subscription on iPhone

After a period of use, if you no longer want to subscribe to iCloud+, you can completely cancel your iCloud+ subscription. Below are instructions for canceling your iCloud+ subscription on iPhone.

Instructions for activating Click to Do Windows 11

Instructions for activating Click to Do Windows 11

Click to Do Windows 11 helps you complete tasks more efficiently by recognizing text and images you can interact with on the screen.

Instructions for using Ambient Music to listen to music according to your mood on iPhone

Instructions for using Ambient Music to listen to music according to your mood on iPhone

Ambient Music will compile playlists according to each state and users can listen to music according to their mood, such as relaxation or sleep playlists.

How to turn off Apple Intelligence on iPhone

How to turn off Apple Intelligence on iPhone

After the initial excitement about using Apple Intelligence died down, many people couldn't get past the frustration and inconsistency of many of its features.

Instructions for converting images to text on Copilot

Instructions for converting images to text on Copilot

Users can also convert photos to text, describe images in text on Copilot. With the photo description content on Copilot, you have more suggestions to use for other purposes.

Instructions for rewriting Notepad text using AI

Instructions for rewriting Notepad text using AI

With AI integration in Notepad on Windows 11, you can use the rewrite tool to change the style and length of your notes, as well as rewrite selected text.

Latest Pokemon Unite Codes and Code Entry Instructions

Latest Pokemon Unite Codes and Code Entry Instructions

If you are looking for the latest codes for the game Pokemon Unite then this article is for you.

How to disable Copilot Alt + Spacebar shortcut on Windows 11

How to disable Copilot Alt + Spacebar shortcut on Windows 11

For many people who do not need to use Copilot on Windows or the voice chat feature, the Alt + Spacebar shortcut can be confusing and should be disabled.

How to Find YouTube Videos on Gemini

How to Find YouTube Videos on Gemini

You can find YouTube videos on Gemini by keywording the video content you need. Immediately, we get YouTube videos from Gemini.

How to create AR Emoji on Samsung phones

How to create AR Emoji on Samsung phones

AR Emoji on Samsung phones will record your facial expressions as cartoon characters, then convert them into emojis for you to use on your phone.