An annual spending report from bank statements is one spreadsheet holding all twelve months of transactions, with three views on top: a grid of category totals by month, a ranked list of the merchants you paid most, and a comparison with the previous year. Building it takes an evening once the statements are rows, and free spreadsheet software is enough.
What goes into an annual spending report from bank statements?
An annual spending report shows where a year's money went, in a form you can read in five minutes. It needs a category total for each month, a total for the year, the merchants that took the largest share, and the change from last year. Everything else in the report is detail you can drill into from those four numbers.
The four views
Each view answers a different question about the year:
- Category by month grid. Which categories grew, shrank or spiked in a particular month.
- Year totals by category. Where the money went over the whole year, ranked from largest to smallest.
- Top merchants. Which single shops, services or billers took the most.
- Year-over-year change. Whether this year cost more or less than last, and in which categories.
What the report is for
People build an annual report for different reasons. Some want to set next year's budget from real numbers. Some are checking whether a change, such as a new car or a move, cost what they expected. Households with children or students living away often want to see how one part of the family's spending grew. The family budget spreadsheet and the student budget spreadsheet are good places to put the figures once the report is done, since both start from monthly category limits.
Putting 12 months of statement rows into one annual sheet
Twelve months go into one sheet by stacking each month's rows in a single table with the same columns. Each statement becomes a block of rows, pasted below the previous one. When all twelve blocks are in, the table holds every transaction of the year and the report formulas can read it in one place.
Getting the rows
You need the rows as data, not twelve PDFs. Two ways work well:
- A CSV export for the whole year. Many banks let you export transactions for a custom date range. If yours does, one export covers all twelve months for that account.
- Twelve converted statements. If you only have PDF statements, convert each one to CSV or Excel. CheapBankConvert charges once per statement, and a bundle of five statements brings the price to about $2 each, which suits a year-end stack.
Repeat for each account that holds spending, such as a checking account and a credit card.
The columns
Use one table, named Tx, with these columns:
- Date, as a real date.
- Description, as printed.
- Amount, negative for money out.
- Account, such as Checking or Card, typed once per block.
- Category, from payee rules or typed by hand.
- Month, with
=TEXT([@Date],"yyyy-mm"). - Merchant, a cleaned-up payee name, used in the top-merchant view.
Checking each month is complete
Before building any view, confirm each block is whole. For each statement, the opening balance plus the sum of its rows should equal the closing balance printed on the statement. A month that does not tie is missing rows or has extra ones, and every annual total built on it will be off by the same amount.
Annual totals by category with a month-by-month grid
The grid has categories down the side and the twelve months across the top. Each cell is one SUMIFS that adds the spending for that category and month. A Total column on the right gives the year per category, and a Total row at the bottom gives each month's spending.
The grid formula
Put category names in A2 downward and month labels such as "2025-10" in B1 across. In B2:
=-SUMIFS(Tx[Amount], Tx[Category], $A2, Tx[Month], B$1)
The dollar signs keep the category column and month row fixed when you fill the formula right and down. The minus sign turns spending into positive numbers. A refund in that category and month is a positive amount, so it reduces the cell, which is usually what you want.
An example grid
Here is a shortened example with four categories and four of the twelve months. The values are illustrative.
| Category | Oct | Nov | Dec | Jan | Year total |
|---|---|---|---|---|---|
| Groceries | 602 | 588 | 744 | 571 | 7,210 |
| Eating out | 184 | 166 | 312 | 120 | 2,148 |
| Utilities | 141 | 176 | 228 | 239 | 2,236 |
| Gifts | 40 | 95 | 610 | 0 | 1,190 |
Read across a row to see a category's pattern. Gifts and eating out spike in December. Utilities rise into winter. Read down a column to see which month was expensive and why.
Highlighting the spikes
Select the grid and apply a colour scale from Conditional Formatting. The highest cells turn the strongest colour, and spikes jump out without reading every number. A colour scale per row, rather than across the whole grid, shows each category's own peak months.
Which merchants took the most money this year?
The top merchants are the payees with the largest total spending across the year. A pivot table with Merchant as rows and the sum of Amount as values, sorted from largest to smallest, gives the ranking in a minute. The first ten names usually account for a large share of the year's spending.
Cleaning merchant names first
Banks print one merchant many ways. "AMZN Mktp US2K4", "AMAZON.COMRT8" and "Amazon Prime" are one company to you and three payees to a pivot table. The Merchant column fixes this: a lookup table maps keywords to a clean name, the same way a category rule works, so all three rows become "Amazon".
An example top-merchant table
| Rank | Merchant | Year total | Payments |
|---|---|---|---|
| 1 | Landlord | 18,600 | 12 |
| 2 | Kroger | 4,980 | 61 |
| 3 | Amazon | 2,315 | 44 |
| 4 | City Power | 1,402 | 12 |
| 5 | Shell | 1,288 | 39 |
The Payments column, from a count of rows, tells you how the total built up. Twelve payments of the same size is a fixed bill. Sixty-one payments is a habit, and a habit is where small changes add up.
Recurring charges in the list
Some merchants near the bottom of the top twenty are subscriptions: twelve identical charges that each look small. Sort the pivot table by the payment count and look for rows with a count of 12 and a steady amount. The guide to finding subscriptions on a bank statement shows how to spot the ones that hide under a parent company's name.
Year-over-year change in the annual spending report
Year-over-year change compares each category's total this year with the same category last year, as an amount and a percentage. It needs last year's rows in the same table, or last year's grid saved beside this one. A rise is not bad by itself, but an unexplained rise is worth a look.
An example comparison
| Category | Last year | This year | Change | Change % |
|---|---|---|---|---|
| Groceries | 6,640 | 7,210 | +570 | +8.6% |
| Eating out | 2,610 | 2,148 | -462 | -17.7% |
| Utilities | 2,050 | 2,236 | +186 | +9.1% |
| Subscriptions | 540 | 892 | +352 | +65.2% |
The percentage is =(This-Last)/Last, formatted as a percentage. A large percentage on a small category, like subscriptions here, often points to new sign-ups or price rises. A large amount on a big category, like groceries, can come from prices alone.
Comparing like with like
Use the same categories both years. If you renamed or split a category halfway through, map the old names to the new ones before comparing, or the change will be nonsense. If you only started tracking this year, there is no last year to compare yet. Build the report anyway: next year's comparison starts from it.
Annual report traps: card payments, a missing month and duplicate rows
Three mistakes make an annual total wrong without any visible error. Counting credit card payments as spending counts the same money twice. A missing month lowers every total. Rows that appear twice, usually where two statements overlap by a day, raise them. Each one has a quick check.
Credit card payments from checking
If the report includes both a checking account and a credit card, the monthly card payment appears on the checking statement as money out. The purchases behind it appear on the card statement. Give the payment row a Transfer category and leave Transfer out of every total, so the spending counts once.
A missing month
Count the rows per month with a pivot table or COUNTIFS on the Month column. A month with zero rows, or far fewer than its neighbours, is missing a statement or part of one. Download that statement, convert it, paste its rows into the table, and only then read any totals.
Duplicate rows
When two exports overlap, the same purchase can appear twice with the same date and amount. Highlight duplicates with Conditional Formatting on a helper column that joins date, description and amount, such as =[@Date]&"|"&[@Description]&"|"&[@Amount]. Not every duplicate is an error, since two identical coffees on one day are real. Check each highlighted pair against the statement before deleting one.