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:

=QUERY(data, "SELECT columns WHERE condition")

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

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

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:

=QUERY(A1:G11, "SELECT * WHERE C = 'Scotland Yard'")

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:

=QUERY(A1:G11, "SELECT B, D, F WHERE F > 400")

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:

=QUERY(A1:G11, "SELECT B, D, F WHERE C = 'Baker Street' AND F > 300")

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:

=QUERY(A1:G11, "SELECT B, D, F WHERE B = 'Sherlock Holmes'")

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

To exclude a specific value, use !=:

=QUERY(A1:G11, "SELECT * WHERE C != 'Whitehall'")

This returns all rows except the two Whitehall transactions.

Filter by Number

Numeric comparisons do not use quotes around the value:

=QUERY(A1:G11, "SELECT B, D, E WHERE E >= 15")

This returns rows where Units (column E) is 15 or more. Four rows qualify: Inspector Lestrade’s 15 Pocket Watchs, Mycroft Holmes’s 20 Cipher Decoders, Irene Adler’s 25 Magnifying Glasss, and Sherlock Holmes’s 30 Evidence Bag Kits.

To filter a range of values, combine two conditions:

=QUERY(A1:G11, "SELECT B, D, F WHERE F >= 300 AND F <= 500")

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

Combine Conditions with AND / OR

AND (all conditions must be true)

=QUERY(A1:G11, "SELECT B, D, F WHERE C = 'Scotland Yard' AND F > 400")

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

OR (any condition can be true)

=QUERY(A1:G11, "SELECT B, C, F WHERE C = 'Whitehall' OR C = 'Scotland Yard'")

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

Combining AND and OR

Use parentheses to group conditions:

=QUERY(A1:G11, "SELECT B, C, F WHERE (C = 'Baker Street' OR C = 'Scotland Yard') AND F > 400")

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:

Contains Example

=QUERY(A1:G11, "SELECT B, D, F WHERE B contains 'Holmes'")

This returns every row where the Salesperson name includes “Holmes.” Seven rows match — all transactions by Fred, Ginny, or Mycroft Holmes.

Starts With Example

=QUERY(A1:G11, "SELECT B, D WHERE D starts with 'S'")

This returns products that start with the letter S: Forensic Chemistry Set and Cipher Decoder.

Filter by Date

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

=QUERY(A1:G11, "SELECT B, A, F WHERE A > date '2026-01-15'")

This returns transactions after January 15, 2026. Four rows from the 10-row dataset qualify: Sherlock Holmes (1/18/2026), Mycroft Holmes (1/20/2026), Irene Adler (1/22/2026), and Inspector Lestrade (no — he is 1/15, which is not after).

The actual matching rows are: Sherlock Holmes 1/18/2026 ($269.97), Mycroft Holmes 1/20/2026 ($336.00), and Irene Adler 1/22/2026 ($330.00).

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 = 'fred 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) = 'fred 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

Frequently Asked Questions

How do you filter rows with QUERY WHERE in Google Sheets?

Add a WHERE clause to your QUERY string. For example, =QUERY(A1:G11, "SELECT * WHERE F > 400") returns only rows where column F exceeds 400. Text values need single quotes: WHERE C = 'Scotland Yard'.

Is QUERY WHERE case-sensitive in Google Sheets?

Yes. Text comparisons in QUERY WHERE are case-sensitive. WHERE B = 'fred holmes' returns no results if the data contains “Sherlock Holmes”. Use the lower() function to make comparisons case-insensitive: WHERE lower(B) = 'fred holmes'.

Can you use QUERY WHERE with multiple conditions?

Yes. Use AND to require all conditions: WHERE C = 'Baker Street' AND F > 300. Use OR to match any condition: WHERE C = 'Whitehall' OR C = 'Scotland Yard'. You can combine AND and OR with parentheses.

How do you filter text that contains a word in QUERY?

Use the contains keyword: WHERE B contains 'Holmes' returns every row where column B includes the word Holmes anywhere in the cell. It is case-sensitive.

How do you use QUERY WHERE with dates?

Use the date keyword with the format YYYY-MM-DD in single quotes. For example: WHERE A > date '2026-01-15' returns rows where the date in column A is after January 15, 2026.