Google Sheets QUERY for Spending Reports (Examples)
Copy-paste Google Sheets QUERY formulas for spending reports: sum by category, this month, totals by month, a category-by-month pivot and top 10 purchases.
In short
To sum spending by category in Google Sheets, use QUERY with group by: =QUERY(Expenses!A:D, "select B, sum(C) where B is not null group by B label sum(C) 'Total'", 1). Column B is the category and C the amount. Add a where clause on the date to report one month, or a pivot clause to spread months across the top.
To sum spending by category in Google Sheets, use QUERY with a group by clause: =QUERY(Expenses!A:D, "select B, sum(C) where B is not null group by B label sum(C) 'Total'", 1). Column B holds the category and C the amount. Add a where test on the date to report one month. Below are six ready-made spending reports to copy.
Every example assumes an Expenses tab laid out like our free Google Sheets expense tracker template, with headers in row 1:
| Column | A | B | C | D |
|---|---|---|---|---|
| Header | Date | Category | Amount | Notes |
| Example | 03/11/2026 | Food | 64.20 | Groceries |
If your columns differ, swap the letters inside the query. Put each formula in the top-left cell of an empty area on another tab, because QUERY fills as many rows and columns as the result needs.
The 6 spending reports at a glance
| # | Report | Key clauses |
|---|---|---|
| 1 | Total spend by category | group by B |
| 2 | This month by category | where A >= date '…' |
| 3 | Total spend by month | group by year(A), month(A)+1 |
| 4 | Category-by-month grid | pivot month(A)+1 |
| 5 | Top 10 purchases this month | order by C desc limit 10 |
| 6 | Category stats: entries, average, largest | count(), avg(), max() |
QUERY basics in 2 minutes
QUERY takes three arguments:
=QUERY(data, "query", headers)
- data is the range to read:
Expenses!A:D. - query is a text string in Google's query language. Columns are referred to by their letter: A, B, C.
- headers is the number of header rows. Use
1so QUERY reads "Date", "Category" and so on as labels, not data.
The query string is built from clauses, and they must appear in this order. Skip any you don't need:
selectthe columns to show, such asB, sum(C)whereto filter rows, such asC > 50group byto combine rows that share a valuepivotto turn one column's values into new columnsorder byto sort, withascordesclimitto keep the first n rowslabelto rename output columnsformatto set number formats
The where clause handles most of the filtering a spending report needs:
| Test | Example |
|---|---|
| Equals text (case-sensitive) | where B = 'Food' |
| Any capitalisation | where lower(B) = 'food' |
| Number comparison | where C >= 100 |
| A fixed date | where A >= date '2026-11-01' |
| Text inside Notes | where lower(D) contains 'amazon' |
| Skip blank rows | where A is not null |
| Combine tests | where B = 'Food' and C > 20 |
Three rules trip up nearly everyone:
- Text goes in single quotes inside the double-quoted query string:
"where B = 'Food'". - Dates use the keyword
dateand the format yyyy-mm-dd, whatever your spreadsheet's locale shows. - Every column in
selectmust be grouped or aggregated.select B, sum(C)needsgroup by B.
How do you use Google Sheets QUERY to sum by category?
1. Total spend by category
This is the core report: one row per category, with the total spent, largest first.
=QUERY(Expenses!A:D,
"select B, sum(C)
where B is not null
group by B
order by sum(C) desc
label sum(C) 'Total'", 1)
The result looks like this:
| Category | Total |
|---|---|
| Rent | 3,600.00 |
| Food | 1,433.15 |
| Utilities | 418.40 |
| Transport | 401.50 |
where B is not null drops rows with no category, so stray blank rows don't add an empty line to the report. Line breaks inside the formula are optional. Press Ctrl+Enter (⌘+Enter on a Mac) while editing to add them, and QUERY ignores them.
2. This month by category
To make the Google Sheets QUERY sum by category for the current month only, build the date tests from TODAY(). QUERY can't read a Sheets date directly inside the string, so TEXT() turns it into the yyyy-mm-dd form QUERY expects:
=QUERY(Expenses!A:D,
"select B, sum(C)
where A >= date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"'
and A < date '"&TEXT(EOMONTH(TODAY(),0)+1,"yyyy-mm-dd")&"'
group by B
order by sum(C) desc
label sum(C) 'This month'", 1)
EOMONTH(TODAY(),-1)+1 is the 1st of this month and EOMONTH(TODAY(),0)+1 is the 1st of next month, so the report resets itself on the 1st. "Before next month's 1st" also catches timestamps late on the last day, which a "less than or equal to the last day" test would drop. The SUMIFS by month guide explains these date windows in more detail.
To report a month you choose instead, put the 1st of that month in a cell such as F1 and swap the two TEXT() parts for TEXT(F1,"yyyy-mm-dd") and TEXT(EDATE(F1,1),"yyyy-mm-dd").
More spending reports with QUERY
3. Total spend by month
Group by the year and the month of the Date column to get one row per month:
=QUERY(Expenses!A:D,
"select year(A), month(A)+1, sum(C)
where A is not null
group by year(A), month(A)+1
order by year(A), month(A)+1
label year(A) 'Year', month(A)+1 'Month', sum(C) 'Total'", 1)
| Year | Month | Total |
|---|---|---|
| 2026 | 9 | 1,982.40 |
| 2026 | 10 | 2,104.75 |
| 2026 | 11 | 1,266.30 |
The +1 matters. QUERY numbers months from 0, so without it January shows as 0 and November as 10. Grouping by the year as well keeps November 2025 and November 2026 apart.
4. Category-by-month grid with pivot
pivot turns each month into its own column, giving the categories-down, months-across table most budgets want:
=QUERY(Expenses!A:D,
"select B, sum(C)
where B is not null and year(A) = "&YEAR(TODAY())&"
group by B
pivot month(A)+1", 1)
| Category | 9 | 10 | 11 |
|---|---|---|---|
| Food | 398.15 | 412.80 | 221.40 |
| Rent | 1,200.00 | 1,200.00 | 1,200.00 |
| Transport | 110.50 | 96.00 | 48.00 |
The year(A) = "&YEAR(TODAY())&" part limits the grid to this year. A column appears only for months that have data, and a category with no spending that month shows a blank cell rather than 0. If you need a fixed twelve-month layout with zeros, the SUMIFS grid in our budget dashboard guide is the better tool.
5. Top 10 purchases this month
Sorting the raw rows by amount, with no grouping, lists your biggest purchases:
=QUERY(Expenses!A:D,
"select A, B, C, D
where A >= date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"'
and A < date '"&TEXT(EOMONTH(TODAY(),0)+1,"yyyy-mm-dd")&"'
order by C desc
limit 10", 1)
Leave out the two date lines and use where C is not null for the top 10 of all time. To skip fixed costs and see where the flexible money went, add and B <> 'Rent'.
6. Category stats: entries, average and largest
One query can return several aggregates per category, which is a quick way to spot a category where the count is fine but the average is creeping up:
=QUERY(Expenses!A:D,
"select B, count(C), sum(C), avg(C), max(C)
where B is not null
group by B
order by sum(C) desc
label count(C) 'Entries', sum(C) 'Total', avg(C) 'Average', max(C) 'Largest'
format avg(C) '0.00'", 1)
| Category | Entries | Total | Average | Largest |
|---|---|---|---|---|
| Food | 31 | 1,433.15 | 46.23 | 142.60 |
| Transport | 18 | 401.50 | 22.31 | 64.00 |
label comes before format, as in the clause order above. Swap where B is not null for one of the date windows from example 2 to get this month's stats.
Pivot table vs QUERY: which should you use?
Both summarise the same rows. The difference is how you build them and what you can do with the result.
| Pivot table | QUERY | |
|---|---|---|
| How you build it | Drag fields in a side panel | Type a formula |
| Learning curve | Low | Medium |
| Group dates by month | Right-click a date → Create pivot date group | group by year(A), month(A)+1 |
| Follows a date in another cell | Filters only, set by hand | Yes, through TEXT() |
| Feeds a chart or another formula | Possible, but the layout moves | Yes, a fixed shape |
| Copy to another sheet | Rebuild it | Copy the formula text |
| Zeros for empty months | Blank cells | Blank cells (use SUMIFS for zeros) |
| Updates when rows are added | Yes, with an open range like A:D | Yes |
A pivot table (Insert → Pivot table) is the better tool for exploring: dragging Category into Rows and the date into Columns shows in seconds where the money went. QUERY is the better tool for a report you'll read every month, because it lives in a cell, responds to TODAY() or a month picker, and keeps the same shape for a chart. Many people use both: a pivot table to look around, and QUERY for the monthly report.
When a QUERY report goes wrong
Totals are short or rows are missing. QUERY gives each column one data type, the most common one, and treats the rest as empty. A handful of dates stored as text, or amounts pasted with a currency symbol, drop out with no error. Check suspect cells with =ISNUMBER(A2), then fix them with Format → Number and a retype, or DATEVALUE() and VALUE() in a helper column.
A category is split in two. "Food" and "food" are different to QUERY, and so are "Food" and "Food " with a trailing space. Use Data → Data cleanup → Trim whitespace, and give the Category column a dropdown. Our category dropdown guide shows how.
"Unable to parse query string". Usually a quote is missing or clauses are out of order. Check that each piece of text has single quotes, that the string is closed with a double quote before each &, and that label comes after order by and limit.
"Array result was not expanded". Something is in the way of the output. Clear the cells below and to the right of the formula.
A column error with IMPORTRANGE or {} arrays. When the data comes from IMPORTRANGE or an array like {Expenses!A:D}, refer to columns as Col1, Col2, Col3 instead of A, B, C.
Semicolons instead of commas. In locales that use a comma as the decimal separator, the formula's arguments are separated by semicolons: =QUERY(Expenses!A:D; "…"; 1). The query string inside stays the same.
Keep the data QUERY-ready from your phone
Every report here depends on clean columns: real dates in A, the same category spellings in B, and plain numbers in C. Rows typed into the Sheets app on a phone are where text dates and "food" versus "Food" usually creep in.
Voice Sheet reads your spreadsheet's columns and builds a phone form from them, with a date picker for Date and your dropdown options for Category, so every row arrives in the same shape as the rest of the sheet. The free plan includes that form and seven ready-made budget spreadsheets, and the in-app budget dashboard shows this month's spend per category without opening the sheet.
For the full set-up, from columns to dropdowns to sharing, see the complete guide to track expenses in Google Sheets.
Frequently asked questions
How do I sum by category with QUERY in Google Sheets?
Group the rows by the category column and sum the amount column: =QUERY(Expenses!A:D, "select B, sum(C) where B is not null group by B", 1). The last argument tells QUERY that row 1 holds headers. Add order by sum(C) desc to put the biggest category first, and a label clause to rename the total.
Why does QUERY return blank cells or miss some rows?
Usually the column holds mixed data types. QUERY gives each column one type, the most common one, and treats every other value as empty. A few text dates in a date column, or amounts typed with a currency symbol, simply disappear from the totals. Check with =ISNUMBER() and convert the odd cells to real dates or numbers.
Why does month() in QUERY return the wrong month?
QUERY numbers months from 0, so January is 0 and December is 11. That's why the examples here use month(A)+1, which gives the familiar 1 to 12. Use the same expression everywhere it appears: in select, group by, order by and label, or QUERY reports an error about the column not being grouped.
Is QUERY case-sensitive?
Yes. In QUERY, where B = 'food' doesn't match Food, unlike SUMIFS, which ignores case. Wrap the column in lower() to match any capitalisation: where lower(B) = 'food'. The same applies to contains, starts with and like. A category dropdown avoids the problem, because every entry is spelled the same way.
Should I use QUERY or a pivot table for spending reports?
Use a pivot table when you want to drag fields around and explore. Use QUERY when you want a fixed report that sits in a cell, follows a date in another cell, and feeds a chart or another formula. Both read the same expense rows, and many budgets use a pivot table to explore and QUERY for the monthly report.
