Advanced Filter in Google Sheets

What is Advanced Filter in Google Sheets?

With the Google Sheets Advanced Filter option, users can dynamically filter data. It offers greater customization and versatility than regular filtering. Advanced filters in Google Sheets are tools that allow for greater efficiency, allowing users to navigate complex data sets easily. Let us look at how to use advanced filters for data analysis and explore their usage.

Let us look at an example. Our worksheet contains some students’ marks. We must sum up only the marks they secured in Math. We use the following FILTER formula.

=SUM(FILTER(B2:B9, A2:A9=”Math”)).

Here, you filter all the rows in A2:A9 containing “Math” and display their corresponding marks in B2:B9.

SUM Filter
Key Takeaways
  1. Using an advanced filter in Google Sheets means using more advanced filter options, such as working with dates for complex data manipulation.
  2. Advanced filters overcome the shortcomings in using standard filtering, offering more control for complex data analysis.
  3. When we use basic filters with the filter tools option in Google Sheets, we do simple sorting and filtering based on single criteria.
  4. We use advanced filters with custom formulas or the FILTER function to apply multiple, complex criteria to their data sets.
  5. Always ensure that you follow best practices such as organizing your data and defining correct ranges when using advanced filters in Google Sheets.

How to Create Advanced Filter in Google Sheets

With your dataset in Google Sheets, it’s time to learn how to use advanced filter in Google Sheets step-by-step.

To use an advanced filter in Google Sheets, you follow these steps.

Step 1: Select the range of cells where you need to apply the filter.

How to Create Advanced Filter in Google Sheets

Step 2: Click on Data. Then select Create a filter.

How to Create Advanced Filter in Google Sheets - Step 2

Step 3: Now, you should go to the top of the range. Then click on Filter

How to Create Advanced Filter in Google Sheets - Step 3

Step 4: Click on the option Filter by condition

How to Create Advanced Filter in Google Sheets - Step 4

Step 5: Here, you can choose any of the conditions or write your custom formulas where you can leverage the advantages of advanced filtering to input complex criteria.

You can filter data by specific conditions as well.

Here, we filter out data with the following conditions:

=A2:A8=”Reah”

How to Create Advanced Filter in Google Sheets - Step 5

Step 6: Click OK and check the table.

How to Create Advanced Filter in Google Sheets - Step 6

Examples

In this section, we understand the nuances of setting up and applying advanced filters. Here are some interesting examples that showcase their capability. From data preparation to the final analysis, let us look at how to master advanced filters with these examples.

Example #1 – Filter Based on Another Cell’s Value

In this example, let us apply a filter based on the value in another cell. Below is a table with some data on sales at a store,

Example 1 - Filter Based on Another Cell’s Value

Step 1: Select the range

To filter the whole table, you can click a single cell anywhere in it and proceed to turn the filter on. Here, your entire used range will be selected automatically. You can also select the required columns and apply the filter. Here, we select the whole table.

Go to Data > Create a filter in the menu.

Example 1 - Filter Based on Another Cell’s Value - Step 1

Step 2: Now, each column header will have an upside-down pyramid icon. Pick the column you’d like to filter and click on the filter icon in its header.

Here, we wish to filter based on sales greater than $100 and display their information.

Example 1 - Filter Based on Another Cell’s Value - Step 2

Step 3: You’ll see all conditions available to filter. Here, we will write our own custom formula. Choose “Custom formula.”

Example 1 - Filter Based on Another Cell’s Value - Step 3

Step 4: Enter the following formula where we display data with a particular ID.

=FILTER(B2:B8, B2:B8=”121AD”). Press OK. The table is filtered accordingly.

Example 1 - Filter Based on Another Cell’s Value - Step 4

Example #2 – Use OR logic inside an advanced filter

In Google Sheets, you can create an advanced filter in Google Sheets using the built-in Filter Views with OR conditions. Let us take the previous dataset. Here, we must apply some conditions using OR.

We need to filter the data using the product IDs 121AD and 231RF. For this we use the function REGEXMATCH.

The REGEXMATCH function allows you to check whether a specified regular expression pattern matches a given text. Its syntax is REGEXMATCH(text, regular_expression)

Step 1: Enable the filter as in the example above. Now, click on the filter icon on the column we want; here it is B.

Example 2 - Use OR logic inside an advanced filter - Step 1

Step 2: Pick Filter by condition. Now, go to “Custom formula is.” Enter the following formula in the box.

=OR(REGEXMATCH(B2:B8,”121AD”),REGEXMATCH(b2:B8,”231RF”))

Example 2 - Use OR logic inside an advanced filter - Step 2

Step 3: Press OK. You get the data filtered as specified. Here, Google Sheets filter by multiple conditions can be done using the OR logic in one column. Here, we want to see all rows with the ID 121AD and 231RF.

Example 2 - Use OR logic inside an advanced filter - Step 3

Example #3 – Use nested functions inside the Filter function

The FILTER function goes through the dataset and returns the required data that meets your specified criteria. This function doesn’t alter your original data. It just copies the rows that meet the criteria and puts them where the formula is entered. In this example, let us use nested functions inside the FILTER function to create more complex filtering criteria. We have a dataset of some students and their scores.

Example 3 - Use nested functions inside the Filter function

Step 1: Let us filter this dataset for scores greater than the average score.

Now, to calculate the average, we use the formula “ =AVERAGE(B2:B6).

The overall function we type using FILTER is as follows:

=FILTER(A2:B6, B2:B6 > AVERAGE(B2:B6))

Example 3 - Use nested functions inside the Filter function - Step 1
  • Here, the FILTER(A2:B6, …): This part filters the range specified.
  • (B2:B6 > AVERAGE(B2:B6)): It checks if each score is greater than the average score.

You will find that scores greater than the average are displayed.

Example 3 - Use nested functions inside the Filter function - Step 2

This method of using nested functions helps create powerful advanced filter in Google Sheets based on complex criteria.

Example #4 – Sum filtered cells in Google Sheets

Next, in this example, we have the ages of some football team players. Let us use the FILTER function to find the sum of the filtered players’ ages.

Below is the table.

Example 4 - Sum filtered cells in Google Sheets

Step 1: To apply the FILTER function, we must check the criteria. Let’s filter out players over 27 and find the sum of their ages.

Apply the following function in B13.

=SUM(FILTER(B2:B12, B2:B12>27))

Here, we first filter all rows whose corresponding values in column B are greater than 27.

We then use the SUM function to add them.

Example 4 - Sum filtered cells in Google Sheets - Step 1

Step 2: Press Enter, and you get the SUM of the ages of all players above 27.

Example 4 - Sum filtered cells in Google Sheets - Step 2

Example #5 – Filter by Date Range

To use the Google Sheets FILTER function with date and time, we may need additional functions like MONTH, DAY, etc.

In this example, let us take one of the earlier data sets of Example 1.

Example 5 - Filter by Date Range

Step 1: Here, let us extract data that falls before a certain date. For this, we will need the DATE function. Let us use it with a comparison operator less than.

Apply the following formula for products sold before January 1, 2022.

=FILTER(A2:D8,A2:A8<DATE(2022,1,1))

Example 5 - Filter by Date Range - Step 1

Step 2: Press Enter. You will get the data for all dates where the products were sold before Jan 1st, 2022.

Example 5 - Filter by Date Range - Step 2

Important Things to Note

  1. When applying an advanced filter, you can nest functions like SEARCH, REGEXMATCH, etc., within the FILTER function for multiple complex criteria.
  2. Always ensure that the ranges you use in your filters are of the correct size to avoid errors.
  3. If you recolor your original data set when using Filter by Color, the formula result won’t be updated automatically because color changes don’t trigger formula updates.
  4. When using filter for another Google sheet, you should write the formula with the sheet name every time you reference the other sheet.

Frequently Asked Questions (FAQs)

What are the uses of an advanced filter in Google Sheets?

1. You can use advanced filters for the following:
2. You can use advanced filters to extract unique values from a dataset,
After filtering with advanced filters, You can begin analyzing the results. The results obtained from advanced filters can be further sorted, visualized, and filtered which can help in decision-making.
3. You can utilize the Sort function to organize your filtered data in chronological order.
4. You can transform your filtered data into charts or graphs to understand the trends and patterns.

What are the error messages you get when Google Sheets FILTER function not working.

Some of the error messages obtained include:
#REF! error: It occurs for invalid cell references. Therefore, check that you entered all ranges & all sheet names correctly.
#N/A error: This error occurs when your FILTER formula can’t find any matching results. If there are simply no matches, you can use IFERROR to display an appropriate message.
#VALUE! error: If you see this error, it mostly means that you are using ranges of different sizes.
#NAME error: When filtering Google Sheets using a custom formula, you may sometimes see this error. Check if your function names are right.

What is the difference between regular filters and advanced filtering in Google Sheets?

When you use a regular filter, the Filter tool in Google Sheets allows you to filter data using simple conditions such as filtering by values, filtering by text, colors, etc.).
Advanced Filtering: When it comes to advanced filtering, you use functions like FILTER, QUERY, etc., or complex formulas to create filtering criteria, such as combining multiple conditions. You can use the FILTER function with logical operators for multiple criteria in advanced filtering.

Download Template

This article must be helpful to understand Advanced Filter In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is Advanced Filter In Google Sheets. Here we explain how to create and use it to dynamically filter data with examples. You can learn more from the following articles –

Euro Symbol in Google Sheets

Reference Another Sheet in Google Sheets

PRICE Function in Google Sheets

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *