# Google Sheets FILTER function: formulas and tools to filter data in spreadsheets

If the only way you know of creating a filter in Google Sheets is the standard tool, I have a surprise for you. :) Come explore the FILTER function with me. There are plenty of ready-made formulas you can borrow, along with a new powerful tool that complements the filtering toolset immensely.

Some time ago we explained how to filter in Google Sheets using the standard tool. We mentioned how to filter by value and by condition. However, spreadsheets always have more in them than we know. And this time I'm going to explore the Google Sheets FILTER function with you.

You won't find it in Excel, so it is definitely worth checking out.

## Syntax of the Google Sheets FILTER function

Unlike the standard Google Sheets filter, the function doesn't do anything with your original data. It copies the found rows and puts them wherever you build the formula.

The syntax is pretty easy since each argument speaks for itself:

=FILTER(range, condition1, [condition2, ...])
• range is the data you want to filter. Required.
• condition1 is a column or row along with the TRUE/FALSE criteria it should fall under. Required.
• condition2,..., etc., stand for other columns/rows and/or other criteria. Optional.
Note. Each condition should be of the same size as the range.
Note. If you use multiple conditions, they should all be either for columns or rows. Google Sheets FILTER function doesn't allow mixed conditions.

Now, with these notes in mind, let's see how the arguments take shape of different formulas.

## How to use FILTER function in Google Sheets

I'm going to show you all the examples while filtering a small table where I track some orders:

The table contains 20 rows with various types of data which is perfect to learn the function.

### How to filter in Google Sheets by text

#### Example 1. Text is exactly

First, I will ask the function to show only those orders that are running late. I enter the range to filter — A1:E20 — and then set the condition — column E should equal Late:

`=FILTER(A1:E20,E1:E20="Late")`

#### Example 2. Text is exactly not

I can ask the function to get me all orders but those that are late. For that, I will need a special comparison operator (<>) that means not equal to:

`=FILTER(A1:E20,E1:E20<>"Late")`

#### Example 3. Text contains

Now I'd like to show you how to build the Google Sheets FILTER function based on the partial match. Or in other words — if text contains.

Did you notice that order IDs in column A contain country abbreviations at their end? Let's create a formula to retrieve only orders that were shipped from Canada (CA).

Normally, you'd use wildcard characters for this task. But when it comes to the FILTER formula, it is FIND and SEARCH functions that operate this way.

Tip. If you'd rather avoid nesting other functions when filtering by simple word occurrences, feel free to try out the add-on described at the end.
Note. If the text case is important, use FIND, otherwise, pick SEARCH.

The SEARCH function will do just fine for my example since the text case is irrelevant:

=SEARCH(search_for, text_to_search, [starting_at])
• search_for is the text I want to find. It's really important to wrap it with double-quotes: "ca". Required.
• text_to_search is the range to scan for the needed text. Required. It's A1:A20 for me.
• starting_at indicates the starting position for the search — the number of the character to start looking from. It's completely optional but I need to use it. You see, all order IDs consist of letters and numbers, meaning a pair of CA may occur somewhere in between. The identical pattern of all IDs allows me to look for CA starting from the 8th character.

After collecting all these parts together, I get the desired result:

`=FILTER(A1:E20,SEARCH("ca",A1:A20,8))`

### How to filter by date and time in Google Sheets

Filtering by date and time also requires using additional functions. Depending on your criteria, you may need to embed DAY, MONTH, YEAR, or even DATE and TIME in the main Google Sheets FILTER function.

Tip. If you're not familiar with these or always mess things up with dates — no worries. The tool described at the end doesn't require any functions at all.

#### Example 1. Date is

To get ahold of those orders that are due on 9 January 2020, I'll invite the DATE function:

`=FILTER(A1:E20,C1:C20=DATE(2020,1,9))`

Note. This only works if your cells don't contain time units along with the date (you spreadsheet may add them by default). To make sure, just select a cell and check what appears in the formula bar:

If the time is there and removing it is not an option, you should use either QUERY or a more complex condition in your Google Sheets FILTER function, like this:

`=FILTER(A1:E20,C1:C20>=DATE(2020,1,9),C1:C20<DATE(2020,1,10))`

Tip. I talk about multiple conditions in more detail below.

#### Example 2. Date contains

If you're interested in a particular month or a year only, you can get by with MONTH and YEAR functions. Put the range with dates right into it (C1:C20) and specify the number of the month (or year) it should be equal to (=1):

`=FILTER(A1:E20,MONTH(C1:C20)=1)`

#### Example 3. Date is before/after

To get the data that falls before or after the specified date, you will need the DATE function and such comparison operators as greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=).

Here are the orders that were received on and after 1 January 2020:

`=FILTER(A1:E20,D1:D20>=DATE(2020,1,1))`

Of course, you can easily substitute DATE with MONTH or YEAR here. The result won't differ from the one above:

`=FILTER(A1:E20,YEAR(D1:D20)>=2020)`

#### Example 4. Time

When filtering on Google Sheets by time, the drill is exactly the same as with dates. You use the additional TIME function.

For instance, to get only days with a timestamp after 2:00 PM, the formula will be:

`=FILTER(A1:B10,A1:A10>TIME(14,0,0))`

However, when it comes to using the HOUR function (as with MONTH for dates), the game changes a bit. Time is tricky enough in spreadsheets, so a few adjustments are necessary.

To return all rows with timestamps between 2:00 PM and 12:00 PM, do this:

1. Enclose the range with timestamps (A1:A10) in a separate HOUR function. This will indicate where to look.
2. Then add another HOUR function to set the time itself.

`=FILTER(A1:B10,HOUR(A1:A10)>=HOUR("2:00:00 PM"))`

Tip. See that the result doesn't include 12:41 PM? That's because the spreadsheet treats it as 00:41 which is less than 2:00.

If you find a more elegant solution, please share it in the comments section below.

### How to filter in Google Sheets using cell references

Each time you create a Google Sheets filter formula, you need to enter the condition as is: whether a word or its part, the date, etc. Unless you're familiar with cell references.

They make lots of things about formulas easier. Because instead of typing everything out, you can simply refer to cells with conditions.

Remember how I looked for all orders that are late? I can quickly refer to E4 with the text Late to do the same:

`=FILTER(A1:E20,E1:E20=E4)`

The result won't differ at all:

You can repeat this with all the aforementioned formulas. For example, avoid adding more functions like DATE and just refer to the cell with a date of interest:

`=FILTER(A1:E20,C1:C20=C15)`

Tip. Cell references also let you filter from another sheet. You just have to bring the sheet name:

`=FILTER(Orders!A1:E20,Orders!C1:C20=Orders!C15)`

### Google Sheets FILTER formulas with multiple criteria

While I mainly used one condition in all Google Sheets filter formulas before, it is more likely that you'll need to filter a table by a few conditions at a time.

#### Example 1. IS BETWEEN logic

To find all rows falling between two numbers/dates/times, the optional arguments of the function will come in handy — condition2, condition3, etc. You just duplicate the same range each time but with a new condition.

Look, I'm going to return only those orders that cost me more than \$250 but less than \$350:

`=FILTER(A1:E20,B1:B20>=250,B1:B20<350)`

#### Example 2. OR logic in the Google Sheets FILTER function

Sadly, to get all rows that contain different records in a column of interest, the previous way won't do. So how can I check all orders that are both on their way and late?

If I do try the previous method and enter each order status to a separate condition, I'll get the #N/A error:

Thus, to correctly set the OR logic in the FILTER function, I should sum these two criteria within one condition:

`=FILTER(A1:E20,(E1:E20="Late")+(E1:E20="On the way"))`

What's even more likely than applying a few conditions to one column is creating a filter in Google Sheets for multiple columns.

The arguments are all the same. But each new part of the formula requires a new range with its own criteria.

Let's try and make the FILTER function in Google Sheets return orders that fall under all the following rules:

1. They should be \$200-400 worth:

`A1:E20,B1:B20>=200,B1:B20<=400`

2. Are due in January 2020:

`MONTH(C1:C20)=1`

3. And are still on their way:

`E1:E20="on the way"`

`=FILTER(A1:E20,B1:B20>=200,B1:B20<=400,MONTH(C1:C20)=1,E1:E20="on the way")`

FILTER function is great and all, but sometimes it can be too much. Keeping track of all arguments, delimiters, nested functions and whatnot can be extremely confusing and time-consuming.

Luckily, we have a better solution that transcends both Google Sheets FILTER function and their standard tool — Multiple VLOOKUP Matches.

Don't get puzzled by its name. It resembles Google Sheets VLOOKUP function because it searches for matches. Just as the FILTER function does. Just as I did above.

Here are 5 main advantages of the tool over the Google Sheets FILTER function:

1. You won't have to think about operators for different conditionsjust pick the one from the list:
2. Enter dates and time as you always do in spreadsheets — no more special functions:
3. Create and delete multiple conditions for multiple columns real quick:
4. Preview the result and adjust conditions (if needed) before pasting everything to your sheet:

I truly encourage you to install Multiple VLOOKUP Matches and give it a go. To look through its options closer, visit its tutorial page or watch a special instructional video:

Tip. If you're looking for the video transcript, visit this blog post.

## You may also be interested in

1. Ian says:

Hi there,
Thanks for this super helpful resource, it has helped me to get a hold of just how much we can achieve with filtering. I have a question about whether it is possible to filter for the same condition from two different sheets in the same Google Sheet. I tried applying the OR logic as above but it is returning a VALUE error message, I've copied the formula I tried below.
=FILTER('IA'!C2:F5,'IA'!C2:C5="Family activity packs")+('SF'!C2:F5,'SF'!C2:C5="Family activity packs")
Any help would be gratefully received.

• Hi Ian,

Thank you for your lovely feedback!

={FILTER(IA!C2:F5,IA!C2:C5="Family activity packs");FILTER(SF!C2:F5,SF!C2:C5="Family activity packs")}

2. Saaha says:

.thanks, and sorry to bother you again Is the connection between drop list and table possible in this situation .if i have created a drop down e = azam, nazam, shazam which is the content of b column, and i want A C E column based on drop down list. how its possible with power query ...mam help me please

• Saaha says:

Sorry """ from above question i need A C D column based on drop down list

• Hi again,

Sure, it's possible. Try to use a condition like this: 'QUERY(..., "select A,C,E where B=E1")
Assuming, your drop-down is in E1.

You'll find more examples and ready-made formulas on the blog post devoted to QUERY.

• Saaha says:

hi mam, Sorry to bother again,
i did the same thing but its showing error, i applied 'QUERY(Range "select A,C,D where B=E1")
where Column B unable to take reference Cell E1,,,
im try trying but its showing error...

• Hi Saaha,

There should be a comma right after your range, before "select...". If it doesn't help, for me to be able to help you better, please share an editable copy of your spreadsheet with us (support@apps4gs.com). Make sure to highlight the QUERY formula you created so I could see where to look.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

• Saaha says:

Hi Mam,
i've sent you sheet copy to you through mail. sorry actually i ve sent you same file twice, and file name is" Saaha file to resolve file to resolve filter problem" Please go through

• Thank you for sharing the spreadsheet, Saaha.

• Saaha says:

Thank you so much mam,
i was waiting for your reply , and you are so humble, you guys are always help us,
and thank you again Mam..🙏🙏🙏🙏

• You're most welcome, Saaha! :)

3. Saaha says:

if there are 5 column i.e A B C D E but i want to filter only A C E so how could we use filter formula for this condition please give me formula

• Saaha says:

if there are 5 column i.e A B C D E but i want to filter only A C E so how could we use filter formula for this condition please give me formula, I dont want entire column i just need information of A C E where B=L4 please help me

• Saaha says:

Help me i want to filter only A C E so how could we use filter formula for this condition please give me formula, I dont want entire column i just need information of A C E where B=L4 please help me

• Hello Saaha,

If you'd like to return only part of the columns based on the contents of some of them, you'd better use the QUERY function instead.

• Saaha says:

thanks alote , and sorry to bother you again if i have created a drop down list E4 = azam, nazam, shazam which is the content of B column, and i want A C E column based on drop down list , Its possible with the power query or filer formula and How ?? Is the connection between drop list and table possible in this situation please give me a formula?

• saaha says:

• Charles says:

Actually, you can do this with the Filter function directly! You don't need to use Query

e.g. =FILTER({C:C,B:B},D:D>=2)

4. Paul says:

THANKS!!!!

5. Mike says:

Thank you so much for posting the note on how to use OR logic with a filter function. For as practical a use case as that is, I don't see this anywhere else on the internet and I can't imagine who would have figured this out intuitively. This saved me hours.

6. Paul M Bielaczyc says:

Hi there,
I am trying to use the Filter function to snag data from another tab. This is the formula that I have, = FILTER(Artists!A:D,Artists!E:E= "yes").

I would like to be able to insert new columns into the Artist sheet between column D and E without the FILTER formula changing. I tried adding Absolute References to the formula like so, = FILTER(Artists!A:D,Artists!\$E:\$E= "yes"), but when I Insert a column in the Artist sheet between column D and E, the FILTER function changes to Artists!F:F (or Artists!\$F:\$F).

Is there anyway to use absolute references within the FILTER function so it doesn't update when new columns are added?

Paul

• Hi Paul,

Formulas in Google Sheets are dynamic. If you refer to a particular range/column and then insert new cells/columns causing your original range change letters, ranges/columns in formulas will change accordingly. This way your formulas will always refer to the same range and don't break.

The only way to make the formula always use the contents of a particular column is to reference it with the INDIRECT function.

• Paul says:

Thanks! I ended up finding the INDIRECT function a few hours after posting. Thanks for confirming that I did it right. :)

• My pleasure, Paul!

7. Jason says:

Hi Natalia, I have been trying to combine two of the concepts you outlined with a little trouble. I am trying to filter where text 'contains' multiple conditions in one column. So here is what I have:

=FILTER(\$WO3:\$W,(SEARCH("Sub",\$R3:\$R))+(SEARCH("Viv",\$R3:\$R)))

I am trying to display columns O through W filtering for column R containing either 'Sub' or 'Viv', but all I get is #N/A stating there are no matches. I am attempting to combine the two examples you provided:

=FILTER(A1:E20,SEARCH("ca",A1:A20,8))

=FILTER(A1:E20,(E1:E20="Late")+(E1:E20="On the way"))

Let me know if this is possible, thank you!

• Hi Jason,

Your formula doesn't work because SEARCH returns errors for all rows that don't have 'viv' or 'sub'. Wrap those SEARCH formulas into IFERROR and it'll work:
=FILTER(\$WO3:\$W,IFERROR(SEARCH("Sub",\$R3:\$R))+IFERROR(SEARCH("Viv",\$R3:\$R)))