Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
58 votes

How to export a filtered set of rows from a Google Spreadsheet?

Create a new sheet (Sheet2) within the spreadsheet. Set the A1 cell to be =filter(Sheet1!A:X, Sheet1!A:A>1). (See docs on the filter function.) You should then be able to save or export Sheet2 as ...
user29020's user avatar
  • 922
11 votes

Is there any way to use a formula in a Google Sheets filter view?

Remember to us the cell under the filter heading in the formula, so if your filter is on A3, use A4. Here is an example for a filter in cell A1, checking if it matches a value in C1 =(A2=$C$1) for ...
John Pratt's user avatar
9 votes
Accepted

Google Sheets Filter Multi Column List With Another List Using A Function

Use a FILTER/MATCH combination. Example on your data below: =filter(C52:F57; match(C52:C57; I53:I54; 0))
ttarchala's user avatar
  • 1,679
8 votes

Filter multiple values using filter by condition tool in Google Spreadsheet

And for those who are using the "custom formula" option you will need to know that the formula is based off the cell immediately under the filter. So if your have a filter in Cell A1, use A2 in the ...
John Pratt's user avatar
7 votes

Why are sheet Filter Views not updating for new rows?

Apparently in Google Sheets, when you turn on the filter (say on the first row) and you don't create a named filter view, that filter on the first row is still set with a range that only goes to the ...
Rhetsy's user avatar
  • 71
5 votes

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

I have found a workaround that creates the same result, though it may take a little bit more time to set up. What I do is create a pivot table of the data that comes in from the Google Forms results,...
Roman Y. K's user avatar
4 votes

Filtering a protected column

At this time, on Google Sheets owner and users with permissions to edit a protected range are allowed to filter it. to edit saved filters. users without permissions to edit a protected range ...
Rubén - Volunteer Moderator -'s user avatar
4 votes

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

Try a Script that fires onFormSubmit. Get and then re-set the same criteria. var sheet = SpreadsheetApp.getActiveSheet(); var filter = sheet.getFilter(); if (filter) { var criteria = filter....
Peter's user avatar
  • 41
4 votes
Accepted

How can I filter a set of data based on a column containing a given date in sheets

Short answer Try =FILTER( 'Output Checker'!A:P, ROUNDDOWN('Output Checker'!A:A)=A2, 'Output Checker'!L:L <2, 'Output Checker'!M:M ="NO", 'Output Checker'!N:N ="Yes", 'Output Checker'...
Rubén - Volunteer Moderator -'s user avatar
4 votes
Accepted

No query result when one of the columns used in the output never has a value

You are used to an empty cell having a value of zero in other functions. However, QUERY acts uniquely from other functions. As currently set up, you are asking QUERY to subtract a null string from a ...
Erik Tyler's user avatar
  • 5,048
3 votes

Google Sheets Filter Multi Column List With Another List Using A Function

@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this: =filter(C52:F57; ISNA (match(C52:C57; I53:I54; 0)))
Markus Frömmel's user avatar
3 votes
Accepted

Increment Cell Reference in Formula by 10

On Locking Ranges using "$" The first problem I see is the $ sign is placed before the row. This tells the spreadsheet that it needs to LOCK that row (2 & 12) from changing numbers. The drag-...
Xzila's user avatar
  • 843
3 votes

How to export a filtered set of rows from a Google Spreadsheet?

You can make a Pivot table for that, inserting the desired filter. Then just export it as CSV.
Guilherme Franco's user avatar
3 votes

Is there any way to use a formula in a Google Sheets filter view?

In filter you choose filter by condition, then custom formula is =(O:O >date(2016,1,1)) + (O:O ="") The letter is the column letter. The + is use to do multiple conditions. (OR) Best practice ...
Frederic Lacoste Bienvenue's user avatar
3 votes
Accepted

How do I get rid of the blank line when I use UNIQUE in Google Sheets?

=UNIQUE(FILTER(A2:A, A2:A<>""))
user0's user avatar
  • 26.6k
3 votes

QUERY with Week Number

weeknum is not a valid scalar function so you need to trick it like this: =ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY({A2:B, WEEKNUM(A2:A)}, "where Col3 = 21")), 999^99, 2) =FILTER(A2:B, WEEKNUM(A2:A)=21)
user0's user avatar
  • 26.6k
3 votes
Accepted

If values in a range appear in another range, remove from list in another cell

Here's a way to achieve this =FILTER(B1:B10, iferror(ArrayFormula( VLOOKUP(B1:B10,$A$1:$A$10,1,false)) ,true)) EDIT : If you have a lot of values this is maybe a better solution : =FILTER(B1:...
pjmg's user avatar
  • 2,010
3 votes
Accepted

How do I exclude a list of values using a filter in Google Sheets?

The + operator corresponds with an or condition. Your test will yield true for any value, because a value is always different from either value1 or value2 or both, when value1 and value2 are not equal....
doubleunary's user avatar
  • 14.7k
2 votes

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

Sometimes if I need to figure out a function on Google Sheets, I start recording a macro, make the change that I want to make, and then stop recording the macro. Doing this, I found out that there is ...
wriddle2's user avatar
2 votes

FILTER(condition OR condition) syntax?

=FILTER(A:F, REGEXMATCH(A:A, "Combat Medic|Universal")) =QUERY(A:F, " where A matches 'Combat Medic|Universal'", 0)
user0's user avatar
  • 26.6k
2 votes

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

Look into the Tools -> Add a Slicer option. Select the table and set it up like a standard filter. Then I've found that it is important to click the 3 dots and set it as a Set Current Filter as ...
ImpossibleBag's user avatar
2 votes

Filter multiple values using filter by condition tool in Google Spreadsheet

The REGEXMATCH() certainly works but it's hard to remember and, according to my limited testing, much slower than simply using an OR() like this: =NOT(OR(N2="facebook",N2="pinterest",N2="twitter")) ...
RedYeti's user avatar
  • 293
2 votes

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

I spent the morning digging around for something similar. Unfortunately the function call to UpdateFilter() posted by wriddle2 didn't work. The function isn't defined in Apps Script for a Google ...
Jordan Urie's user avatar
2 votes

Query or filter the data where the start time is past 3:00 pm

3 PM is 15h so you can do: =FILTER(A1:D, HOUR(C1:C) >= 15)
user0's user avatar
  • 26.6k
2 votes
Accepted

Dynamic range for filter function

You might use INDIRECT within a query, say: =query(indirect($B4&"!D:H");"select sum(H) where D ='"&B$3&"' label sum(H) ''") but really you seem to have backed yourself into a corner. You ...
pnuts's user avatar
  • 17.6k
2 votes

Filter multiple categories Spreadsheet

You may use an additional column to filter categories in place. Here's the formula to check if any text matches "golf" or "ski" : =REGEXMATCH(A2,"ski|golf") It will give True if cell A2 contains ...
Max Makhrov's user avatar
2 votes

Restrict ImportRange to only visible rows

Follow this answer. Create a new sheet within the same document Use filter function (Set the A1 cell to be =filter(Sheet1!A:X, Sheet1!A:A>1)). (See docs on the filter function.) Import range from ...
Mayank Kumar Chaudhari's user avatar
2 votes

Why are sheet Filter Views not updating for new rows?

Google Sheet's Filter Views only work on the range specified. This range does not update upon the adding of rows or columns. The second issue, the number not being sorted, is because of the first one....
Mark Shamy's user avatar
2 votes

Can you auto update the range for a filter in Google Sheets?

if your formula is: =FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10) then just don't include the last number of closing range: =FILTER(A2:B, A2:A > 5, D2:D < 10)
user0's user avatar
  • 26.6k
2 votes

Google Sheets' Query or Filter solution

Wilson. Here is another approach (assuming the date is in A1:C): =ArrayFormula(IFERROR(VLOOKUP(UNIQUE(A:A),QUERY(A:C,"Select * Order By B Desc",1),{1, 2, 3},FALSE))) Just be sure to format the ...
Erik Tyler's user avatar
  • 5,048

Only top scored, non community-wiki answers of a minimum length are eligible