A pivot table is the quickest way to summarise one card over a date range: filter the data to the dates you want, put the weeks or categories down the side and the amounts in the middle. You do not need formulas at all, and you can change the dates in seconds if your question changes.
What problem are we solving?
An exchange student in Melbourne puts almost everything for the semester on one prepaid travel card, topped up by a parent. The parent asks how much went out between 24 February and 30 May, and on what. The student has a year's worth of exports and needs only the semester, only that card.
How do I build the pivot?
- Export the card's transactions as CSV, or copy them from your online statement into a sheet with columns for date, description and amount.
- Add a column called Card and fill it with the card name, so rows from other accounts could be told apart later.
- Add a column called Week, using =A2-WEEKDAY(A2,2)+1 to return the Monday of each transaction's week (Excel and Google Sheets both support this).
- Select the data and insert a pivot table.
- Drag Week to Rows and Amount to Values, set to Sum. Drag Date to Filters, and choose the range 24 February to 30 May.
In Excel the date filter may offer Between; in Google Sheets, use a filter condition on the date field such as "Date is after" and "Date is before". Include the end dates themselves by choosing on or after and on or before where offered, or by extending the range by a day.
What does the weekly view show?
| Week starting | Spent |
|---|---|
| 24 Feb | A$212.00 |
| 3 Mar | A$168.50 |
| 10 Mar | A$397.20 |
| 17 Mar | A$141.30 |
The four weeks add up to 212.00 + 168.50 + 397.20 + 141.30 = A$919.00, an average of 229.75 a week, since 919 / 4 = 229.75. The 10 March week stands out; clicking into it might show the deposit on a flat or a flight home. The figures are imagined.
Can I group by something other than weeks?
Yes. Add a category column, filling it by hand or by sorting similar descriptions together, and put it in Rows instead of Week. Food, transport, study costs and fun give a four-line answer that a parent can read at a glance. If you want both, put Category in Rows and Week in Columns.
What should I do about top-ups and transfers between cards?
Money added to the card is not spending, so keep it out of the total. Give top-ups their own category or a minus sign, and filter them out of the pivot. Transfers between your own accounts can double-count too: the same A$300 appears as a debit on one account and a credit on another. Exclude both ends from the spending view.
When would an app be worth it?
When you have many months and several accounts, repeating this by hand gets tedious. SpendixAI keeps each transaction with its account and lets reports be filtered by account and date. It is built around India and INR and we do not claim it for Australian cards, but it shows the kind of feature to look for.
Making the report easy for someone else to read
A parent or manager does not want a pivot table; they want an answer. Add two sentences above it: the total for the period and the one item that explains most of it. Round to whole dollars for the summary, keep cents in the detail, and put the date range in the sheet's title. If the student above spent A$919.00 over four weeks and A$397.20 of it was in one week, the sentence writes itself: about 43 percent of the spending came from a single week, which is 397.20 divided by 919.00.
Finishing touches
- Write the start and end dates at the top of the sheet.
- Save the pivot as a separate tab, leaving the raw data untouched.
- Send a short note with the total, the biggest week and one line on what caused it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

