Google Sheets QUERY for Bank Transactions: Examples

TL;DR Once your bank statement is in Google Sheets as a clean table (one row per transaction, with a real date, a description, a signed amount and a category), the QUERY function can answer most questions in one formula. Filter by date range, total spending by category, build a month-by-category summary with pivot, list your biggest merchants, or find every transaction that matches a keyword. The formulas below assume columns A to E are Date, Account, Description, Amount and Category, with headers in row 1. Two things trip people up: dates in a where clause need the date 'YYYY-MM-DD' syntax, and month() counts from 0, so January is 0.

Google Sheets is a great home for bank transactions: it's free, it's shareable with a partner or bookkeeper, and it has one function that makes analysis much faster than stacking up SUMIFS formulas. QUERY lets you write something close to a database query against a range of cells. Instead of building a pivot table by hand, you write one formula and it updates whenever new transactions are added.

This guide is a practical collection of QUERY formulas for bank data, each explained, with the common mistakes and how to fix them. It assumes you already have transactions in a sheet; if not, start with our guide to getting a bank statement PDF into Google Sheets.

Set up the data first

QUERY is fussy about data types. Most problems people hit with it are really data problems. Before writing any formulas, make sure your transaction sheet, here called Tx, looks like this:

The figures are illustrative.

Rules that matter:

• Dates must be real dates, not text. Select the column and use Format, then Number, then Date. If they stay left-aligned, they're text; see our guide to fixing bank statement dates, which applies to Sheets as well.
• Amounts must be numbers. No currency symbols, no text. Money in positive, money out negative.
• One data type per column. QUERY decides each column's type from the majority of its values. If a column is mostly numbers with a few text cells, the text cells are treated as empty. This is the most common cause of missing rows in results.
• No blank rows or subtotals in the middle of the data.
• One header row.

If your bank statement has separate debit and credit columns, combine them into one signed Amount first. Our guide to separating debit and credit columns shows the formula in both directions.

To get PDF statements into this shape quickly, SheetStatement converts them to CSV with one row per transaction and a signed amount, after checking the statement's balances. Import the CSV into Sheets with File, then Import. The free plan includes 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans cover more.

The basic shape of QUERY

=QUERY(data, query, headers)

• data is the range, for example Tx!A:E.
• query is a text string in Google's query language.
• headers is the number of header rows, usually 1.

Columns are referred to by letter (A, B, C) when the data is a plain range. Put each formula on a separate sheet, such as Reports, so results don't overlap your data.

1. Show all transactions in a date range

=QUERY(Tx!A:E, "select A, C, D, E where A >= date '2026-03-01' and A <= date '2026-03-31' order by A", 1)

Dates inside the query string must use the format date 'YYYY-MM-DD'. Writing A >= '2026-03-01' or A >= 3/1/2026 won't work.

To make the dates come from cells, so you can change them without editing the formula, build the string with TEXT:

=QUERY(Tx!A:E, "select A, C, D, E where A >= date '" & TEXT(H1, "yyyy-mm-dd") & "' and A <= date '" & TEXT(H2, "yyyy-mm-dd") & "' order by A", 1)

where H1 holds the start date and H2 the end date.

2. Total spending by category

=QUERY(Tx!A:E, "select E, sum(D) where D < 0 and E <> 'Transfer' group by E order by sum(D) label sum(D) 'Total'", 1)

What this does:

• where D < 0 keeps only money out.
• E <> 'Transfer' excludes transfers between your own accounts, so card payments aren't counted twice.
• group by E totals by category.
• order by sum(D) sorts with the biggest spending first (the most negative number).
• label renames the column header.

If you'd rather see spending as positive numbers, multiply inside the query:

=QUERY(Tx!A:E, "select E, -1*sum(D) where D < 0 and E <> 'Transfer' group by E order by -1*sum(D) desc label -1*sum(D) 'Spent'", 1)

3. Month-by-category summary with pivot

This is the formula most people want: categories down the side, months across the top.

=QUERY(Tx!A:E, "select E, sum(D) where E <> 'Transfer' and E <> 'Income' group by E pivot month(A)+1", 1)

Notice month(A)+1. In the query language, month() returns 0 for January through 11 for December. Adding 1 gives the familiar 1 to 12.

If your data spans more than one year, include the year as well, otherwise January of two different years lands in the same column:

=QUERY(Tx!A:E, "select E, sum(D) where E <> 'Transfer' group by E pivot year(A), month(A)+1", 1)

Pivot columns appear in sorted order, so months line up correctly.

If you want to see how this compares with Excel, our guide to bank statement pivot tables in Excel builds the same summary there.

4. Monthly totals for income, spending and net

=QUERY(Tx!A:E, "select year(A), month(A)+1, sum(D) where E <> 'Transfer' group by year(A), month(A)+1 order by year(A), month(A)+1 label year(A) 'Year', month(A)+1 'Month', sum(D) 'Net'", 1)

That gives net cash flow per month (income minus spending, excluding transfers). For separate income and spending columns, run two queries side by side, one with where D > 0 and one with where D < 0, or use a pivot on a helper column that labels each row "In" or "Out".

For a more formal version of this, see building a cash flow statement from bank statements.

5. Top merchants

=QUERY(Tx!A:E, "select C, count(D), sum(D) where D < 0 group by C order by sum(D) limit 15 label count(D) 'Transactions', sum(D) 'Total'", 1)

This groups by description, counts how many times each appears, totals the amounts and shows the top fifteen. Bank descriptions often include dates or reference numbers that make the same merchant look different each time. If that's the case, add a cleaned Merchant column to your data first and group by that instead. Our guide to cleaning bank statement data shows how to strip reference numbers.

6. Find transactions by keyword

=QUERY(Tx!A:E, "select A, C, D where lower(C) contains 'subscription' order by A", 1)

lower() makes the search case-insensitive. Other text operators:

• starts with 'AMZN'
• ends with 'LTD'
• like '%COFFEE%', where % matches any characters
• matches '.*(NETFLIX|SPOTIFY|DISNEY).*', a regular expression

To search for a word typed into a cell, say H1:

=QUERY(Tx!A:E, "select A, C, D where lower(C) contains '" & LOWER(H1) & "' order by A", 1)

7. Large transactions

=QUERY(Tx!A:E, "select A, B, C, D where D <= -500 or D >= 500 order by A", 1)

Useful when preparing for a mortgage or rental application, where you may be asked to explain large deposits, or when reviewing a client's account. See bank statements for a mortgage application for what lenders commonly look at.

8. Uncategorized rows

=QUERY(Tx!A:E, "select A, C, D where E is null or E = 'Uncategorized' order by A", 1)

Keep this on a "To do" sheet. As long as it's empty, every transaction has a category.

9. Filter by account

=QUERY(Tx!A:E, "select A, C, D where B = 'Card' and A >= date '2026-01-01' order by A desc", 1)

Combine with any of the above to report on a single account.

10. Pull from another spreadsheet

If your transactions live in another file, combine QUERY with IMPORTRANGE. When the data comes from IMPORTRANGE (or any array rather than a plain range), refer to columns as Col1, Col2 and so on instead of letters:

=QUERY(IMPORTRANGE("spreadsheet_url", "Tx!A:E"), "select Col5, sum(Col4) where Col4 < 0 group by Col5", 1)

The first time, you'll need to allow access between the two files.

11. Count transactions per category

=QUERY(Tx!A:E, "select E, count(D), avg(D) where D < 0 group by E order by count(D) desc label count(D) 'Count', avg(D) 'Average'", 1)

Counting is surprisingly useful. A category with a modest total but a very high count, such as dozens of small coffee or snack purchases, is often where habits hide. The average tells you the typical transaction size, which helps when you're setting a weekly allowance rather than a monthly one.

12. Compare two periods

To compare this quarter with the same quarter last year, run the same category query twice with different date filters and put the results side by side. Then use a lookup to line them up, because the categories may appear in a different order:

=IFERROR(VLOOKUP(A2, LastYear!A:B, 2, FALSE), 0)

where column A holds this period's categories and LastYear holds the earlier query output. A difference column shows which categories grew. This is a quick way to answer "why does it feel like we're spending more?" with actual numbers.

When to use QUERY, SUMIFS or a pivot table

All three can produce totals by category or month. They suit different jobs:

• QUERY is best when you want a whole report from one formula that updates automatically, including filtering and sorting. It's compact but harder to read for people who don't know the syntax.
• SUMIFS is best for a fixed layout, such as a budget sheet where every category has a row and you want one number in each cell. It's easier for others to understand and audit.
• Pivot tables are best for exploring: dragging fields around to look at the data from different angles. They're less convenient for a fixed monthly report.

Many people use all three: a pivot table to explore, QUERY for the review sheet, and SUMIFS for the budget.

Keep your queries maintainable

A few habits make QUERY sheets easier to live with:

• Use whole columns like Tx!A:E so new rows are included automatically.
• Keep category names consistent. "Eating out" and "Eating Out" are different groups. A drop-down list using data validation on the Category column prevents this.
• Write a short note above each formula saying what it shows. You'll thank yourself in six months.
• Keep parameters in cells, such as dates and thresholds, rather than inside the formula text.

Formatting results

QUERY output follows the source formatting in some cases and not others. You can format numbers inside the query:

=QUERY(Tx!A:E, "select E, sum(D) group by E format sum(D) '#,##0.00'", 1)

Or simply apply number formatting to the result columns. Formatting is kept as long as the output stays the same size.

A worked example: a monthly review sheet

A household wants a single "Review" sheet they can open on the first of each month.

1. Data. They keep all transactions from checking, savings and two credit cards in Tx, appending each new month's converted statements.
2. Month selector. Cell B1 holds the first day of the month to review. B2 has =EOMONTH(B1, 0) for the last day.
3. Spending by category for that month:

=QUERY(Tx!A:E, "select E, -1*sum(D) where D < 0 and E <> 'Transfer' and A >= date '" & TEXT(B1,"yyyy-mm-dd") & "' and A <= date '" & TEXT(B2,"yyyy-mm-dd") & "' group by E order by -1*sum(D) desc label -1*sum(D) 'Spent'", 1)

4. Top merchants for the same month, with the same date filter.
5. Large items over a threshold in cell B3.
6. Uncategorized rows, so nothing slips through.

Changing B1 to a different month updates the whole sheet. Next to the spending-by-category output, they add their budget figures and a difference column. That's their entire budgeting system; our guide to creating a budget from bank statements explains how they set the budget figures.

Common errors and fixes

"Unable to parse query string". A syntax problem, usually a missing quote, a typo in a column letter, or a date not written as date 'YYYY-MM-DD'. Build the string in a separate cell first so you can see exactly what's being sent.

Results are missing rows. A column has mixed data types. Text amounts or text dates are being treated as empty. Clean the source data.

January shows up as 0. That's month() being zero-based. Use month(A)+1.

Totals include transfers. Add E <> 'Transfer' to the where clause, and make sure transfers are tagged consistently.

"Array result was not expanded". Something is in the way of the output. Clear the cells below and to the right of the formula.

Headers are wrong or duplicated. Set the third argument to the number of header rows in your data, usually 1.

Sharing with a partner or bookkeeper

One advantage of Sheets is that several people can look at the same numbers. If you share your transaction file, consider giving view-only access to the raw Tx sheet and edit access only to the categories, so nobody accidentally changes an amount. Protected ranges in Sheets make this straightforward. Because QUERY reports recalculate on their own, everyone sees the same up-to-date summary without anyone exporting or emailing files.

Reconciliation in Sheets

QUERY is for analysis. For proving that your sheet matches the bank, use a reconciliation layout instead. Our guide to reconciling a bank statement in Google Sheets covers that, and the free balance checker is a quick way to check a statement's totals.

Get your statements into Sheets

Upload a bank statement PDF to SheetStatement, check the rows and the balance check, and download a CSV that's ready for QUERY. The free plan includes 3 pages a month, enough to try it on a real statement.

FAQ

How do I filter by date in Google Sheets QUERY?

Write dates inside the query as date 'YYYY-MM-DD', for example where A >= date '2026-01-01'. To use a cell, build the string with TEXT(cell, "yyyy-mm-dd").

Why does month() return 0 for January?

The query language counts months from 0. Use month(A)+1 to get 1 to 12.

How do I total spending by category?

Use select E, sum(D) where D < 0 group by E, where E is the category and D is the amount. Exclude transfers with and E <> 'Transfer'.

Why are some rows missing from my QUERY results?

Usually a column has mixed data types, such as some amounts stored as text. QUERY treats the minority type as empty. Make each column a single type.

Can QUERY make a pivot table?

Yes. Add pivot to the query, for example group by E pivot month(A)+1, to get categories down the side and months across the top.

Can I use QUERY on data from another file?

Yes, wrap IMPORTRANGE inside QUERY and refer to columns as Col1, Col2 and so on.

Is it safe to upload my statements?

See the security page for how SheetStatement processes and stores files, and review any service's policy before uploading financial documents.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.