To group expenses by merchant and by month, you need three columns in your data: a date, a clean merchant name and an amount. Put them in a pivot table with merchant as rows, month as columns and the sum of amount as values, or build the same grid with SUMIFS. The hard part is not the pivot; it is making the merchant names consistent first.
What data do I need before I start?
Export or type your transactions into one flat table with a header row. You need at least these columns:
- Date, stored as a real date and not text.
- Merchant, one spelling per business.
- Amount, as a number with refunds as negatives.
- Month, a helper column you add yourself, so grouping does not depend on pivot features.
For the helper column, with the date in A2, use =TEXT(A2,"yyyy-mm") in Excel or Google Sheets. It yields 2026-03, which sorts correctly as text and avoids mixing March of different years.
How do I clean merchant names first?
Card statements print merchants in several forms: AMZN Mktp, Amazon web order, AMAZON PRIME. If you group before cleaning, one shop appears as three rows. Add a column called Merchant clean and map the variants. A small lookup table works well: raw name in one column, clean name in the next, then =IFERROR(VLOOKUP(B2,$H$2:$I$200,2,FALSE),B2) so unknown names pass through unchanged. Review the pass-through rows once a month and add them to the lookup.
How do I build the grid with a pivot table?
- Select the whole table and choose Insert, then PivotTable.
- Drag Merchant clean into Rows.
- Drag Month into Columns.
- Drag Amount into Values and confirm it says Sum of Amount.
- Turn on Grand Totals for rows and columns so you can check against your statement.
To see only the top spenders, sort the pivot by the Grand Total column, largest first. In Google Sheets the same layout lives under Insert, then Pivot table, with the same Rows, Columns and Values boxes.
Can I do it with formulas instead of a pivot?
Yes, and the result updates the moment data changes. List unique merchants down column A of a report sheet and months across row 1 as text like 2026-03. Then in B2 use:
=SUMIFS(Data!$C$2:$C$500, Data!$B$2:$B$500, $A2, Data!$D$2:$D$500, B$1)
Here C is Amount, B is Merchant clean and D is the Month helper. The dollar signs matter: they let you fill the formula across and down without the ranges sliding. In Excel 365 or Google Sheets, =UNIQUE(Data!$B$2:$B$500) can generate the merchant list for you; older Excel needs the list typed or built through a pivot.
A worked example with made-up numbers
| Merchant | 2026-01 | 2026-02 | 2026-03 | Total |
|---|---|---|---|---|
| Grocery Mart | $310.00 | $295.50 | $342.25 | $947.75 |
| Gas station | $88.00 | $74.00 | $96.40 | $258.40 |
| Streaming | $15.99 | $15.99 | $15.99 | $47.97 |
Check the arithmetic: 310.00 + 295.50 + 342.25 is 947.75. A merchant total that does not match its monthly cells usually means a name variant has slipped through. Add a Grand Total row, and compare it with the sum of the Amount column in the data sheet.
When does a spreadsheet stop being enough?
If you reclean names every month and the lookup table keeps growing, you are doing work an app can absorb. SpendixAI has a Report builder with filtering and grouping plus a CSV download, and it learns from your corrections. It is built around India and INR, so it suits that setup best; for dollar accounts, the spreadsheet above remains the practical route.
What if a merchant appears in two months only partly?
A subscription billed on the 30th can slip into the next month if the posting date differs from the purchase date. Pick one date column and use it for every row. Card statements usually list a posting date and sometimes a transaction date; mixing them is how a merchant total ends up one month off. Whichever you choose, write the choice at the top of the sheet so you do not switch halfway through the year.
Checklist for a report you can trust
- One clean spelling per merchant.
- Month helper column present and consistent.
- Refunds as negatives.
- Grand total equals the statement total.
- Ranges extended when you add rows, or the data kept in a Table so they expand by themselves.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

