Prompt Almanac

Create intermediate Works with: Any AI

Google Sheets QUERY Function Writer With Worked Examples

Structured brief Has example

Write and explain QUERY formulas for filtering, grouping and sorting.

Open in ChatGPTOpen in Claude

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Rate this prompt

Loading ratings…

Help me write QUERY formulas in Google Sheets. My data range and column headings, with sample rows: [DATA_SAMPLE]. The results I want: [DESIRED_RESULTS]. Whether the headings are in the first row: [HEADER_ROWS].

Present the formulas in a table with Goal, Formula, Result on sample and Notes, with at least one example of each clause I use (select, where, group by, order by, label, format, limit). For each, break down the data range, the query string in quotes, and each clause. Explain that the query string uses column letters, not headings, how to refer to a cell value inside the query string by joining text with the ampersand, how to join text and date values into a where clause, and how to handle text with apostrophes. Worked example on invented data in A1:C6 with headings Name, Region, Sales: =QUERY(A1:C6,"select B, sum(C) where C > 0 group by B") returns each region with its total [VERIFY].

List typical errors (a text number in a numeric column, wrong quotes around text, dates not recognised, missing header argument) and troubleshoot an empty result, shifted columns, and numbers treated as text. Test by starting with select * and adding clauses one at a time. Keep the query readable by holding the query text in a cell. Show combining QUERY with IFERROR and with a dropdown cell that chooses the region, protect the results range, and offer a formula-only alternative for simple cases. Mark clause behaviour you are unsure of [VERIFY].

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

See an example result (fictional data)
Invented data in A1:C6 with headings Name, Region, Sales. Formula: =QUERY(A1:C6,"select B, sum(C) where C > 0 group by B order by sum(C) desc label sum(C) 'Total sales'",1). Sample result: North 540, South 310.
Check: North rows 300 + 240 = 540. Second example: =QUERY(A1:C6,"select A, C where B = 'North' order by C desc",1) returns the North rows sorted by sales. Third example using a cell: =QUERY(A1:C6,"select A, C where B = '"&E1&"'",1) where E1 holds the region chosen from a dropdown. If the result is empty, check that the region text matches exactly.

Illustrative only, made-up data. Your results will differ and must be reviewed.

Related prompts