Spreadsheets

Pivot table of spending by category and month from a statement

Build an Excel pivot table of spending by category and month from a bank statement: clean the rows, group dates, fix blanks and refresh.

Editorial illustration for Pivot table of spending by category and month from a statement

To see spending by category and month from a bank statement, turn the statement rows into an Excel Table, insert a PivotTable from it, put Category in Rows, Date in Columns and Amount in Values, then group the dates by month. Once the data is clean it takes about five clicks, and it refreshes in one more when you add next month's rows.

Get the rows clean before you pivot

A pivot is only as tidy as the columns under it. Each statement line should be one row, with a real date, a filled Category cell and a single Amount column. If your statement has separate Debit and Credit columns, decide once whether refunds should reduce a category, and stay with that choice.

  • Dates should be right-aligned in the cell. Left-aligned dates are usually text and will not group.
  • One header row, no merged cells, no blank rows in the middle.
  • Delete the opening balance and the total line at the bottom, or the pivot will add them in.
  • Fill the Category column for every row, even if it is just Misc for now.
  • Click inside the data and press Ctrl+T so Excel turns it into a Table.

The five-click version

  1. Click any cell inside the Table.
  2. Choose Insert, then PivotTable, and accept New Worksheet.
  3. Drag Category into Rows.
  4. Drag Date into Columns. Recent Excel versions group it into Months automatically. If you see individual dates, right-click one, choose Group, and select Months and Years.
  5. Drag Amount into Values and check it says Sum of Amount, not Count of Amount.

You now have one row per category and one column per month, with grand totals on both edges.

Why does Excel say it cannot group the dates?

The message Cannot group that selection almost always means the date column holds a blank cell or text that only looks like a date. Filter the column for blanks and for left-aligned entries and fix them. For text dates written as 15/04/2026, select the column, choose Data, Text to Columns, click Next twice, set the column format to Date with DMY, and finish. Then refresh the pivot.

If the statement runs across more than one year, keep Years ticked in the Group box. Otherwise April 2025 and April 2026 would be added into one column.

What do the blank row and new transactions do?

Any row with an empty Category appears in the pivot as (blank). Treat that row as a to-do list: find those transactions in the source Table, fill the category, refresh, and the blank row disappears.

The pivot does not update by itself. After you paste new rows directly under the Table, which then grows, choose Data, then Refresh All, or press Alt+F5 with a cell in the pivot selected. A pivot built from a plain range instead of a Table would ignore the new rows, which is the main reason to make the Table first.

A small worked example

Say three months of a household's statements give these category totals. These numbers are made up to show the layout, not typical spending.

CategoryAprMayJunRow total
Groceries9,2008,4009,80027,400
Dining out3,1004,6002,90010,600
Transport2,8003,0002,6008,400
Electricity1,9002,4003,1007,400
Month total17,00018,40018,40053,800

Read across a row to see whether a category is drifting, and down a column to see what a whole month cost. May's dining out of 4,600 stands out against 3,100 and 2,900 around it, which is a good prompt to open the Table and look at those rows.

When a pivot stops being enough

If cleaning each statement by hand is what slows you down, SpendixAI lets you upload statements as PDF, CSV or Excel, review the rows before saving, and read category reports without building anything. For one account and a few months, the pivot above is enough.

Before you close the file, check three things: totals match the statement's own total, no (blank) row remains, and the date grouping includes the year.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports