Google Sheets

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.

By Waseem Gul8 min read

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:

ColumnABCD
HeaderDateCategoryAmountNotes
Example03/11/2026Food64.20Groceries

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

#ReportKey clauses
1Total spend by categorygroup by B
2This month by categorywhere A >= date '…'
3Total spend by monthgroup by year(A), month(A)+1
4Category-by-month gridpivot month(A)+1
5Top 10 purchases this monthorder by C desc limit 10
6Category stats: entries, average, largestcount(), 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 1 so 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:

  1. select the columns to show, such as B, sum(C)
  2. where to filter rows, such as C > 50
  3. group by to combine rows that share a value
  4. pivot to turn one column's values into new columns
  5. order by to sort, with asc or desc
  6. limit to keep the first n rows
  7. label to rename output columns
  8. format to set number formats

The where clause handles most of the filtering a spending report needs:

TestExample
Equals text (case-sensitive)where B = 'Food'
Any capitalisationwhere lower(B) = 'food'
Number comparisonwhere C >= 100
A fixed datewhere A >= date '2026-11-01'
Text inside Noteswhere lower(D) contains 'amazon'
Skip blank rowswhere A is not null
Combine testswhere 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 date and the format yyyy-mm-dd, whatever your spreadsheet's locale shows.
  • Every column in select must be grouped or aggregated. select B, sum(C) needs group 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:

CategoryTotal
Rent3,600.00
Food1,433.15
Utilities418.40
Transport401.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)
YearMonthTotal
202691,982.40
2026102,104.75
2026111,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)
Category91011
Food398.15412.80221.40
Rent1,200.001,200.001,200.00
Transport110.5096.0048.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)
CategoryEntriesTotalAverageLargest
Food311,433.1546.23142.60
Transport18401.5022.3164.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 tableQUERY
How you build itDrag fields in a side panelType a formula
Learning curveLowMedium
Group dates by monthRight-click a date → Create pivot date groupgroup by year(A), month(A)+1
Follows a date in another cellFilters only, set by handYes, through TEXT()
Feeds a chart or another formulaPossible, but the layout movesYes, a fixed shape
Copy to another sheetRebuild itCopy the formula text
Zeros for empty monthsBlank cellsBlank cells (use SUMIFS for zeros)
Updates when rows are addedYes, with an open range like A:DYes

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.

  • query function
  • google sheets formulas
  • spending report
  • spending by category
  • pivot table
  • expense tracker
New to Voice Sheet?

Say it, and it's in your Google Sheet.

Voice Sheet adds rows to your Google Sheets by voice, from a pasted list, a home screen widget or a form built from your columns. It works offline and never connects to your bank.

See everything Voice Sheet does →
Voice Sheet — say it, and it's in your Google Sheet

Keep reading

Add rows to this sheet from your phone

Voice Sheet turns your columns into a phone form and fills it from one spoken sentence. 3 voice entries a day are free.

Download on the App StoreGet it on Google Play