QUERY WHERE Clause in Google Sheets

The QUERY WHERE clause in Google Sheets filters rows based on conditions you define inside the query string. It works like a row-level filter built into the QUERY function, you set the rules, and WHERE returns only the rows that match. We’ll cover text filters, numeric comparisons, date handling, multiple conditions with AND/OR, and text matching with contains.

In This Guide

• WHERE Clause Syntax
• How to Filter Rows with WHERE: Step-by-Step
• Filter by Text Value
• Filter by Number
• Combine Conditions with AND / OR
• Filter with Contains, Starts With, Ends With
• Filter by Date
• Common WHERE Mistakes
• Tips
• Related Google Sheets Tutorials
• FAQ

WHERE Clause Syntax

WHERE goes inside the query string, after SELECT:

The condition compares a column to a value using an operator:

Operator	Meaning	Example
=	Equals	WHERE C = 'Scotland Yard'
!=	Not equal	WHERE C != 'Whitehall'
>	Greater than	WHERE F > 400
<	Less than	WHERE E < 10
>=	Greater than or equal	WHERE F >= 500
<=	Less than or equal	WHERE E <= 15

How to Filter Rows with WHERE: Step-by-Step

We’ll use the same sales records table from the QUERY pillar guide. The data is in A1:G11 with 10 transactions.

Sample Data

	A	B	C	D	E	F	G
1	Date	Salesperson	Region	Product	Units	Revenue	Commission
2	1/5/2026	Sherlock Holmes	Baker Street	Listening Device	12	$239.88	$24.00
3	1/7/2026	Irene Adler	Scotland Yard	Forensic Chemistry Set	8	$360.00	$36.00
4	1/8/2026	Inspector Lestrade	Baker Street	Pocket Watch	15	$525.00	$52.50
5	1/10/2026	Sherlock Holmes	Baker Street	Field Binoculars	5	$325.00	$32.50
6	1/12/2026	Mycroft Holmes	Scotland Yard	Cipher Decoder	20	$570.00	$57.00
7	1/14/2026	Irene Adler	Whitehall	Magnifying Glass	25	$624.75	$62.50
8	1/15/2026	Inspector Lestrade	Scotland Yard	Listening Device	10	$199.90	$20.00
9	1/18/2026	Sherlock Holmes	Baker Street	Disguise Kit	3	$269.97	$27.00
10	1/20/2026	Mycroft Holmes	Whitehall	Brass Telescope	8	$336.00	$33.60
11	1/22/2026	Irene Adler	Scotland Yard	Lockpick Set	6	$330.00	$33.00

Your data lives in A1:G11. Row 1 is the header. Columns are Date (A), Salesperson (B), Region (C), Product (D), Units (E), Revenue (F), and Commission (G).

Select an empty cell and enter:

This returns all seven columns, but only rows where Region (column C) equals “Scotland Yard”. The result is 4 rows: Irene Adler’s Forensic Chemistry Set, Mycroft Holmes’s Cipher Decoder, Inspector Lestrade’s Listening Device, and Irene Adler’s Lockpick Set.

Enter a new formula:

This returns rows where Revenue exceeds $400, showing only the Salesperson, Product, and Revenue columns. Three rows qualify: Inspector Lestrade’s Pocket Watch ($525.00), Mycroft Holmes’s Cipher Decoder ($570.00), and Irene Adler’s Magnifying Glass ($624.75).

Enter:

This returns rows where the region is Baker Street AND revenue exceeds $300. Two rows match: Inspector Lestrade’s Pocket Watch ($525.00) and Sherlock Holmes’s Field Binoculars ($325.00).

Filter by Text Value

Exact text matches use the = operator with single quotes:

This returns Sherlock Holmes’s 4 transactions from the 10-row dataset. The text match is case-sensitive, 'sherlock holmes' returns nothing.

To exclude a specific value, use !=:

This returns all rows except the two Whitehall transactions.

Filter by Number

Numeric comparisons do not use quotes around the value:

This returns rows where Units (column E) is 15 or more. Three rows qualify: Inspector Lestrade’s 15 Pocket Watches, Mycroft Holmes’s 20 Cipher Decoders, and Irene Adler’s 25 Magnifying Glasses.

To filter a range of values, combine two conditions:

This returns transactions between $300 and $500 in revenue.

Combine Conditions with AND / OR

AND (all conditions must be true)

Returns Scotland Yard transactions with revenue over $400. One row matches: Mycroft Holmes’s Cipher Decoder ($570.00).

OR (any condition can be true)

Returns all transactions from either Whitehall or Scotland Yard. Six rows match.

Combining AND and OR

Use parentheses to group conditions:

This returns rows from Baker Street or Scotland Yard where revenue exceeds $400. Two rows match: Inspector Lestrade’s Pocket Watch ($525.00) from Baker Street and Mycroft Holmes’s Cipher Decoder ($570.00) from Scotland Yard.

The logic follows the same patterns as IF with AND and OR, AND narrows results, OR broadens them.

Filter with Contains, Starts With, Ends With

QUERY WHERE supports partial text matching with three keywords:

Keyword	Meaning	Example
contains	Text includes the value anywhere	WHERE B contains 'Holmes'
starts with	Text begins with the value	WHERE D starts with 'E'
ends with	Text ends with the value	WHERE D ends with 's'

Contains Example

This returns every row where the Salesperson name includes “Holmes.” Five rows match, all transactions by Sherlock or Mycroft Holmes.

Starts With Example

This returns products that start with the letter F: Forensic Chemistry Set and Field Binoculars.

Filter by Date

Dates in QUERY WHERE need the date keyword and YYYY-MM-DD format:

This returns transactions after January 15, 2026. Three rows from the 10-row dataset qualify: Sherlock Holmes 1/18/2026 ($269.97), Mycroft Holmes 1/20/2026 ($336.00), and Irene Adler 1/22/2026 ($330.00). Inspector Lestrade’s 1/15 sale does not make the cut, because January 15 is not after January 15.

Common WHERE Mistakes

Missing single quotes around text values

WHERE C = Scotland Yard breaks because QUERY interprets Scotland Yard as a column name. The fix: WHERE C = 'Scotland Yard' with single quotes.

Using double quotes inside the query string

The query string is already inside double quotes, so using double quotes for text values causes a syntax error. Use single quotes for text values inside WHERE.

Case mismatch on text comparisons

WHERE B = 'sherlock holmes' returns no results when the data contains “Sherlock Holmes.” QUERY WHERE is case-sensitive. Either match the exact case or use lower(): WHERE lower(B) = 'sherlock holmes'.

Forgetting the date keyword

WHERE A > '2026-01-15' compares dates as text strings, which gives wrong results. Use WHERE A > date '2026-01-15' with the date keyword.

Wrong column letter

Column letters in the query string must match the columns in your data range. If your range is A1:G11, then A is the first column. If your range starts at C1, then C is the first column in the query. Double-check that you are referencing the correct column.

Tips

1. Start with SELECT * to see all columns. Once you confirm your WHERE condition returns the right rows, narrow the SELECT to the columns you need.

2. Use cell references for dynamic filtering. Instead of hardcoding a value, reference a cell: =QUERY(A1:G11, "SELECT * WHERE C = '"&I1&"'"). This lets you change the filter by editing cell I1. The concatenation ('"&I1&"') inserts single quotes around the cell value.

3. Chain WHERE with other clauses. WHERE combines with SELECT, GROUP BY, ORDER BY, and LIMIT. For example: "SELECT C, SUM(F) WHERE F > 200 GROUP BY C" filters first, then groups. WHERE always runs before GROUP BY.

Related Google Sheets Tutorials

• QUERY Function: Complete Guide: covers all QUERY clauses with syntax and examples
• QUERY SELECT: choose specific columns and reorder the output
• QUERY GROUP BY: summarize and aggregate data with SUM, COUNT, and AVG
• IF with AND / OR: same AND/OR logic applied to IF statements