Spreadsheets

QUERY with pivot: categories down, months across in Google Sheets

Build a category-by-month grid in Google Sheets with QUERY and its pivot clause, using a helper month column instead of a pivot table.

Editorial illustration for QUERY with pivot: categories down, months across in Google Sheets

Add a helper column that turns each date into a label like 2026-04, then run =QUERY(A1:F,"select C, sum(E) where C is not null group by C pivot F",1). You get categories down the side, months across the top and a total in each cell, and it updates itself when you paste new rows. No pivot table needed.

Why a helper month column?

QUERY has a month() function, but it counts months from zero, so January is 0 and December is 11, and it ignores the year. A text label such as 2026-04 sorts in the right order, separates one year from the next, and is easy to read. Say your date is in A, merchant in B, category in C, account in D and amount in E. In F1 type Month, then in F2 put:

=ARRAYFORMULA(IF(A2:A="","",TEXT(A2:A,"yyyy-mm")))

The blank guard keeps unused rows empty. A date such as 14 April 2026 becomes 2026-04.

What does the pivot formula do?

With the helper in place, the formula reads:

=QUERY(A1:F,"select C, sum(E) where C is not null group by C pivot F",1)

ClauseJob
select C, sum(E)Row label is the category; the value is the total of amount
where C is not nullLeaves out rows with no category
group by COne row per category
pivot FOne column per distinct value in the month column

The month labels become the column headings, in alphabetical order, which is also chronological with the yyyy-mm style. Months and categories with no spending leave the cell blank, not zero.

A small hypothetical result

Category2026-042026-052026-06
Groceries4,0903,8604,420
Fuel1,9502,1001,800
Rent18,00018,00018,000

Read across to see how a category moves from month to month. Read down a column to see where one month's money went. The numbers above are made up purely to show the layout.

How do I restrict it to a financial year?

Add a condition on the month label. Text labels compare alphabetically, which works because of the year-first format. For April 2025 to March 2026 (hypothetical):

=QUERY(A1:F,"select C, sum(E) where C is not null and F >= '2025-04' and F <= '2026-03' group by C pivot F",1)

Move the two labels as the year changes, or read them from cells with the string-joining pattern: ...and F >= '"&H1&"' and F <= '"&H2&"' group by...

What are the limits of this approach?

  • You cannot add a row total inside QUERY; put =SUM(B2:M2) beside the grid if you need it
  • A month with no transactions has no column at all, so the grid can skip a month
  • Sorting with order by on pivot output is awkward; sort the source or use SORT around the whole formula
  • If dates are text, TEXT returns the text unchanged and the labels will look odd; check with ISNUMBER first

Can I show months in the Indian financial-year order?

The pivot orders its columns alphabetically by label, and a year-first label such as 2026-04 makes that the same as calendar order. Restrict the where clause to April of one year through March of the next, as shown above, and you get twelve columns that run in financial-year order. A friendlier label such as Apr 2026 would sort alphabetically, putting Apr before Aug before Dec, so keep the year-first label for the query and show friendlier names in a heading row if you like.

Setup checklist

  1. Check column A holds real dates
  2. Add the Month helper with ARRAYFORMULA
  3. Run the pivot query with header count 1
  4. Compare the grid total with =SUM(E2:E)
  5. Add a row-total column if you want one

Wherever you track your spending, this grid answers one specific question. SpendixAI's report library groups spending by question, so you may find a ready-made report before you need to build a grid.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports