To see expenses by category and month, turn your export into a Table, choose Insert, PivotTable, drag Category to Rows, the date or Month to Columns, and Amount to Values. Excel then totals each category for each month in a grid with row and column totals. The steps below use a sample bank export of 300 rows with hypothetical numbers.
How should the data be laid out first?
A pivot table is only as tidy as its source. Make sure of these before you start.
- One header row, with every column named: Date, Description, Category, Amount.
- No blank rows or merged cells inside the data, and no total row at the bottom.
- Dates stored as real dates. Click a date cell and see if the formula bar shows a proper date; text dates will not group.
- Amounts as numbers. If some rows left-align, they are text.
- Money out as one sign throughout. Many exports show debits and credits in separate columns; combine them into one Amount column first.
What are the steps for the pivot?
- Click any cell in the data and press Ctrl+T to format it as a Table. Tick My table has headers. This makes the source grow as you add rows.
- With a cell in the Table selected, choose Insert, PivotTable, and place it on a new worksheet.
- In the Fields pane, drag Category to Rows.
- Drag Date to Columns. In recent Excel versions it may group into months by itself, possibly with years or quarters; if it shows every single date, right-click a date and choose Group, then Months and Years.
- Drag Amount to Values. It should read Sum of Amount. If it says Count, click it, choose Value Field Settings and pick Sum.
- Right-click any number, choose Number Format, select Number, tick Use 1000 Separator, and set decimals to 0.
What will the result look like?
Take a small hypothetical slice. Rent is 18,000 in each of three months. Groceries are 6,200, 7,100 and 5,900. Dining out is 2,400, 3,800 and 1,900. The pivot shows rent as 18,000 down the row with a row total of 54,000, groceries totalling 19,200 and dining totalling 8,100. The grand total across the three months is 81,300. If your own grand total differs from the sum of the Amount column, something is excluded; check filters.
| Category | Apr | May | Jun | Row total |
|---|---|---|---|---|
| Rent | 18,000 | 18,000 | 18,000 | 54,000 |
| Groceries | 6,200 | 7,100 | 5,900 | 19,200 |
| Dining out | 2,400 | 3,800 | 1,900 | 8,100 |
| Grand total | 26,600 | 28,900 | 25,800 | 81,300 |
How do I tidy it up?
- Sort categories largest first: click a cell in the Grand Total column, then Data, Sort Z to A.
- Rename Sum of Amount to something shorter by typing over the header cell, such as Spend.
- Show blanks as zero: PivotTable Options, Layout and Format, Empty cells show, type 0.
- Turn off row totals if you do not need them from Design, Grand Totals.
- Move it to a Dashboard sheet and keep the data on its own sheet.
What if a category looks wrong or missing?
The pivot reports what the data says. A category called Food and one called Food & Drinks appear as two rows, and an empty category appears as (blank). Fix these in the source, not in the pivot, then press Refresh. If a month is missing, check that its dates are real dates and not text, and that no filter is excluding it.
When is a pivot the wrong tool?
It works well for a single log you maintain. If you collect many statements every month, classifying rows is the slow part, not the pivot. SpendixAI imports statements, assigns categories you can correct, and shows month and category reports without building the grid, though a pivot still helps when you want a layout of your own. Either way, refresh the pivot after adding rows, keep categories consistent, and check the total each month.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


