Select all data where a condition is true in Google Sheets

The syntax you are looking for

=QUERY(A1:D100, "SELECT * WHERE C = 'Active'")

That returns every row in A1:D100 where column C holds the text Active, including all four columns of each matching row.

QUERY is Google Sheets' SQL-like function. SELECT * means "all columns", WHERE filters rows. If you know SQL this will feel immediately familiar - with a handful of differences that cause most of the errors people hit. Those are covered below.

Reference columns by letter, not by header

This is the single biggest source of confusion.

Inside a QUERY string you refer to columns by their spreadsheet letter, not by the header text in row 1:

=QUERY(A1:D100, "SELECT * WHERE C = 'Active'")

Even if C1 contains the header Status, you write C. Writing WHERE Status = 'Active' fails.

There is one exception. When the first argument is an array or a function result rather than a plain range, columns become Col1, Col2, Col3 numbered relative to that data:

=QUERY({A1:B100; F1:G100}, "SELECT * WHERE Col2 = 'Active'")

Rule of thumb: plain range gives you letters, anything else gives you Col numbers.

Quoting rules by data type

Getting this wrong produces empty results with no error message, which makes it hard to debug. The rules:

Text - single quotes:

=QUERY(A1:D100, "SELECT * WHERE C = 'Active'")

Numbers - no quotes:

=QUERY(A1:D100, "SELECT * WHERE D > 500")

Dates - the date keyword and ISO format:

=QUERY(A1:D100, "SELECT * WHERE B > date '2026-01-01'")

The date form catches everyone. You cannot write WHERE B > '2026-01-01' - the literal date keyword is required, and the value must be yyyy-mm-dd regardless of how the column is displayed in the sheet.

Booleans - bare true or false:

=QUERY(A1:D100, "SELECT * WHERE E = true")

Handling the header row

If your range includes headers, tell QUERY with the third argument:

=QUERY(A1:D100, "SELECT * WHERE C = 'Active'", 1)

The 1 means "the first row is a header". Without it, QUERY guesses - and when it guesses wrong it either treats your header as data or, worse, decides a numeric column is text because of the header string and silently stops matching numeric comparisons.

Pass 0 for no header row. Being explicit here prevents a whole category of confusing results.

Multiple conditions

Combine with AND, OR and NOT, using parentheses for grouping:

=QUERY(A1:D100, "SELECT * WHERE C = 'Active' AND D > 500")

=QUERY(A1:D100, "SELECT * WHERE C = 'Active' OR C = 'Pending'")

=QUERY(A1:D100, "SELECT * WHERE (C = 'Active' OR C = 'Pending') AND D > 500")

Without the parentheses, AND binds tighter than OR, exactly as in SQL, and you get results you did not intend.

Partial matches and wildcards

LIKE with % as the wildcard:

=QUERY(A1:D100, "SELECT * WHERE B LIKE '%error%'")

% matches any number of characters, _ matches exactly one. '%error%' finds it anywhere in the cell, 'error%' only at the start.

LIKE is case-sensitive. To ignore case, use MATCHES with a regular expression and the (?i) flag:

=QUERY(A1:D100, "SELECT * WHERE B MATCHES '(?i).*error.*'")

Note that MATCHES must match the entire cell, which is why the pattern is wrapped in .* on both sides.

Empty and non-empty cells

=QUERY(A1:D100, "SELECT * WHERE C IS NOT NULL")

=QUERY(A1:D100, "SELECT * WHERE C IS NULL")

Careful: IS NULL matches genuinely empty cells. A cell containing an empty string - common when data arrives from a form or an import - is not null. If rows you expect are missing, test with WHERE C != '' as well.

Using a cell as the condition

To make the filter interactive, concatenate a cell reference into the query string:

=QUERY(A1:D100, "SELECT * WHERE C = '" & F1 & "'")

Type a status into F1 and the results update. The quote placement is fiddly - the ' characters belong inside the string literals, so the concatenated value ends up wrapped in single quotes.

For a numeric column, drop the inner quotes:

=QUERY(A1:D100, "SELECT * WHERE D > " & F1)

And for a date:

=QUERY(A1:D100, "SELECT * WHERE B > date '" & TEXT(F1, "yyyy-mm-dd") & "'")

Beyond SELECT *

Once the WHERE clause works, QUERY gives you the rest of the SQL vocabulary.

Specific columns, reordered:

=QUERY(A1:D100, "SELECT B, A, D WHERE C = 'Active'")

Sorted:

=QUERY(A1:D100, "SELECT * WHERE C = 'Active' ORDER BY D DESC")

Top 10:

=QUERY(A1:D100, "SELECT * WHERE C = 'Active' ORDER BY D DESC LIMIT 10")

Grouped and aggregated:

=QUERY(A1:D100, "SELECT C, COUNT(A), SUM(D) WHERE D > 0 GROUP BY C")

GROUP BY is where QUERY decisively beats every alternative - FILTER cannot aggregate at all.

When FILTER is the better tool

FILTER does the same row-matching job with normal spreadsheet syntax instead of a query string:

=FILTER(A1:D100, C1:C100 = "Active")

=FILTER(A1:D100, (C1:C100 = "Active") * (D1:D100 > 500))

Multiply conditions for AND, add them for OR.

Prefer FILTER when: you have one or two straightforward conditions, you want live cell references without string concatenation, or your comparison values are already in cells. It is easier to read and much easier to debug.

Prefer QUERY when: you need to select or reorder specific columns, sort, group, aggregate, or limit rows. FILTER cannot do any of those.

Why a QUERY returns nothing

Working through these in order resolves most cases:

1. Column referenced by header name. Use the letter, or Col1 style for array inputs.
2. Missing quotes around text. WHERE C = Active looks for a column named Active.
3. Mixed data types in the column. QUERY picks one type per column by majority and discards the rest. Numbers stored as text is the classic case - the column looks numeric but WHERE D > 500 matches nothing. Fix the source data rather than the formula.
4. Header row not declared. Add the third argument.
5. Date written as plain text. The date keyword and yyyy-mm-dd are both mandatory.
6. Trailing whitespace. 'Active ' is not 'Active'. Wrap in TRIM at the source or use LIKE 'Active%'.
7. Case mismatch. All string comparison in QUERY is case-sensitive.

Doing the same thing from code

QUERY runs inside the spreadsheet. When an application needs filtered rows, you want that filtering to happen server-side - pulling the whole sheet down and filtering in JavaScript wastes bandwidth and gets slow fast.

GKit SheetsAPI exposes the same idea as query parameters:

GET /api/spreadsheets/{userKey}/Orders?where=status:Active

GET /api/spreadsheets/{userKey}/Orders?where=status:Active&sort=-total&limit=10

Filtering, sorting and pagination happen before the response is built, so you transfer only the matching rows. The mapping from QUERY is direct:

Google Sheets QUERY	SheetsAPI
WHERE C = 'Active'	?where=status:Active
ORDER BY D DESC	?sort=-total
LIMIT 10	?limit=10
SELECT B, A, D	?fields=name,id,total

There is more in paginate, search and sort a Google Sheet over REST. Free while in beta.

Quick reference

Goal	Formula
All rows where text matches	=QUERY(A1:D100, "SELECT * WHERE C = 'Active'")
All rows above a number	=QUERY(A1:D100, "SELECT * WHERE D > 500")
All rows after a date	=QUERY(A1:D100, "SELECT * WHERE B > date '2026-01-01'")
Two conditions, both true	... WHERE C = 'Active' AND D > 500
Either condition true	... WHERE (C = 'Active' OR C = 'Pending')
Contains a substring	... WHERE B LIKE '%error%'
Case-insensitive contains	... WHERE B MATCHES '(?i).*error.*'
Non-empty cells only	... WHERE C IS NOT NULL
Condition from a cell	"SELECT * WHERE C = '" & F1 & "'"
Simple filter, no SQL	=FILTER(A1:D100, C1:C100 = "Active")