In Google Sheets, a single QUERY formula with the pivot keyword can turn a flat list of transactions into a merchant-by-month table. You add one helper column for the month and write the formula once; the table rebuilds itself whenever the data changes. Excel does not have QUERY, so Excel users should use a pivot table instead.
Who is this for?
A self-employed carpenter in Winnipeg buys from three lumber yards, a tool supplier and a fuel station. At the end of each quarter he wants to see, supplier by supplier, how spending moved month to month. His accountant asks for a list of merchants and totals, and he would rather produce it in a minute than an hour.
What does the data need to look like?
- Column A: transaction date, as a real date.
- Column B: merchant, with one consistent spelling each.
- Column C: amount in Canadian dollars, as a plain number.
- Column D: month, using =TEXT(A2,"yyyy-mm") filled down. Put a header in D1 such as Month.
- Row 1 holds headers: Date, Merchant, Amount, Month.
What is the formula?
=QUERY($A$1:$D$500, "select B, sum(C) where B is not null group by B pivot D", 1)
Read it in pieces. "select B, sum(C)" asks for the merchant and the sum of the amounts. "where B is not null" skips blank rows. "group by B" gives one row per merchant. "pivot D" turns each distinct month into a column. The final 1 says there is one header row. Paste it into an empty cell with plenty of room to the right and below.
What comes out?
| Merchant | 2026-07 | 2026-08 | 2026-09 |
|---|---|---|---|
| Lumber yard A | C$1,240.00 | C$980.50 | C$1,415.25 |
| Lumber yard B | C$310.00 | none | C$275.40 |
| Tool supplier | C$129.99 | C$64.50 | none |
| Fuel station | C$210.30 | C$198.75 | C$225.10 |
Cells marked none mean no spending that month (the formula itself leaves them blank). July adds to 1,240.00 + 310.00 + 129.99 + 210.30 = C$1,890.29, August to 980.50 + 64.50 + 198.75 = C$1,243.75 and September to 1,415.25 + 275.40 + 225.10 = C$1,915.75. All numbers are made up. As a check, =SUM($C$2:$C$500) over the raw data should equal the sum of every number in the pivot's body.
Why does it show an error or nothing?
- "Unable to parse query string": usually a stray quote. Keep the whole query in straight double quotes, and any text values inside it in single quotes.
- "Result would overwrite data": something is sitting in the cells the table needs. Clear them.
- A merchant appears twice: the spellings differ, often by a space or capital letter. Clean the source with =TRIM(B2).
- Months show as dates or numbers: the TEXT helper was not used, or the cells were reformatted. Re-enter the helper formula.
Is there a tool with this built in?
An expense tracker with a report builder can do the same grouping without formulas. SpendixAI offers filtering, grouping, saved reports and CSV export, and it is built around India and INR, so we would not suggest it for a Canadian contractor's books; I am using it as an example of what a report builder should offer.
Saving the report so it does not break
A QUERY result is live, so someone sorting or typing over the output can damage it. Put the formula on its own tab named Report, keep the raw data on another, and protect the Report tab from edits under the Data menu. When you need a static copy for the accountant, choose File, Download as CSV on the Report tab, or copy the table and paste as values only into a new sheet. That freezes the figures at the time you sent them, which is useful if the source data changes later.
What to give your accountant
Send the pivot as a CSV, the raw export and a line stating which accounts and dates are included. Ask whether they want amounts before or after any sales tax; we cannot answer that for you, and the answer depends on your situation.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

