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.
FILTER in Google Sheets scans your data and returns the required information that meets your criteria.
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:
Now, with these notes in mind, let's see how the arguments take shape of different formulas.
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.
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")
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")
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.
The SEARCH function will do just fine for my example since the text case is irrelevant:
After collecting all these parts together, I get the desired result:
=FILTER(A1:E20,SEARCH("ca",A1:A20,8))
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.
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))
=FILTER(A1:E20,C1:C20>=DATE(2020,1,9),C1:C20<DATE(2020,1,10))
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)
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)
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:
=FILTER(A1:B10,HOUR(A1:A10)>=HOUR("2:00:00 PM"))
If you find a more elegant solution, please share it in the comments section below.
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)
=FILTER(Orders!A1:E20,Orders!C1:C20=Orders!C15)
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.
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)
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:
A1:E20,B1:B20>=200,B1:B20<=400
MONTH(C1:C20)=1
E1:E20="on the way"
Put all these parts together and your Google Sheets filter formula for multiple columns is ready:
=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:
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: