How It Works

QUERY uses a SQL-like syntax to manipulate data in Google Sheets. It’s incredibly powerful for analyzing transaction logs, building summaries, and creating dynamic reports.

Syntax

=QUERY(data, query, [headers])

data: Range to query
query: SQL-like query string
headers: Number of header rows (optional, default 1)

Basic Examples

Select Specific Columns

=QUERY(A:E, "SELECT A, B, E")

Returns only Date, Description, and Amount columns.

Filter by Condition

=QUERY(A:E, "SELECT * WHERE E > 100")

Returns all rows where Amount > $100.

Sort Results

=QUERY(A:E, "SELECT * ORDER BY E DESC")

Returns all rows sorted by Amount, highest first.

Transaction Analysis

Sample Data:

Date	Description	Category	Account	Amount
1/5	Groceries	Food	Checking	-85.00
1/6	Paycheck	Income	Checking	3500.00
1/7	Electric	Utilities	Checking	-125.00
1/8	Restaurant	Food	Credit	-45.00

Sum by Category

=QUERY(A:E, "SELECT C, SUM(E) WHERE E < 0 GROUP BY C LABEL SUM(E) 'Total'")

Category	Total
Food	-130.00
Utilities	-125.00

Monthly Spending

=QUERY(A:E, "SELECT MONTH(A)+1, SUM(E) WHERE E < 0 GROUP BY MONTH(A)+1 LABEL MONTH(A)+1 'Month', SUM(E) 'Spending'")

Filter by Date Range

=QUERY(A:E, "SELECT * WHERE A >= DATE '2024-01-01' AND A <= DATE '2024-01-31'")

Top 10 Expenses

=QUERY(A:E, "SELECT * WHERE E < 0 ORDER BY E LIMIT 10")

Building Dashboards

Spending by Category This Month

=QUERY(Transactions!A:E,
  "SELECT C, SUM(E)
   WHERE MONTH(A)+1 = " & MONTH(TODAY()) & "
   AND YEAR(A) = " & YEAR(TODAY()) & "
   AND E < 0
   GROUP BY C
   ORDER BY SUM(E)
   LABEL SUM(E) 'Amount'")

Average Transaction Size by Category

=QUERY(A:E, "SELECT C, AVG(E), COUNT(E) WHERE E < 0 GROUP BY C")

Income vs Expenses Summary

=QUERY(A:E, "SELECT SUM(E) WHERE E > 0 LABEL SUM(E) 'Income'")
=QUERY(A:E, "SELECT SUM(E) WHERE E < 0 LABEL SUM(E) 'Expenses'")

Advanced Filtering

Multiple Conditions (AND)

=QUERY(A:E, "SELECT * WHERE C = 'Food' AND E < -50")

Multiple Conditions (OR)

=QUERY(A:E, "SELECT * WHERE C = 'Food' OR C = 'Dining'")

Contains Text

=QUERY(A:E, "SELECT * WHERE B CONTAINS 'Amazon'")

Starts With

=QUERY(A:E, "SELECT * WHERE B STARTS WITH 'AMAZON'")

Not Equal

=QUERY(A:E, "SELECT * WHERE C <> 'Income'")

NULL Handling

=QUERY(A:E, "SELECT * WHERE C IS NOT NULL")

Aggregation Functions

Function	Description
SUM(col)	Total of values
AVG(col)	Average
COUNT(col)	Number of rows
MAX(col)	Maximum value
MIN(col)	Minimum value

Example: Complete Summary

=QUERY(A:E, "SELECT C, COUNT(A), SUM(E), AVG(E), MIN(E), MAX(E) WHERE E < 0 GROUP BY C")

Dynamic Queries

Reference cells in your query:

Category from Cell

=QUERY(A:E, "SELECT * WHERE C = '"&G1&"'")

Where G1 contains the category name.

Date from Cell

=QUERY(A:E, "SELECT * WHERE A >= DATE '"&TEXT(G1,"yyyy-mm-dd")&"'")

Amount Threshold from Cell

=QUERY(A:E, "SELECT * WHERE E < "&G1)

Pivot-Style Reports

Category by Month Matrix

=QUERY(A:E,
  "SELECT C, SUM(E)
   PIVOT MONTH(A)+1")

Creates a table with categories as rows, months as columns.

Combining QUERY with Other Functions

Query + IMPORTRANGE

Pull data from another spreadsheet:

=QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A:E"), "SELECT * WHERE Col5 < 0")

Query + Array

Query calculated results:

=QUERY({A:C, ARRAYFORMULA(B:B*C:C)}, "SELECT Col1, Col4 WHERE Col4 > 100")

Common Query Patterns

Transactions This Week

=QUERY(A:E, "SELECT * WHERE A >= DATE '"&TEXT(TODAY()-WEEKDAY(TODAY(),2)+1,"yyyy-mm-dd")&"'")

Year-to-Date Spending

=QUERY(A:E, "SELECT SUM(E) WHERE A >= DATE '"&YEAR(TODAY())&"-01-01' AND E < 0")

Recurring Transactions

=QUERY(A:E, "SELECT B, COUNT(B), AVG(E) WHERE E < 0 GROUP BY B HAVING COUNT(B) > 1 ORDER BY COUNT(B) DESC")

Pro Tips

Column letters in query match your data range, not the sheet (Col1, Col2, etc. if using arrays)
Use LABEL to rename output columns:
```
LABEL SUM(E) 'Total Spending'
```
FORMAT for display:
```
FORMAT E '$#,##0.00'
```
Headers parameter - set to 0 if your data has no headers
Debug complex queries - build piece by piece, testing each addition

Common Errors

#VALUE!: Query syntax error - check quotes, column references
#REF!: Referenced range doesn’t exist
Wrong column: Remember QUERY uses Col1, Col2 notation with arrays
Date issues: Use DATE ‘YYYY-MM-DD’ format in queries
Text matching: Text comparisons are case-sensitive