To see spending grouped by merchant down the side and months across the top, the quickest route is a pivot table: merchant in rows, month in columns, amount as the value. If you prefer formulas, a SUMIFS grid does the same and updates as you add rows. Both work in Excel and Google Sheets.
For a gig worker this view answers a practical question: which petrol pump, workshop or recharge shop is costing the most, and is it growing.
What does your sheet need before you start?
- One row per transaction, with no blank rows in the middle.
- A date column that is a real date, not text. Click a cell; if it shows as left-aligned text, convert it.
- A merchant column with consistent names. Two spellings of the same pump will become two rows.
- An amount column with numbers only, no rupee symbol typed in as text.
Spending ten minutes tidying names saves you an hour of wrong answers.
Method one: a pivot table
- Select your data, including the header row.
- Insert a pivot table, either in a new sheet or the same one.
- Put Merchant in Rows.
- Put the date in Columns and group it by month. In Excel, right-click a date and choose Group, then Months. In Google Sheets, use the date group option in the column settings.
- Put Amount in Values and set it to Sum.
Pivot tables do not always refresh on their own. After adding rows, refresh the pivot, or it will show old totals.
Method two: a SUMIFS grid
Say dates are in A2:A500, merchants in B2:B500 and amounts in C2:C500. List merchants down column E from E2. Put the first day of each month across row 1 from F1, so F1 holds 1 October 2026, G1 holds 1 November 2026, and so on. In F2 enter:
- =SUMIFS($C$2:$C$500,$B$2:$B$500,$E2,$A$2:$A$500,">="&F$1,$A$2:$A$500,"<"&EDATE(F$1,1))
Fill it right and down. The dollar signs lock the data ranges and let the merchant and month references move. EDATE moves a date forward by one month, so each cell sums only that merchant in that month. Argument order matters: sum range first, then each range and its condition.
A worked example with hypothetical figures
| Merchant | October in ₹ | November in ₹ |
|---|---|---|
| Pump A | 4,200 | 3,900 |
| Workshop B | 0 | 6,500 |
| Recharge shop C | 499 | 499 |
| Month total | 4,699 | 10,899 |
October: 4,200 + 0 + 499 = 4,699. November: 3,900 + 6,500 + 499 = 10,899. The jump in November is the workshop bill, and the sheet makes that obvious in a second.
When does a sheet stop being enough?
When you keep adding bank statements and fixing merchant names by hand, the cleanup becomes the work. SpendixAI imports statements, normalises merchant names and lets you add aliases, and its report builder can filter and group before you download a CSV. Pro includes custom reports and advanced filters. Check how it handles your own statements before relying on it.
A useful follow-up is a share column. Next to the November total for each merchant, divide by the month total, for example =F2/F$6 if the total sits in F6, and format as percent. You will quickly see that one pump or one workshop accounts for most of the bill. That is a conversation you can have with the shop, or a reason to compare another one, and it needs no new tool, only a sheet kept tidy.
A few habits that keep the report right
- Fix merchant spelling at import, not at report time.
- Keep one tab for raw data and one for the report.
- Never type over a formula cell.
- Note the date range at the top of the report.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
