How To Use Filter Function In Google Sheets (With Examples)

While Google Sheets already has a built-in filter feature, there’s also a special function called the “FILTER” function that you can use.

Think of the FILTER function as a tool that helps you sort data based on certain rules. For instance, imagine you have a list of names, their corresponding states, and their sales values. By using the FILTER function in Google Sheets, you can swiftly extract all the information related to a particular state.

The FILTER function has a special advantage over the regular filter view tool since the result can change automatically when your data changes. This is really helpful when you want your reports or dashboards to update by themselves.

This tutorial will show you how to use the FILTER function in Google Sheets. We’ll also review some easy examples to see how it works in your daily tasks.

To help you better understand how the filter function in Google Sheets works, we have put together a scenario that helps convey the message better.

Scenario/Use Case

Imagine you’re organizing a fundraising event and keeping track of donations from various sources. You’ve created a Google Sheets spreadsheet with columns for donor names, donation amounts, and the donation source (like individuals, companies, or organizations). As your list grows, it becomes challenging to see the overall picture. This is where the FILTER function comes in handy.

Let’s say you want to see the donations specifically from companies. You can use the FILTER function in Google Sheets to instantly generate a new table showing only the rows where the donation source is marked as “Company.” This makes it much easier to assess how much you’ve collected from businesses and plan your appreciation strategies accordingly.

In this way, the FILTER function acts like a magnifying glass for your data, helping you zoom in on specific details so you can make informed decisions.

Understanding Google Sheets Filter Function Syntax

Before we start practicing with Google Sheets today, let’s take a quick look at how the Filter function works. The formula for the Google Sheets filter is as follows:

FILTER(range, condition1, [condition2, …]):

range: This means the group of cells you want to filter.
condition1: This is like a rule for a column or row (like the rows in your data). It gives you a list of “TRUE” and “FALSE” answers. It needs to be the same size as the range.

[condition2]: This part is optional. It’s like a second rule you can use in the formula. It’s also a column or row (like your data’s columns or rows). It needs to be the same size as the range.

When you use more than one rule, only the things that match both rules will show up.

If the FILTER function can’t find anything that matches the rules, it shows an error called “#N/A.”

If you’re unsure how this works, let’s go through a couple of examples (below), and you’ll see how to use the FILTER function in Google Sheets.

Filter Function in Google Sheets- Practical Examples

Now that we’ve understood how the Filter function works in Google Sheets, let’s move on to some real-life examples.

Don’t worry if you’re new to Google Sheets or if you’re already familiar – these examples will walk you through using the filter function step by step.

To start, let’s look at our first example using this sample data.

Now that we’ve got our data ready, let’s take a quick look at how the filter function in Google Sheets actually works.

The main idea here is to understand how to use filters to sort out specific pieces of information.

In this example, we aim to separate out the donations made by companies.

Step 1: Update The Spreadsheet To Include The Headings You Want In The Filtered Section

First, let’s ensure our spreadsheet displays the titles we want for the filtered part. In this example, we’ll add headings for “Donor Name,” “Donation Amount,” and “Source” in cells F1, G1, and H1, respectively. This will help organize the filtered data better.

Step 1: Choose The Cell Where You Want The Result Generated

To work with the filter function in Google Sheets, you need to pick the cell where you want the filtered information to appear. In this guide, we’ll use cell F2 for our example.

Step 2: Type In The Filter formula

After choosing the cell where you want your result generated, simply navigate to the formula bar and type in the following formula:

=Filter(A2:C11,C2:C11=”Company”)

Step 3: Hit Enter

Now that we’ve correctly typed the Filter formula in the formula bar, as explained in the previous step, just press the Enter key on your keyboard. You’ll see something amazing happen – Google Sheets will instantly generate the result in your chosen cell.

Here is what our spreadsheet looks like after applying the Filter formula:

In the picture above, you can see that we’ve managed to sort our original data to show only the donations that were given by companies.

Now, you’ve experienced firsthand how simple it is to use the Filter function in Google Sheets. This function helps you organize your spreadsheet by showing the necessary information based on certain rules.

Understanding The Filter Formula We Used

In this example, the formula we used takes two parts: the range of data and the condition. We looked at the cells from C2 to C11 and checked if each cell’s value was “Company.” If it’s “Company,” that donation shows up in the filtered result; if not, it’s left out.

But here’s something cool: you don’t always have to put “Company” directly into the formula. You can put it in a cell, and the formula can look at that cell instead. For instance, if you type “Company” in cell H1, you can use this formula:

=FILTER(A2:C11, C2:C11=H1)

FILTER function in Google Sheets: A Few Things You Should Know

Now that you’ve got a grasp on the Filter formula we used in the last example, here are some key points to keep in mind about the Filter function:

The FILTER function in Google Sheets creates a bunch of values that fill into nearby cells (this is called a dynamic array). To make this work, it’s important that the cells around (where the results will show up) are empty.

If any of these nearby cells aren’t empty, your formula will show an error called #REF! Google Sheets helps you out by showing a red triangle at the upper-right of the cell. When you hover over it, you’ll see a message like this:

“Array result was not expanded because it would overwrite data in F3.”

As soon as you erase the filled cell, causing the FILTER function to stop, it automatically fills the space with the results.

Also, the outcome of the FILTER formula is like a bunch of information together. You can’t just change or delete one piece of it (or a couple pieces). If you want to get rid of the results, you’ll have to delete the whole bunch of them. To do this, click on the cell where you put the formula and press the delete key.

How To Filter Google Sheets Based on Multiple Conditions (And Condition)

The FILTER function can also be used to look for more than one condition. This way, it only shows records that meet both conditions together.

To demonstrate how to use the filter function in Google Sheets to filter data in your spreadsheet based on multiple conditions, we will use the same sample data we used in our earlier example, albeit with a few changes.

Let’s say we have the above sample data, and we want to filter out the records where the donors are companies, and the donation value is over 300 dollars.

Here is how we will go about it:

Step 1: Choose An Empty Cell Where You Want The Filtered Result Generated

Let’s start off by choosing an empty cell in our spreadsheet where we want our filtered data to be generated. For this example, we will use cell F2.

Step 2: Type The Filter Formula

After choosing an empty cell where you want the filtered result generated, head over to the formula bar and type in the following formula:

=FILTER(A2:C11, B2:B11=”Company”, C2:C11>300)

Step 3: Hit Enter

Having typed in the Filter function formula like we showed you above, simply press the Enter button on your keyboard. Google Sheets should automatically generate the result in the selected cell.

Here is what our spreadsheet looks like after applying the Filter formula:

Looking at the picture above, you can tell that we managed to sort the data to show donations not just from companies but also those exceeding $300.

And that’s how to work with the filter function in Google Sheets when you want to filter data using more than one rule. It wasn’t too complicated, right?

How To Filter Google Sheets Based on Multiple Conditions (Or Condition)

In the example above, we looked for two conditions and got results where both of them were true.

Now, you can also use the FILTER formula to check for an OR condition. We’re going to teach you how to do that in this part.

For instance, think about this: you have data like the one shown below. You want to find all the records for donations made by either Companies or Organizations. This means the condition should be donors who are either Companies or Organizations (which makes it an OR condition).

In this section, we’ll show you how to use the FILTER function to get the rows where the source is either a Company or an Organization.

Here is how to go about it:

Step 1: Select The Cell Where You Want Your Result Generated

First things first, choose the cell where you want the filtered result generated. For this example, we will use cell F2.

Step 2: Type In The Filter Formula

Once you’ve chosen the cell where you want the filtered outcome, go to the formula bar at the top and type in this formula:

=FILTER(A2:C11, (C2:C11=”Company”) + (C2:C11=”Organization”))

This formula helps you find what you’re looking for. It checks the data from cells C2 to C11 and looks for those rows where the source is either “Company” or “Organization.” It’s like telling Google Sheets to give you the rows that match either of these conditions.

Step 3: Hit Enter

Once you’ve typed in the formula above, press the Enter key on your keyboard. Then, something magical will happen right before your eyes. Google Sheets will do its thing and create the desired result in your chosen cell.

Here is what our spreadsheet looks like after executing the steps we showed you above.

If you take a good look at the picture above, you’ll see that we managed to sort our original data to display donations from both companies and organizations. And that’s exactly how you use the filter function in Google Sheets to get the desired results using “OR Condition.”

Final Thoughts

Google Sheets is a powerful tool that makes managing and organizing your data a breeze. In addition to its built-in filter feature, a special function called the “FILTER” function can provide even more control.

In this guide, we provided you with some background on the Filter function. We mentioned that you should see the FILTER function as your data sorting assistant. Imagine having a list of names, along with their states and sales figures. By employing the FILTER function, you can effortlessly extract data related to a specific state.

What sets the FILTER function apart is its dynamic nature. Unlike the standard filter tool, the results from the FILTER function adapt automatically as your data changes. This is especially handy for creating reports or dashboards that update themselves.

In this tutorial, we’ve guided you through the usage of the FILTER function in Google Sheets. We’ve also provided easy-to-follow examples to illustrate how it can streamline your daily tasks.

After reading today’s guide, you should be able to use the Filter function in Google Sheets with ease.

In case you need further clarification, feel free to leave a comment, and we will be happy to get back ASAP.