The Ultimate Guide to Using the FILTER Function in Google Sheets

If you think you've mastered Google Sheets filtering with just the standard tools, get ready for a big surprise. Unlike the built-in tool, the FILTER formula allows you to create dynamic filters with specific criteria that may change over time; apply complex conditions; combine with other functions, and more.

Ready to see the full potential of your spreadsheets? Read this article to learn about use cases, best practices, and common challenges of working with the FILTER function in Google Sheets.

The FILTER function in Google Sheets is a formula that dynamically selects and displays data based on defined criteria, and offers more advanced filtering capabilities than the standard filter tool.

Unlike the manual filter tool, the FILTER function in Google Sheets automatically updates as data changes and can handle complex conditions, like filtering based on multiple criteria or mathematical operations.

You can even combine it with other formulas. For example, FILTER and VLOOKUP can work perfectly together, if you want to find specific information in your data. You can use FILTER to narrow down what you're looking at, like only seeing purchases from last month. Then, VLOOKUP helps you find exactly what you need within that filtered data.

Here's the syntax for the FILTER formula in Google Sheets:

Where:

• range: This is the range of cells or data you want to filter. It can be a single column, multiple columns, or a range of cells containing your data.
• condition1, [condition2, …]: These are the conditions or criteria you want to apply to filter the data. Each condition is a logical expression that evaluates to either TRUE or FALSE. The filtered output will include only rows with all conditions TRUE.

To apply the FILTER function in Google Sheets, choose the range of data you want to filter. Then, after the comma within the function, add either the column or row you're targeting, or the condition.

Here are all the steps for applying the FILTER function in Google Sheets:

Enter the FILTER function in a cell where you want the filtered results:

💡If you need to look up specific values or perform one-to-one matching between datasets, you might prefer to use VLOOKUP or XLOOKUP. Explore our comprehensive guide for detailed instructions on using these functions, along with practical use cases and templates to get you started.

Specify the range of data you want to filter (B3:E17):

Define the filter conditions. For example, to filter for orders made by John Doe:

Press Enter to apply the filter:

As you can see, the FILTER function in Google Sheets enables you to filter through a data range based on specified criteria, creating a new dataset that displays only the entries from the original dataset that meet the conditions outlined in the formula.

Now that you know how to filter using another function for the condition, it’s time to learn how to filter with multiple conditions.

We'll break down the customer data example to explore different ways of filtering. From filtering by text, numbers, and dates to using multiple conditions across columns, we'll analyze various scenarios:

To filter data by text in Google Sheets, we can use the FILTER function along with a single condition we want to apply. Let's say that we need to extract all rows from our customer data based on a single condition, where the product purchased is "Financial Software".

The formula will be:

It scans through the range B3:E17 and filters out only the rows where the value in column C (Product Purchased) matches "Financial Software", displaying relevant rows.

To filter rows where the product purchased is "Financial Software" and the order amount is greater than $5000, we have to use the FILTER function with multiple conditions and the AND logical operator.

The formula will be:

This formula selects rows from the range B3:E17 where both conditions are met: the product is "Financial Software" and the order amount exceeds $5000. It narrows the dataset to display only the relevant rows meeting both criteria.

💡If you're working with complicated data and want to use one formula for many cells or ranges, making calculations easier and changing how data is managed, try ARRAYFORMULA. Check out our article for more tips on using this tool.

Let's consider filtering rows, where the product purchased is either "Financial Software" or "Marketing Analytics".

Using the FILTER function with multiple conditions and the OR logical operator, the formula would be:

This formula selects rows from the range B3:E17 where the product is "Financial Software" or "Marketing Analytics". It retrieves the dataset to display rows where at least one of the specified criteria is true.

To filter data by numerical value in Google Sheets, we can use the FILTER function along with the condition we want to apply.

To filter the data to show purchases with an order amount greater than $6000, use the formula:

This formula filters rows from the range B3:E17 where the order amounts exceed $6000. By setting E3:E17 > 6000 as the condition, we specifically target and retrieve entries that represent higher-value purchases.

This method effectively isolates significant transactions, allowing for a focused analysis of major spending within the dataset.

Suppose we want to filter the data to display purchases made after January 17, 2023:

Here is the formula to apply:

This formula is designed to sift through the data range B3:E17, applying the criterion D3:D17 > DATE(2023,1,17) to only include purchases made after January 17, 2023.

The resulting filtered dataset provides a clear view of customer purchases that occurred beyond this date, offering insights into current buying trends and enabling timely business analysis and decision-making.

In this example, we want to use the FILTER function combined with the TIMEVALUE function to sift through our dataset and display only those records where the purchase time is later than 10:00 AM. To do that, let's extend our table range by adding column E for the time of purchase.

Here is what the formula looks like:

Here is the breakdown:

• B3:E17: this specifies the data range that the FILTER function will consider.
• TIMEVALUE(E3:E17) > TIME(10,0,0): this is used to set the condition and convert the string times in column E into time values that Google Sheets can recognize and compare. We then compare these time values to 10:00 AM (represented by TIME(10,0,0)).

The formula returns rows from B3:E17 where the condition is true, i.e., the purchase was made after 10:00 AM.

While the FILTER function is powerful on its own for extracting specific data based on set criteria, its capabilities can be significantly enhanced when combined with other functions. After exploring how FILTER can be applied directly to sift data, we will now dive into how to pair FILTER with functions like SORT, UNIQUE, SUM, and LARGE to uncover even more potential.

These combinations allow for more complex data manipulations such as organizing filtered results, removing duplicates, summing up subsets, and identifying top records, providing solutions for diverse data analysis needs.

Let's say you've filtered your customer data to show only purchases made by John Doe. You can further refine this by sorting the filtered data based on the order amount, to see the largest transactions first. Using the SORT function together with FILTER makes it possible.

To show purchases made by John Doe and arrange them based on the order amount in descending order, we can use the FILTER function combined with the SORT function.

The formula is:

Here is the breakdown:

• FILTER(B3:E17, B3:B17="John Doe"): This filters the data to include only rows where the customer name is "John Doe".
• SORT(..., 4, FALSE): This sorts the filtered data based on the order amount in column E (which is the 4th column in the range), in descending order (FALSE).

To remove duplicates from data in Google Sheets, we can combine the FILTER and UNIQUE functions.

In our example, let's remove duplicate customer names:

This formula filters the data in column B (customer names) using the FILTER function to exclude empty cells. Then, it applies the UNIQUE function to this filtered list, removing any duplicate customer names.

To sum up filtered results in Google Sheets, you can combine the FILTER function with the SUM function.

Let's say we want to sum up the order amounts for purchases made by the customer "John Doe":

This formula first filters the order amounts (column E) based on the condition that the customer name (column B) is "John Doe". Then, it uses the SUM function to add up these filtered order amounts, giving the total sum of purchases made by the customer.

To retrieve the largest value from filtered results in Google Sheets, you can nest the FILTER function within the LARGE function. This method is particularly useful when you want to reverse earlier explorations where LARGE was nested within the FILTER, offering a different approach to manipulating and analyzing your data.

If we want to find the largest order amount for purchases made by "John Doe", we will use the formula:

This formula first filters the order amounts (column E) based on the condition that the customer name (column B) is "John Doe". Then, it uses the LARGE function to identify and retrieve the largest value from the filtered order amounts, ensuring you have the most significant single transaction at a glance.

You can also use the FILTER function in combination with LARGE to quickly find the top number of records. This method is distinct from the previous example, where the FILTER was nested within the LARGE, offering a different approach to manipulating and analyzing your data.

For instance, to show the top 3 records based on order amount, we need to use the FILTER function with the nested LARGE function to get the desired result.

Here is the formula for the top 3 records:

This formula filters the data in the range B3:E17 to include only the rows where the order amount in column E is greater than or equal to the third-largest value in column E (i.e., the top 3 records based on order amount).

Now that you have many examples of the FILTER function, you can start using them right away. However, if you mix and match filters in more complicated ways, you might run into some problems and errors. To avoid that, follow these best practices:

Before using the FILTER function, make sure your data is nicely organized. After importing data, keep it in a clean, table-like format without any merged cells, as they can mess up your filtering.

Also, be consistent with how you enter your data. If you're using the date format "DD/MM/YYYY" for one entry, make sure to maintain the same format throughout your dataset. Avoid mixing formats like "MM/DD/YYYY" or "YYYY/MM/DD," as this inconsistency can lead to unexpected results when filtering.

Instead of referencing cell ranges directly in your formulas, assign them names that reflect their purpose. For example, name a range containing sales data "Sales_Data" or a range containing customer names "Customer_Names". This makes your formulas more understandable and reduces errors when updating or sharing your spreadsheet. Additionally, named ranges give context and make it easier for collaborators to understand your calculations.

Understanding the FILTER function is just the first step. Step 2 is all about teaming it up with other functions. If you want to calculate the total revenue from a specific region or find the average temperature during a particular period, you can pair FILTER with SUM, AVERAGE, COUNT, or any other function. So, don't stop at just mastering FILTER; take it to the next level by exploring its potential when combined with other functions.

If you're filtering a column of names and nearby cells have numbers or labels, they might mistakenly get included in your filtered results. So, before you start filtering, make sure nearby cells are empty. This stops unrelated information from getting into your results and keeps your data accurate.

By adding logical operators such as AND, OR, and NOT with the FILTER function, you can create complex filtering conditions. You could filter sales data to display transactions above a certain point made by a specific customer within a defined timeframe.

With more practice, you can customize filters to fit all sorts of needs and make your data analysis in Google Sheets more thorough and accurate.

💡It is also possible to solve complex tasks using a combination of VLOOKUP and IF functions. Read our full guide on how to use VLOOKUP with IF statement in sheets and get a free template with this useful combination of formulas.

Even experienced Google Sheets users often see errors in their formula results. Let's explore some common mistakes and how to fix them most quickly:

⚠️ Error: Very often, the sizes of the ranges you're using don't match up. This causes errors because the FILTER needs the conditions to match each row or column in the main range.

Let's say we want to filter out the purchases made by John Doe and Emily Davis, but we mistakenly apply a condition range that doesn't match the main range size.

Here's a wrong formula that would lead to such an error:

In this formula, we're trying to filter the data based on the names "John Doe" and "Emily Davis" in column A instead of column B, which causes a mismatch with the main range size (B3:C17). This discrepancy would result in an error because the FILTER requires all condition ranges to match each row or column in the main range.

✅ Solution: To fix this, make sure all the ranges you're using in the FILTER function are the same size as the main range. This way, each condition lines up with the right row or column, and the function works without any problems.

⚠️ Error: Another problem you might face is called the #REF! error, which happens when a FILTER tries to put its results into a space that already has other data. The FILTER needs some empty cells nearby to work properly because it changes the results dynamically.

In our example, there is already data present in the cells where we expect the filtered results to appear (in column I), that's why the FILTER function results in the #REF! error.

✅ Solution: Before you use the FILTER, check if there's any data in the area where you want the results to show up. If there is, clear those cells. This makes sure the FILTER has enough room to show the results without any issues.

⚠️ Error: Sometimes, the FILTER can't find any data that matches the conditions you've set. When this happens, it gives you an error message, but that might not be very helpful if you're trying to show that there's no data.

Here's an example of a formula that could result in an error when no data matches the specified conditions:

In this formula, we're trying to filter the data to show only rows where the customer name is "Angela Davis", which doesn't exist in our data.

✅ Solution: To handle this situation and make the error message more helpful and clear, we can wrap the FILTER function in an IFERROR statement:

With this modified formula, if the FILTER doesn't find any matching data, the IFERROR function will display the custom message "No matching data found" instead of the error message, making it clearer to users that no data met the specified conditions.

⚠️ Error: When using FILTER, you can't change or delete individual cells or rows in the results it gives you. This is because FILTER treats the results as one connected item and not separate parts.

Let's consider the following formula that uses the FILTER function to extract data:

In this formula, we're filtering the data to show only rows where the order amount (in column D) is greater than 5000. However, since FILTER treats the results as one connected item, we cannot directly change or delete individual cells or rows within the filtered results.

✅ Solution: To work around this limitation and edit the filtered data, we have a couple of options:

1. Adjust the conditions in the FILTER function to get different results. For example, we can modify the condition to filter orders above a different point.
2. Remove the FILTER function and then manually edit the data. After removing the function, the data becomes editable as regular data, allowing us to make changes as needed.
3. If we only need to edit specific parts of the filtered results, we can copy and paste the filtered results somewhere else as regular data. This way, we can edit the copied data without affecting the original filtered results.

To enhance your Google Sheets skills, delve into advanced functions like MATCH, QUERY, and Pivot Table to achieve mastery.

Pivot Table: This adaptable tool simplifies summarizing, organizing, and examining extensive data sets, allowing easy extraction of insights and identification of trends.

CONCATENATE Function: This function combines multiple text items into a single continuous string, providing a seamless way to merge text from different cells.

QUERY Function: This powerful function allows you to perform complex data analysis within Google Sheets by using SQL-like queries. It enables filtering, sorting, and manipulating data efficiently, making it ideal for in-depth data exploration.

MATCH Function: This function searches for a specified item in a range and returns its relative position. It's particularly useful for finding the location of a value within a list, enhancing data lookup and organization.

SEARCH function: This function in Google Sheets is used to find the position of a substring within a string, case-insensitively.

With OWOX BI's integration with Google Sheets and BigQuery, you can effortlessly generate reports and visualizations within your spreadsheet. The free OWOX BI BigQuery Reports Extension simplifies the process, allowing you to manage queries and import results quickly and without errors. Say goodbye to manual data handling and hello to a smoother, more efficient workflow with OWOX BI.